Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Range between two dates

Solved
Jump to Solution
2719 3
cancel
Showing results for 
Search instead for 
Did you mean: 
dataLizard37
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
dataLizard37
5 - Automation Enthusiast
5 - Automation Enthusiast

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

 

 

See Solution in Thread

3 Replies 3
Grunty
7 - App Architect
7 - App Architect

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
11 - Venus
11 - Venus

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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