+ Reply to Thread
Results 1 to 8 of 8

Absence Data Calculations

  1. #1
    Registered User
    Join Date
    03-12-2021
    Location
    UK
    MS-Off Ver
    2010
    Posts
    6

    Absence Data Calculations

    Hello.

    I am trying to calculate some data in excel using start and end dates for absence.

    I have some absence data (attached an example) for staff that contains their Start Date and End date of the absence. Basically I need to be able to calculate:

    1 - the number of consecutive months someone has had absence

    2 - the max number of consecutive months someone has been off.

    Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Dawnie75; 03-15-2021 at 01:33 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Absence Data Calculations

    I believe these will work in Excel 2010. Maybe someone has a simplier, more elegant formula 1. but
    In D2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in E2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-12-2021
    Location
    UK
    MS-Off Ver
    2010
    Posts
    6

    Re: Absence Data Calculations

    Hi. This seems to work a treat. Thank you for your help.

  4. #4
    Registered User
    Join Date
    03-12-2021
    Location
    UK
    MS-Off Ver
    2010
    Posts
    6

    Re: Absence Data Calculations

    Hi. I seem to have come across an issue. Both the formulas worked on my personal laptop but when I tried them on my work laptop the second (max) formula didn’t work. It showed me a 1 in the first row but then 0’s down the rest of the rows. Any suggestions? I have the same version of excel on both laptops.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Absence Data Calculations

    Could be a number of reasons. Can you remove confidential information and upload the file? If you want to try to troubleshoot it yourself,
    On the Formula Tab, there's an icon for "Evaluate Formula" What that does is walk you through the sub calculations of the formula and that might help you figure out why it's not working.
    Maybe the date cells are formatted as date but actually include the time?

  6. #6
    Registered User
    Join Date
    03-12-2021
    Location
    UK
    MS-Off Ver
    2010
    Posts
    6

    Re: Absence Data Calculations

    Hi. I managed to get the formula's to work. Thank you.
    There seems to be a couple of issues with the formula calculating the consecutive months
    1 - It doesn't appear to like the dates that cross over two years. ie begin in 2019 and end in 2021. (lines 11 and 17 attached)
    2 - It doesn't add consecutive together if one absence ends in Dec and the next starts in Jan the following year. (lines 3 & 4 attached)

    Is it possible to work round these issues with the formula?
    Attached Files Attached Files

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Absence Data Calculations

    Please try at
    D2
    =IFERROR(DATEDIF(EOMONTH(B2,-1)+1,EOMONTH(C2,0)+1,"m")+IF(A1=A2,CHOOSE(MATCH(DATEDIF(EOMONTH(C1,0)+1,EOMONTH(B2,0)+1,"m"),{0,1,2}),D1-1,D1,0)),D1+IFERROR(DATEDIF(EOMONTH(C1,0)+1,EOMONTH(C2,0)+1,"M"),0))

    E2
    =MAX(INDEX(($A$2:$A$20=A2)*$D$2:$D$20,))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-12-2021
    Location
    UK
    MS-Off Ver
    2010
    Posts
    6

    Re: Absence Data Calculations

    Thank you so much, this works a treat!.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Staff Absence Calculations
    By SirSzymon in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-12-2020, 09:11 AM
  2. adding data to the employee absence schedule template
    By garythecat in forum Excel General
    Replies: 5
    Last Post: 04-15-2020, 02:46 PM
  3. [SOLVED] Creating a summary box from existing employee absence data
    By temp2trim in forum Excel General
    Replies: 2
    Last Post: 04-26-2016, 11:38 AM
  4. Replies: 5
    Last Post: 10-24-2014, 09:13 AM
  5. Excel 2007 : generating "absence data"
    By lraebies in forum Excel General
    Replies: 2
    Last Post: 09-29-2010, 11:38 AM
  6. Absence Monitoring Help
    By Lexx Diggler in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2009, 05:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1