Date Ranges

[dataLizard37]

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

[Grunty]

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?

[Sho]

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.

[dataLizard37 · Accepted solution]

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