Skip to main content

Hi 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

Not 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.

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

 

 

Terms & ConditionsAccessibility statement