-1

I'm trying to create a formula to calculate the date in column D4 based on the 'month & year' provided in B4

enter image description here

and a plain-text number given in C4

enter image description here

so if B4 is 02/2021 and C4 is ' 1 ' then calculate the date 02/01/2021 in D4

Likewise, if B4 is 02/2021 and C5 is ' 15 ' then calculate the date 02/15/2021 in D5

Bonus question: if the MONTH-DAY-YEAR (02/01/2021) was a static date at the top of a sheet in A1, what would the formula be to generate the same result - the date that corresponds to the number given in B4?

Thanks for the help.

4
  • Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts a is suggested in How to Ask.
    – Rubén
    Oct 26, 2021 at 21:17
  • Hi and welcome. Would you please show what you have tried already. Just for the record, when you enter just the month and year (such as 02-2021, there is an implicit "day" as well - the first of the month. So 02-2021 is actually 1 February 2021. Add "1" and you get 2 February; add 15 and you get 16 February, etc. So the formula that you seek is simple maths.
    – Tedinoz
    Oct 26, 2021 at 22:24
  • Have you tried to solve any of these? I suggest that you look at the documentation for DATE()
    – Tedinoz
    Oct 26, 2021 at 22:26
  • Hi and Thank you. Yes. this is what I have right now but it's rather clunky and I'd like to avoid having the date criteria listed in two places on the same sheet. =IF(ISBLANK(B12),"-",IF(B12>DAY(EOMONTH(DATE($A$2,$A$3,1),0)),EOMONTH(DATE($A$2,$A$3,1),0),DATE($A$2,$A$3,B12))) I have a sheet for each month of the year with the date format listed above in a cell that corresponds to the month. But to get the math I need I've had to add the year & month again in separate cells as plain text to do the crazy math above. A2 - 2021 A3 - 1 (for Jan) Oct 28, 2021 at 17:51

1 Answer 1

0

As Tedinoz explained in his comment to your original post, this turns out to be simple math. However, you can accomplish it with a single array formula.

Delete any formulas you have in D4:D.

In cell D4, place the following formula:

=ArrayFormula(IF(C4:C="",,B4-1+C4:C))

An array formula can process an entire range instead of just a single cell or value.

The IF first checks to see if there is any value in each row of C4:C. If not, then the corresponding cell in D4:D is left null as well. Otherwise, your B4 date is bumped backward 1 day (to the last day of the previous month), to the number of days in each cell of C4:C is then added.

If you get an error or strange results when you use this formula, there are three likely causes:

1.) If you get a return of numbers in the 40000-50000 range, those are actually unformatted dates. Just format Col D as dates (Format > Number > choose a date format).

2.) You will get an error or unpredictable results further down the column if you currently have unrelated data in your sheet below where your C4:C numbers are. You only let us see a keyhole view of part of your sheet, so there isn't much more I can tell you without seeing more of your sheet in this case.

3.) You are in a non-US locale that uses semicolons instead of commas. In that case, simply change every comma in the formula to a semicolon.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.