- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 18, 2023 09:24 PM
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
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 19, 2023 06:18 AM - edited ‎Oct 19, 2023 06:19 AM
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"
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 18, 2023 11:03 PM - edited ‎Oct 18, 2023 11:04 PM
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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 19, 2023 03:10 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 19, 2023 06:18 AM - edited ‎Oct 19, 2023 06:19 AM
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"
)