Forum Discussion

Jack_Roberts's avatar
Jack_Roberts
Copper Contributor
Dec 09, 2021

How to count periods of absence in excel

Hey, I am creating a spreadsheet to track holidays and absence. I need to figure out a way of counting periods of absence. For example, in the attachment I want TEST 1 to show as one period of absenc...
Excel
Formulas and Functions
office 365
HansVogelaar's avatar
HansVogelaar
MVP

Olivier_Languille 

Thanks. Is S/SH the only combination that should be treated this way, or are there others - if so, please list them in a reply.

Olivier_Languille's avatar
Olivier_Languille
Copper Contributor
Nov 10, 2023
Hans
the combination can be S, SH, U,BH

Thank you
  • Olivier_Languille's avatar
    Olivier_Languille
    Copper Contributor
    Nov 17, 2023
    Hans
    Apologies only just been able to look at it.
    It seems to be working in the correct manner so hopefully what you have done has done the trick.

    Thanks again for your assistance with this as it will be very useful.
  • Olivier_Languille's avatar
    Olivier_Languille
    Copper Contributor
    Nov 14, 2023

    HansVogelaar 

     

    Hans

    Thank you for your help so far.

    I have been through it and some period count do not seem to be correct. Please see on the attached the manual check in red.

    Really unsure as to the reason why as this goes beyond my knowledge and understanding!!!

     

    Would appreciate if you have time to have a look.

     

    Thanks again

  • Olivier_Languille's avatar
    Olivier_Languille
    Copper Contributor
    Nov 13, 2023
    Hans

    Apologies. You are correct as i did not pay attention to the days in the column. The statement is correct in terms of ignoring the weekends.

    Did you have a look at the file i attached previously as i do not seem to get the right outcome.

    Thanks again for your help.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 13, 2023

    Olivier_Languille 

    Apparently, you want something else than Jack_Roberts who started this discussion. They wanted to count periods as if weekend days didn't exist. For example, if an employee is sick on:

    Thu 9 Nov, Fri 10 Nov, Mon 13 Nov and Tue 14 Nov, it should count as 1 period since Sat 11 Nov and Sun 12 Nov should be ignored.

    Can you explain what you want?

     

  • Olivier_Languille's avatar
    Olivier_Languille
    Copper Contributor
    Nov 13, 2023

    HansVogelaar 

     

    Thanks for your file.

     

    The period count is not what i would have expected. In column G i would have expected to be 2 periods, column H is correct, column I should be 4, column J should be 5, etc....

    Not sure if i am missing something.

     

    I have attached a snap shot of my file. Again the period count seems to be incorrect. For example line 20 does not have any of the S or SH or U but it is still counting 8 periods whilst i would expect it to be 0.

    I have remove the BH value as i do not need it to be counted

     

    Thanks

Resources