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

Options
edited 05/27/21

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.

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

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...

• ✭✭✭✭✭✭
Options

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))

• Options

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.

• ✭✭✭✭✭✭
Options

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...

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!