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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!