Formula to determine a specific date within a month based on date ranges.
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.
Best Answers
-
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))
-
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...
Answers
-
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))
-
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.
-
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...
-
Thank you for explaining this in case of a month adjustment. I really appreciate all your help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!