Forum Discussion
Can excel assess if two dates are continuous and then run a formula based on this?
Hi all
I have a spreadsheet that I use to calculate career disruptions. A disruption is deemed eligible if it is >=90 days, and ineligible if <90 days.
Each period of disruption is calculated in separate rows, and the total days of disruption (column J) is calculated using the Full Time Equivalent missed multiplied by the time period of disruption [=(H16-G16)*E16].
I am wondering if there is any formula to be able to assess if the start date of one row (e.g., G17) is continuous with the end date of the previous row (e.g., H16) and if yes, to then add up the total days of the continuous time period of disruption?
In my example below, rows 16-18 are not eligible to be counted as a disruption as they are all individually <90 days. But the three periods of disruption are continuous, and added together would be >90 days and therefore eligible.
This is a template that is filled in by our external stakeholders, so i am trying to utilize formulas and automation as much as possible
Let me know if my query is not clear/if further info is required.
Thanks for any help!
Jacqui
JacquelynAbbott
Here's my approach:1. Check for continuity (column K)
=IF(G17=H16+1, "Yes", "No")2. Sum continuous days (column L), you need to create a running total of continuous disruptions, by summing the disruptions only if they're continuous.
=IF(G17=H16+1, J16 + (H17-G17+1)*E17, (H17-G17+1)*E17)
It will check if the current row start date is continuous with the previous row's end date, then it will add the disruption days to the previous row's total; otherwise, it starts a new count.3. Finally, to determine if the disruption (column M) is eligible (>=90days), you can use:
=IF(SUMIF(F$16:F17,"Yes",J$16:J17)>=90, "Eligible", "Ineligible")
This will sums up the days for all continuous disruption that are marked with "Yes" and checks if the sum >=90.Hi Rodrigo_
Thanks for your help 🙂
I've had a go at what you suggested.
Column K Yes or No to indicate if it's continuous is good.
With Column L, I am wondering why you suggest =IF(G17=H16+1, J16 + (H17-G17+1)*E17, (H17-G17+1)*E17) rather than =IF(K17='"YES",J16+J17,J17), as this is adding an extra day to the count (see below, column L vs N). Is there a particular reason you recommend your method?
However, I still face the same issue using either formula, that if an episode of leave had >2 continuous periods (as is the case for rows 16-18), how do I create a formula that will sum J16:J18, rather than the current way which is summing J16 and J17, and then J17 and J18 (which is inaccurate).
After thinking about it this morning, I've gone a different way with it. I've put in the option to 'group' events as per the following:
I then have a separate table for the groups of disruption and use the following formula to calculate if they're eligible: =SUMIF($B$23:$B$36,D11,($I$23:$I$36))
The only issue i see so far is that a user could group two completely separate periods of disruption as the same event (e.g., both Disruption 1). So I have included your first formula to indicate if the dates are continuous, and hopefully that will prompt people in ensuring that they complete it accurately.
Thanks again for your help!
Jacqui
Here's an Excel 365 solution.
My formula uses named items:
=LET( x, DROP(StartDate, 1), y, DROP(EndDate, -1), g, VSTACK(0, x) - VSTACK(0, y) = 1, h, ABS(VSTACK(y, 0) - VSTACK(x, 0)) = 1, d, DaysDisruption >= 90, filtered, FILTER(DaysDisruption, g + h + d, 0), SUM(filtered) )
