Skip to main content

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!

you should try something like


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

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.

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

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.

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!

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

Reply


Terms & Conditions