Skip to main content
Solved

Determine if dates fall within a range

  • June 23, 2021
  • 6 replies
  • 88 views
S
Forum|alt.badge.img+13

Hi,

I need to determine which of my projects were active in the previous month, and also active in the past 12 months. For the previous month, if we are now in June, I want to see which records were active in May (so I’m not looking for specific dates). For each record I’m looking at, I have a project start and project end which I can use to see when a project was active. So somehow I need to see if the previous month is within those project start and end dates. And ditto for the previous 12 months.

I tried to find the answer here in the community and spent a lot of time looking at Airtable date functions, but I can’t seem to figure it out.

Thanks in advance!

Best answer by Kamille_Parks11

Thanks – but can you help me understand how I can compare a date with mm/dd/yyyy to a month/yyyy?

Say my project start date is 3/5/2021 and project end date is 7/14/2021 and its currently June so I want to see if the project was active in May. I need to compare 3/5/2021 to 05-2021 and compare 7/14/2021 to 05-2021. Will that work with your suggested calculation?

I realized that the date in mm/yyyy format is actually a string, due to the DATETIME_FORMAT function, so I can’t really compare it to a true Date field.


Since any date formatted as YYYYMM results in a number, you can compare the dates to “today” using the same greater/less than or equal to logic as you would with a numeric range:

IF(
   AND(
      DATETIME_FORMAT({Date 1}, "YYYYMM") <= DATETIME_FORMAT(DATEADD(TODAY(), -1, "month"), "YYYYMM"), 
      DATETIME_FORMAT({Date 2}, "YYYYMM") >= DATETIME_FORMAT(DATEADD(TODAY(), -1, "month"), "YYYYMM")
   ), 
   "yes", 
   "no")

    6 replies

    I
    Forum|alt.badge.img+7
    • Ilan_Ben_Yaakov
    • Participating Frequently
    • June 24, 2021

    you should try something like

    IF(AND(date>start,date<end),"Yes","No")
    S
    Forum|alt.badge.img+13
    • Sharon_Visser
    • Author
    • Inspiring
    • June 24, 2021

    you should try something like

    IF(AND(date>start,date<end),"Yes","No")

    Thanks – but can you help me understand how I can compare a date with mm/dd/yyyy to a month/yyyy?

    Say my project start date is 3/5/2021 and project end date is 7/14/2021 and its currently June so I want to see if the project was active in May. I need to compare 3/5/2021 to 05-2021 and compare 7/14/2021 to 05-2021. Will that work with your suggested calculation?

    I realized that the date in mm/yyyy format is actually a string, due to the DATETIME_FORMAT function, so I can’t really compare it to a true Date field.

    Kamille_Parks11
    Forum|alt.badge.img+27

    Thanks – but can you help me understand how I can compare a date with mm/dd/yyyy to a month/yyyy?

    Say my project start date is 3/5/2021 and project end date is 7/14/2021 and its currently June so I want to see if the project was active in May. I need to compare 3/5/2021 to 05-2021 and compare 7/14/2021 to 05-2021. Will that work with your suggested calculation?

    I realized that the date in mm/yyyy format is actually a string, due to the DATETIME_FORMAT function, so I can’t really compare it to a true Date field.


    Since any date formatted as YYYYMM results in a number, you can compare the dates to “today” using the same greater/less than or equal to logic as you would with a numeric range:

    IF(
   AND(
      DATETIME_FORMAT({Date 1}, "YYYYMM") <= DATETIME_FORMAT(DATEADD(TODAY(), -1, "month"), "YYYYMM"), 
      DATETIME_FORMAT({Date 2}, "YYYYMM") >= DATETIME_FORMAT(DATEADD(TODAY(), -1, "month"), "YYYYMM")
   ), 
   "yes", 
   "no")
    kuovonne
    S
    S
    Forum|alt.badge.img+13
    • Sharon_Visser
    • Author
    • Inspiring
    • June 24, 2021

    Since any date formatted as YYYYMM results in a number, you can compare the dates to “today” using the same greater/less than or equal to logic as you would with a numeric range:

    IF(
   AND(
      DATETIME_FORMAT({Date 1}, "YYYYMM") <= DATETIME_FORMAT(DATEADD(TODAY(), -1, "month"), "YYYYMM"), 
      DATETIME_FORMAT({Date 2}, "YYYYMM") >= DATETIME_FORMAT(DATEADD(TODAY(), -1, "month"), "YYYYMM")
   ), 
   "yes", 
   "no")

    This works perfectly! Thank you so much. I am so grateful to the Airtable Community.

    Justin_Barrett
    Forum|alt.badge.img+21

    This works perfectly! Thank you so much. I am so grateful to the Airtable Community.


    Glad to know that you got the answer you were seeking! If you would, please mark @Kamille_Parks’ comment above as the solution to your question. This helps others who may be searching with similar questions. Thanks!

    S
    Forum|alt.badge.img+13
    • Sharon_Visser
    • Author
    • Inspiring
    • June 30, 2021

    Glad to know that you got the answer you were seeking! If you would, please mark @Kamille_Parks’ comment above as the solution to your question. This helps others who may be searching with similar questions. Thanks!


    Will do!

    Sharon Visser

    Terms & ConditionsAccessibility statement