+4Hi everyone,
I have a start and end date field in one of my tables and I am trying to find a way to get airtable to tag each month within the range. Essentially I want to know how many were active between 8/30/2019 and 7/31/2020. I haven't been able to figure out the filters because if a record was 7/30/2019-9/1/2020, it wouldn't show up. My workaround is getting the months range and then I can use that multiple select field to do the filtering.
Any suggestions?
Thank you in advance
Best answer by dataLizard37
Hi @dataLizard37 ,
Like this?
IF(OR(
AND(
IF("Start date is within the duration",1),
IS_BEFORE({Start Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({Start Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
),
AND(
IF("End date is within a duration",1),
IS_BEFORE({End Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({End Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
),
AND(
IF("The duration is contained within the two dates",1),
IS_BEFORE({Start Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({End Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
)
),"!")IF statement is a comment and can be deleted.
Hi Sho!
That worked! I was able to modify it a bit and ended up with the following. I do have to do a separate field for each time range but I am working on a mechanism that will automate this process. Thank you for your feedback.
IF(
OR(
AND(
DATETIME_DIFF({Start Date}, DATETIME_PARSE('2017-09-01', 'YYYY-MM-DD'), 'days') >= 0,
DATETIME_DIFF(DATETIME_PARSE('2018-08-31', 'YYYY-MM-DD'), {Start Date}, 'days') >= 0
),
AND(
DATETIME_DIFF({End Date}, DATETIME_PARSE('2017-09-01', 'YYYY-MM-DD'), 'days') >= 0,
DATETIME_DIFF(DATETIME_PARSE('2018-08-31', 'YYYY-MM-DD'), {End Date}, 'days') >= 0
),
AND(
DATETIME_DIFF(DATETIME_PARSE('2017-09-01', 'YYYY-MM-DD'), {Start Date}, 'days') >= 0,
DATETIME_DIFF({End Date}, DATETIME_PARSE('2018-08-31', 'YYYY-MM-DD'), 'days') >= 0
)
),
"Within Range",
"Outside Range"
)
+15Not sure if I understand correctly but I'll try.
First have each record's date range field split into separate sub-fields. You can use two formula fields, say 'date-from' and 'date-to', to split the range field by the hyphen and then convert the two strings into dates.
Then a third formula field, 'active-in-range':
IF(OR(date-from > 7/31/2020, date-to < 8/30/2019), "no", "yes")
This tells whether the record's entire date range falls outside the control range, or at least partially overlaps the control range.
Is that what you want to achieve?
Hi @dataLizard37 ,
Like this?
IF(OR(
AND(
IF("Start date is within the duration",1),
IS_BEFORE({Start Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({Start Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
),
AND(
IF("End date is within a duration",1),
IS_BEFORE({End Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({End Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
),
AND(
IF("The duration is contained within the two dates",1),
IS_BEFORE({Start Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({End Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
)
),"!")IF statement is a comment and can be deleted.
+4Hi @dataLizard37 ,
Like this?
IF(OR(
AND(
IF("Start date is within the duration",1),
IS_BEFORE({Start Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({Start Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
),
AND(
IF("End date is within a duration",1),
IS_BEFORE({End Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({End Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
),
AND(
IF("The duration is contained within the two dates",1),
IS_BEFORE({Start Date}, DATEADD(DATESTR("7/31/2020"), 1, 'days')),
IS_AFTER({End Date}, DATEADD(DATESTR("8/30/2019"), -1, 'days'))
)
),"!")IF statement is a comment and can be deleted.
Hi Sho!
That worked! I was able to modify it a bit and ended up with the following. I do have to do a separate field for each time range but I am working on a mechanism that will automate this process. Thank you for your feedback.
IF(
OR(
AND(
DATETIME_DIFF({Start Date}, DATETIME_PARSE('2017-09-01', 'YYYY-MM-DD'), 'days') >= 0,
DATETIME_DIFF(DATETIME_PARSE('2018-08-31', 'YYYY-MM-DD'), {Start Date}, 'days') >= 0
),
AND(
DATETIME_DIFF({End Date}, DATETIME_PARSE('2017-09-01', 'YYYY-MM-DD'), 'days') >= 0,
DATETIME_DIFF(DATETIME_PARSE('2018-08-31', 'YYYY-MM-DD'), {End Date}, 'days') >= 0
),
AND(
DATETIME_DIFF(DATETIME_PARSE('2017-09-01', 'YYYY-MM-DD'), {Start Date}, 'days') >= 0,
DATETIME_DIFF({End Date}, DATETIME_PARSE('2018-08-31', 'YYYY-MM-DD'), 'days') >= 0
)
),
"Within Range",
"Outside Range"
)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.