# Autocalculate Date to match a number in a given cell

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

and a plain-text number given in C4

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.

• 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. Oct 26, 2021 at 22:24
• Have you tried to solve any of these? I suggest that you look at the documentation for `DATE()` 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

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.