Date+days not calculating correctly

Megan B.

Hi all,

I need to add 21 days to a date referenced in another cell.

Ex: 10/15/23

When I use this formula: =DATE(YEAR([Projected Start Date]@row), MONTH([Projected Start Date]@row), (DAY([Projected Start Date]@row)) + 21) I receive an #INVALID VALUE

If I hardcode the date into the formula (=DATE(2023, 10, 15)+21, I return the right result (11/5/23)

All of the following formulas also return the correct response:

=DATE(YEAR([Projected Start Date]@row), 10, 15)+21

=DATE(YEAR([Projected Start Date]@row), MONTH([Projected Start Date]@row), 15) + 21

=DATE(YEAR([Projected Start Date]@row), MONTH([Projected Start Date]@row), (DAY([Projected Start Date]@row)) + 2) --(this returns a date of 10/17/23)

The problem seems to be when I use the DAY function + days that run the calculation into the next month but I am stumped because the same problem does not exist if I hard code the same date. It seems like somehow I need to get that numeric value to calculate first so the formula sees it only as a value and not a formula.

Edit to add:

I also tried using VALUE in the formula in this way:

=DATE(YEAR([Projected Start Date]@row), MONTH([Projected Start Date]@row), VALUE(DAY([Projected Start Date]@row)) + 21)

This was also unsuccessful.

Thoughts?

Dan W

Try moving like this

=DATE(YEAR([Projected Start Date]@row), MONTH([Projected Start Date]@row), (DAY([Projected Start Date]@row))) + 21

Dan W

Try moving like this

=DATE(YEAR([Projected Start Date]@row), MONTH([Projected Start Date]@row), (DAY([Projected Start Date]@row))) + 21