How to roll a date back to the first of the month.
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
Best Answer
-
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)
Answers
-
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)
-
Excellent, thankyou very much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!