How to roll a date back to the first of the month.

colin alexander

We have a calibration sheet that has a column for MONTHS (FREQUENCY), a column for LAST CALIBRATION and a column for CALIBRATION EXPIRY which uses the following formula to display the calibration expiry date.

=IFERROR(DATE(YEAR([LAST CALIBRATION]@row) + ROUNDDOWN((MONTH([LAST CALIBRATION]@row) + [MONTHS (FREQUENCY)]@row) / 12, 0) + IF(IF(MOD(MONTH([LAST CALIBRATION]@row) + [MONTHS (FREQUENCY)]@row, 12) = 0, 12, MOD(MONTH([LAST CALIBRATION]@row) + [MONTHS (FREQUENCY)]@row, 12)) = 12, -1), IF(MOD(MONTH([LAST CALIBRATION]@row) + [MONTHS (FREQUENCY)]@row, 12) = 0, 12, MOD(MONTH([LAST

CALIBRATION]@row) + [MONTHS (FREQUENCY)]@row, 12)), DAY([LAST CALIBRATION]@row)), "N/A")

This formula is working well but i am now being asked if we can modify the formula to roll the date back to show the 1st of the month so that if a date from the current formula is 26-Aug-2022, it will display 1-Aug-2022 instead. This is so we can then group all items that are due for calibration in the month together.

Can anyone help with modifying the above formula to achieve this.

Regards

TVang

Here's a shorter formula to determine the CALIBRATION EXPIRY.

calibration_expiry = [MONTHS (Frequency)]@row * (365 / 12) + [LAST CALIBRATION]@row

There are 365 days in a year, or 12 months in a year. So there are approximately 365/12 days-per-month. Multiply the frequency (months) by 365/12 days-per-month, and add the resulting days to the LAST CALIBRATION date to find the CALIBRATION EXPIRY date.

Use DATE(), YEAR(), and MONTH() functions to derive the 1st day of the CALIBRATION EXPIRY month:

DATE( YEAR( calibration_expiry ) , MONTH( calibration_expiry ) , 1 )

Additionally, when there is not a date in LAST CALIBRATION--also written as NOT(IsDATE( [LAST CALIBRATION]@row ))--list or use whatever is in there (i.e., [LAST CALIBRATION]@row).

IF( NOT(ISDATE([LAST CALIBRATION]@row)), [LAST CALIBRATION]@row , DATE( YEAR( calibration_expiry ), MONTH( calibration_expiry ), 1 ) )

Plug in the expression that calculates the expiry date, calibration_expiry, and the complete formula is...

=IF( NOT(ISDATE([LAST CALIBRATION]@row)) , [LAST CALIBRATION]@row , DATE(YEAR([MONTHS (FREQUENCY)]@row * (365 / 12) + [LAST CALIBRATION]@row), MONTH([MONTHS (FREQUENCY)]@row * (365 / 12) + [LAST CALIBRATION]@row), 1) )

Alternatively...

EXPIRY MONTH = DATE( YEAR([LAST CALIBRATION]@row) , MONTH([LAST CALIBRATION]@row) , 1)