IF formula combined with dates
Hello,
I need help. I am wondering how can I create a formula using the IF function with a range of dates. For example:
If the date is between 01/01/2018 and 03/30/2018, then write 1st Quarter., and if the date is between 04/01/2018 and 06/30/2018, then 2nd. Quarter, etc....
Thank you in advance!
Comments
-
Give this a try (a few minor changes may be needed, explained below)
=IF(AND(Date@row >= DATE(2018, 1, 1), Date@row <= DATE(2018, 3, 30)), "Q1", IF(AND(Date@row >= DATE(2018, 4, 1), Date@row <= DATE(2018, 6, 30)), "Q2", IF(AND(Date@row >= DATE(2018, 7, 1), Date@row <= DATE(2018, 9, 30)), "Q3", IF(AND(Date@row >= DATE(2018, 10, 1), Date@row <= DATE(2018, 12, 31)), "Q4"))))
Where it says Date@row, you only need to change the Date portion to your column name.
"Q1", etc... can be changed to "1st Quarter", etc... or whatever you would like it to say.
The dates that denote the beginning and end of each quarter can be changed to whatever you would like. The format for Smartsheet is DATE(YYYY, MM, DD)
-
This formula does the same as Paul's example:
="Q" + INT((MONTH(Date@row) + 2) / 3)
without the year.
If it needs to say "1st Quarter" and not "Q1", then you'll need an nested if like Paul's (with the shorter formula to determine 1-4 here) or a LOOKUP() function to return "1st" when search for "1"
The problem with building formulas that look for a specific time frame is that they will need to be changed / maintained as time moves forward.
On January 1st, all dates will have a blank Quarter number.
I'd prefer
=IFERROR("Q" + INT((MONTH(Date@row) + 2) / 3) + "Y" + RIGHT(YEAR(Date@row),2),"not a date")
to return Q1Y18 for 1st quarter this year.
My $0.02
Craig
-
Diggin' it...
I always forget about the INT function.
-
Thank you all!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!