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:
· Share on FacebookShare on Twitter

Answers

  • Zachary Taylor
    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! 

    · Share on FacebookShare on Twitter
  • sjy
    sjy ✭

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

    Thanks very much, Zachary.

    · Share on FacebookShare on Twitter
  • Melissa Rojas
    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)

    · Share on FacebookShare on Twitter
  • Jasmine
    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))

    · Share on FacebookShare on Twitter
  • Paola Preciado
    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 😊

    · Share on FacebookShare on Twitter
  • 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.

    · Share on FacebookShare on Twitter
  • 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. :)

    · Share on FacebookShare on Twitter
  • LesahDee
    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", " "))))

    · Share on FacebookShare on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭✭

    @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'.

    image.png

    image.png

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!