Get first & last date of a month from a date in another column
Im trying to get 2 formula's that will calculate the first & last dates of a month based on a date in another column on the same row.
So for instance if 'Date of Movement' column has 10/1/2023 i would like the 'Start Date of Month' column to return 1/1/2023.
Likewise if 'Date of Movement' column has 10/1/2023 i would like the 'End Date of Month' column to return 31/1/2023.
I know in excel the first formula would be: =EOMONTH([@[Date of Movement],-1)+1
And the second formula: =EOMONTH([@[Date of Movement]],0)
However i cant find a solution in Smartsheet that does similar.
Gavin
Best Answer
-
Hi @Gavin Seaton ,
For start of the month:
=DATE(YEAR([Date of Movement]@row), MONTH([Date of Movement]@row), 1)
For end of the month:
=IFERROR(DATE(YEAR([Date of Movement]@row), MONTH([Date of Movement]@row) + 1, 1), DATE(YEAR([Date of Movement]@row) + 1, 1, 1)) - 1
Hope this helps; any questions etc. then just ask! 😊
Answers
-
Hi @Gavin Seaton ,
For start of the month:
=DATE(YEAR([Date of Movement]@row), MONTH([Date of Movement]@row), 1)
For end of the month:
=IFERROR(DATE(YEAR([Date of Movement]@row), MONTH([Date of Movement]@row) + 1, 1), DATE(YEAR([Date of Movement]@row) + 1, 1, 1)) - 1
Hope this helps; any questions etc. then just ask! 😊
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!