Forum Discussion
Can excel assess if two dates are continuous and then run a formula based on this?
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