Forum Discussion

Jack_Roberts's avatar
Jack_Roberts
Copper Contributor
Dec 09, 2021
Solved

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
Dagmara912's avatar
Dagmara912
Copper Contributor
Mar 03, 2023

HansVogelaar there is a mixture. Some work Sat - Wed, some Sunday only and some weekend only. My file has very similar layout to the one shown in this thread. I can make some modifications if needed to help with the calculations. Maybe a code in one of the rows to identify staff who work on above days?

HansVogelaar's avatar
HansVogelaar
MVP
Mar 03, 2023

Dagmara912 

See the attached workbook. You can now optionally specify a workdays string.

The workdays string must be seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a workday and 0 represents a non-workday.

For example, "0000011" means that only Saturday and Sunday are workdays.

If you omit the workdays string, "1111100" is assumed, i.e. workdays are Monday to Friday.

  • Dagmara912's avatar
    Dagmara912
    Copper Contributor
    Mar 03, 2023

    HansVogelaar this is genius, thank you. How can I make the formula not to be case sensitive?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Mar 03, 2023

      Dagmara912 

      Change the line

                      If coderange(i).Value = code Then

      to

                      If UCase(coderange(i).Value) = UCase(code) Then
      • Dagmara912's avatar
        Dagmara912
        Copper Contributor
        Mar 03, 2023
        Thank you so much

Resources