• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

add up consecutive days of illness|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / add up consecutive days of illness|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…add up consecutive days of illness
sp_PrintTopic sp_TopicIcon
add up consecutive days of illness
Avatar
Nadine Claessens

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 23, 2020
sp_UserOfflineSmall Offline
1
January 24, 2020 - 7:18 pm
sp_Permalink sp_Print

Hello, 

I have a list (several hundreds of lines) of employees who have been ill during say, the past three months.  It contains a start date, an end date, the number of days and an employee ID-number.  I would like to see for each employee, the number of accumulated, consecutive days of illness. 

So, if someone has been ill from 9/9/2019 until 7/10/2019 and then was prolonged from 8/10/2019 up to 11/10/2019 it should add up to 33 days. If some time later, this person has also been ill from 14/10/2019 up to 18/10/2019 this only represents 4 days.

I tried different approaches but until now, none of them worked. 

I would very much appreciate if someone could find a solution! 

 

Kind regards,

Nadine

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
January 24, 2020 - 8:44 pm
sp_Permalink sp_Print

Try the attached. It is assuming that the rows showing the overlapping dates would be in order.

 

Column F =IF(B2=A3-1,"Con","Not"). Checking if dates overlap

=IF(B2=A3-1,C2+C3,IF(F1="CON","",C2)). Adds days together if overlap, otherwise just the number in C (unless previous day was consecutive.

 

Only had a quick check but seems to be okay

Avatar
Nadine Claessens

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 23, 2020
sp_UserOfflineSmall Offline
3
January 24, 2020 - 10:24 pm
sp_Permalink sp_Print

Hi Purfleet, 

 

thank you for the answer but I believe it's not quite right.  When for example, in cel A12 you enter 6/1/2020 and in cell B12 you enter 8/1/2020, it adds up the days although two different persons are concerned (see employee ID).  The employee ID should be taken into account as well. 

Also if you have three consecutive periods, it adds up period one and two and then again period two and three.... 

 

Kind regards

 

Nadine 

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
January 25, 2020 - 7:21 am
sp_Permalink sp_Print

Sorry didn’t think about employee - the attached fixes that issue.

We could extend the formula to work for 3 occurrences, but then I suppose 4 are possible and so on and the formula would soon get unwieldy

Avatar
Nadine Claessens

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 23, 2020
sp_UserOfflineSmall Offline
5
January 25, 2020 - 5:50 pm
sp_Permalink sp_Print

Hello Purfleet,

 

Thanks for your help, it sure helps a lot!  But as you already mentioned, it is indeed possible to have more, say 6 or 7 lines of illness per person.  In rows 37 up to 39, there are 3 consecutive periods that should add up 51 but the number of days in columns H does not match....

 

Kind regards,

 

Nadine

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
6
January 27, 2020 - 7:49 pm
sp_Permalink sp_Print

If your data is sorted like the sample, and you don't mind the consecutive totals appearing at the end of the sequence rather than the start, you could use:

 

=IF(IF(D2=D3,A3-B2,"")=1,"",SUM(C$2:C2)-SUM(E$1:E1))

 

in E2, and copy down.

sp_AnswersTopicAnswer
Answers Post
Avatar
Nadine Claessens

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 23, 2020
sp_UserOfflineSmall Offline
7
January 27, 2020 - 9:46 pm
sp_Permalink sp_Print

Hello Velouria, 

 

I tried the suggested solution and it just works out great!  I very much appreciate your help for I would have been struggling (in vain) for a  long time!!! 

Might I just ask to explain the formula please?  So, if the employee ID corresponds in both lines, you substract the second start date from the one in the first line, otherwise, you get a blank cel. But why the '=1"? 

and the rest of the formula starting with 'sum' is not clear to me either..... 

 

Kind regards

 

Nadine 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
8
January 28, 2020 - 12:03 am
sp_Permalink sp_Print

Hi Nadine,

That part works out the number of days between the end date of one row and the start date of the next row, assuming the employee id is the same. If that returns 1, then it's a consecutive absence, so the formula simply returns "" - the total calculation is only done for the last of the consecutive periods.

When the calculation is done, it simply totals up the number of days (for all employees/dates) from the start of the data to the current row, and deducts the totals previously calculated in the formula column, which results in the balance for the current absence period.

 

Hopefully that makes sense?

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
9
January 28, 2020 - 5:15 pm
sp_Permalink sp_Print

Velouria said
If your data is sorted like the sample, and you don't mind the consecutive totals appearing at the end of the sequence rather than the start, you could use:

 

=IF(IF(D2=D3,A3-B2,"")=1,"",SUM(C$2:C2)-SUM(E$1:E1))

 

in E2, and copy down.  

I love that formula - if within and if and couple expanding range.

Beautiful 

Avatar
Nadine Claessens

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 23, 2020
sp_UserOfflineSmall Offline
10
January 28, 2020 - 8:45 pm
sp_Permalink sp_Print

Hello Velouria, 

I did not understand the formula quite well and tried to simplify it and the results are the same: 

I replaced your formula

=ALS(ALS(AB2=AB3;H3-I2;"")=1;"";SOM(L$2:L2)-SOM(AZ$1:AZ1))

by 

'=ALS(EN(AB2=AB3;H3-I2=1);"";SOM(L$2:L2)-SOM(AZ$1:AZ1))

 

Thanks again for helping me out!!!! 

 

Kind regards

Nadine 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Nada Perovic, Denise Lloyd
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.