Return Value if Between Dates

sjy

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 : )  

 

Zachary Taylor

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! 

sjy

Yes! Perfectly! I definitely wouldn't have gotten there without help.

Thanks very much, Zachary.

Melissa Rojas

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)

Jasmine

https://community.smartsheet.com/discussion/comment/299833#Comment_299833

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))

Paola Preciado

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 😊

DxDenty

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.

PeggyLang

@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. :)

LesahDee

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", " "))))