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

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    You have at least 2 options (as well as living with it) 😁

    1. Add another IF to the start to evaluate if the Closing Date is blank and return nothing in that case.
    2. 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

  • KPH
    KPH ✭✭✭✭✭✭

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

  • Robert S Fike
    Robert S Fike ✭✭✭✭

    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

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    You have at least 2 options (as well as living with it) 😁

    1. Add another IF to the start to evaluate if the Closing Date is blank and return nothing in that case.
    2. 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))), "")

  • Robert S Fike
    Robert S Fike ✭✭✭✭

    Thank you very much. That worked perfect.

  • KPH
    KPH ✭✭✭✭✭✭

    Wonderful! 👍️