Help with dates?
I have a column titled "Effective Date".
I want to have 3 other columns populated based on the date entered in "Effective Date".
1st column: "Period"
2nd column: "Quarter"
3rd column "Fiscal Year".
Our fiscal year begins February 1st and runs through January 31st. Feb = period 1, Mar = period 2, Apr = period 3, etc. Q1 = Feb 1 through Apr 30, Q2 = May 1 through Jul 31, etc.
Any suggestions? Thanks in advance.
Best Answer
-
To simplify things we can create another column calling it [CalculatedDate] with the below formula:
=[Effective Date]@row - (DAY([Effective Date]@row))
You will then use this column to do all your other calculations, so:
Fiscal Year:
=YEAR(CalculatedDate@row)
Period:
=MONTH(CalculatedDate@row)
Quarter:
="Q" + ROUNDUP(MONTH(CalculatedDate@row) / 3, 0)
Answers
-
To simplify things we can create another column calling it [CalculatedDate] with the below formula:
=[Effective Date]@row - (DAY([Effective Date]@row))
You will then use this column to do all your other calculations, so:
Fiscal Year:
=YEAR(CalculatedDate@row)
Period:
=MONTH(CalculatedDate@row)
Quarter:
="Q" + ROUNDUP(MONTH(CalculatedDate@row) / 3, 0)
-
Thank you!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!