Return Value if Between Dates
Hello!
I have a [DATE SUBMITTED] column and need to create a reference column that returns the fiscal year that date falls in. Something like this -
=IF [DATE SUBMITTED] > 10/01/17 AND [DATE SUBMITTED] < 09/30/18 RETURN "FY18"
and
=IF [DATE SUBMITTED] > 10/01/18 AND [DATE SUBMITTED] < 09/30/19 RETURN "FY19"
I'm not sure if I should use IF AND or if SmartSheet has a BETWEEN option for date formulas.
Any and all formula suggestions would be appreciated : )
Answers
-
Hey there!
Smartsheet doesn't currently have a between operator, but this can be accomplished with a combination of the IF and AND functions.
This formula should accomplish what you're looking for:
=IF(AND([DATE SUBMITTED]@row >= DATE(2017, 10, 1), [DATE SUBMITTED]@row <= DATE(2018, 9, 30)), "FY18", IF(AND([DATE SUBMITTED]@row >= DATE(2018, 10, 1), [DATE SUBMITTED]@row <= DATE(2019, 9, 30)), "FY19", "")
Note the Greater/Less than or equal to operators (>=, <=) to ensure that dates that fall on the specified dates are included in the criteria.
I hope this helps!
-
Yes! Perfectly! I definitely wouldn't have gotten there without help.
Thanks very much, Zachary.
-
Hello,
I'm trying to do something similar, however, I need to count if "the stage column" = to completed AND the date column is between 1/1/2021-12/31/2021.
I cant figure it out, here's what I tried:
=COUNTIFS(Stage:Stage), "Completed" AND([Start Date] = DATE(2021, 1, 1), [Start Date]= DATE(2021, 12, 31))
=COUNTIFS[(Stage:Stage), "Completed"] AND(Start Date is between (2021,1,1:2021,12,31)
-
Hi Melissa,
For a Sheet Summary field try the formula below:
=COUNTIFS(Stage:Stage, "Completed", Date:Date, >DATE(2021, 1, 1), Date:Date, <DATE(2021, 12, 31))
-
Hello, I am trying a similar formula but building a metrics sheet. So, I am using crossed reference formulas.
I am trying to count if it matches a specific program, specific types of reviews and status (using OR formula), and between two dates of receipt to count only the ones received in 2022 Q1.
I will also need to do the same for the ones completed 2022 Q1.
Here it is my formula, but it is not working #UNPEARSABE
=(COUNTIFS({Classification Review Type}, "Informal", {Classification Review Program}, "APD", (AND({Classification Review Date Received}, > DATE(2021, 12, 31)), {Classification Review Date Received}, < DATE(2022, 4, 1))))
=(COUNTIFS({Classification Review Type}, <>"Informal", {Classification Review Program}, "APD", {Review Status}, OR(@cell = "Completed", @cell = "Closed",(AND({Classification Review Date Received}, > DATE(2021, 12, 31)), {Classification Review Date Received}, < DATE(2022, 4, 1))))
I will appreciate the help.
Paola 😊
-
I am trying to perform a COUNTIFS function to count the items assigned to an individual between a set of dates. I am good on the first part but I can not get the date range to work. I have:
=COUNTIF({Range 1}, "name:name", {Range 2}, ">DATE(2022, 6, 30)" AND "<DATE(2023, 7, 1)").
Any Help would be appreciated.
-
@Zachary Taylor LOVE this community! I almost always find what I need. If I can't find it then I ask the question and someone responds. This community has been invaluable to my learning and productivity. :)
-
this was awesome thanks you got my formula to work,
=IF(AND([Submission Date]@row >= DATE(2024, 1, 1), [Submission Date]@row <= DATE(2024, 3, 31)), "1st Quarter", IF(AND([Submission Date]@row >= DATE(2024, 4, 1), [Submission Date]@row <= DATE(2024, 6, 30)), "2nd Quarter", IF(AND([Submission Date]@row >= DATE(2024, 7, 1), [Submission Date]@row <= DATE(2024, 9, 30)), "3rd Quarter", IF(AND([Submission Date]@row >= DATE(2024, 10, 1), [Submission Date]@row <= DATE(2024, 12, 31)), "4th Quarter", " "))))
-
@Zachary Taylor
I am trying to determine if a date from Sheet 1 is in between 2 dates in Sheet 2. If it is then return a cell value from Sheet 2.
Below is a snippet of what Sheet 2 looks like.Formula is below. This is currently returning a 'UNPARSEABLE'.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 60 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!