Formula to determine a specific date within a month based on date ranges.

April Barrera

Good Morning,

I am trying to determine a formula to produce a specific date based on date ranges. So when a date (mm/dd/yy) is entered into a column if it is between the 1-15 day of the month, I want it to populate the 25th of the same month into a column- (mm/25/yy), however, if the date is 16-31 day of the month, I want it to populate the 10th of the next month into the column.

Thanks for any help.

Paul Newcome

OK. So the same month for days before the 16th would be:

=IF(DAY([Date Column]@row)<= 15, DATE(YEAR([Date Column]@row), MONTH([Date Column]@row), 25))

The next bit is a little tricky because of December going into January.

DATE(YEAR([Date Column]@row) + IF(MONTH([Date Column]@row) = 12, 1), IF(MONTH([Date Column]@row) = 12, 1, MONTH([Date Column]@row) + 1), 10)

Drop that into the third portion of the IF above, and you should have a working formula.

=IF(DAY([Date Column]@row) <= 15, DATE(YEAR([Date Column]@row), MONTH([Date Column]@row), 25), DATE(YEAR([Date Column]@row) + IF(MONTH([Date Column]@row) = 12, 1), IF(MONTH([Date Column]@row) = 12, 1, MONTH([Date Column]@row) + 1), 10))

Paul Newcome

For six months to the day it would look something like this...

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 6, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 6, DAY([Date Column]@row)))

If you needed to adjust this to say a different number of months out, you would adjust the numbers in the MONTH function so that the integers equal 12.

1 Month = +1 & -11

2 Months = +2 & -10

3 Months = +3 & -9

so on and so forth...

Paul Newcome

OK. So the same month for days before the 16th would be:

=IF(DAY([Date Column]@row)<= 15, DATE(YEAR([Date Column]@row), MONTH([Date Column]@row), 25))

The next bit is a little tricky because of December going into January.

DATE(YEAR([Date Column]@row) + IF(MONTH([Date Column]@row) = 12, 1), IF(MONTH([Date Column]@row) = 12, 1, MONTH([Date Column]@row) + 1), 10)

Drop that into the third portion of the IF above, and you should have a working formula.

=IF(DAY([Date Column]@row) <= 15, DATE(YEAR([Date Column]@row), MONTH([Date Column]@row), 25), DATE(YEAR([Date Column]@row) + IF(MONTH([Date Column]@row) = 12, 1), IF(MONTH([Date Column]@row) = 12, 1, MONTH([Date Column]@row) + 1), 10))

April Barrera

This works perfectly!

I have one more question - if I have a second date that I need to generate 6 months from this date, what would that formula be? The exact same day in the future month just six months from that date.

Paul Newcome

For six months to the day it would look something like this...

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 6, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 6, DAY([Date Column]@row)))

If you needed to adjust this to say a different number of months out, you would adjust the numbers in the MONTH function so that the integers equal 12.

1 Month = +1 & -11

2 Months = +2 & -10

3 Months = +3 & -9

so on and so forth...

April Barrera

Thank you for explaining this in case of a month adjustment. I really appreciate all your help.

Paul Newcome

Happy to help. 👍️