Return Value if Between Dates

sjy
sjy
edited 12/09/19 in Formulas and Functions

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

 

Tags:

Answers

  • Zachary Taylor
    edited 02/26/19

    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)

  • Jasmine
    Jasmine ✭✭✭

    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
    edited 09/13/22

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!