Calculating a schedule date from a Date
We send out notifications in the month before a closing month, on the 23rd. This simple formula was working well, except it does not work for January. If the closing date is 12/31/23, the notification date is 11/23/23. Any help would be greatly appreciated.
=IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), "")
Best Answer
-
You have at least 2 options (as well as living with it) 😁
- Add another IF to the start to evaluate if the Closing Date is blank and return nothing in that case.
- Return to using blank for an IFERROR but include an IF after it to use the alternate formula if MONTH([Closing Date]@row) is January.
I'd probably go with option 1 as it is the easiest to understand when looking at the formula. Although, option 2 is probably the most robust - just in case there are other errors.
Here is option 1:
=IF(ISBLANK([Closing Date]@row), "", (IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), DATE(YEAR([Closing Date]@row) - 1, 12, 23))))
Here is option 2:
=IFERROR(IF(MONTH([Closing Date]@row) = 1, DATE(YEAR([Closing Date]@row) - 1, 12, 23), (DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23))), "")
Answers
-
Your formula is creating a date based on the date in the closing date column.
It takes the year from the closing date column and uses that for the new date
It takes the month from the closing date column and subtracts one to use that for the new date
It then uses 23 as the day.
It cannot take 1 from the month of January (month 1) as there is no month 0.
So instead would generate an error.
In your case the IFERROR part of the function returns "".
So you see nothing.
If you replace "" with a DATE function for December 23 in the year prior to the year of the closing date you should be fine.
DATE(YEAR([Closing Date]@row)-1, 12, 23)
So your formula would be
=IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), DATE(YEAR([Closing Date]@row)-1, 12, 23))
-
That worked. Thank you. But now, in jobs I don't have a date plugged in yet. It shows #INVALID DATA TYPE. Can this be removed, or we just live with that.
Thanks
-
You have at least 2 options (as well as living with it) 😁
- Add another IF to the start to evaluate if the Closing Date is blank and return nothing in that case.
- Return to using blank for an IFERROR but include an IF after it to use the alternate formula if MONTH([Closing Date]@row) is January.
I'd probably go with option 1 as it is the easiest to understand when looking at the formula. Although, option 2 is probably the most robust - just in case there are other errors.
Here is option 1:
=IF(ISBLANK([Closing Date]@row), "", (IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), DATE(YEAR([Closing Date]@row) - 1, 12, 23))))
Here is option 2:
=IFERROR(IF(MONTH([Closing Date]@row) = 1, DATE(YEAR([Closing Date]@row) - 1, 12, 23), (DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23))), "")
-
Thank you very much. That worked perfect.
-
Wonderful! 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives