Jun 23, 2021 07:26 AM
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!
Solved! Go to Solution.
Jun 24, 2021 02:49 PM
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")
Jun 24, 2021 04:58 AM
you should try something like
IF(AND(date>start,date<end),"Yes","No")
Jun 24, 2021 06:18 AM
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.
Jun 24, 2021 02:49 PM
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")
Jun 24, 2021 03:52 PM
This works perfectly! Thank you so much. I am so grateful to the Airtable Community.
Jun 29, 2021 05:40 PM
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!
Jun 29, 2021 05:59 PM
Will do!
Sharon Visser