+ Reply to Thread
Results 1 to 6 of 6

Calculate Absence in a Time sheet based on working days per week!

  1. #1
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    47

    Exclamation Calculate Absence in a Time sheet based on working days per week!

    The attached table shows 3 Group of employees, each group have different count of employees. And each group work on a different schedule, one on M-T-W, another from Mon-Fri, and another on Sat-Sun.

    The table is to calculate how many employees were absent per week (Average and Percent), BUT based on the sessions (Workdays per week) in Column E
    and how many were present.

    Absence Average ?
    Absence Percentage ?
    Present ?

    The numbers in columns F to L is the number of employees that were absent on that day of the week.

    It could be so simple but with my knowledge in Excel I couldn't figure it out with a formula.

    I appreciate your help.
    Thanks
    Attached Files Attached Files
    Last edited by BassemCh; 09-30-2021 at 09:30 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,712

    Re: Calculate Absence in a Time sheet based on working days per week!

    Excel 2016 (Windows) 32 bit
    M
    N
    2
    Absence Average
    Absence Percentage
    3
    1.333333333
    13%
    4
    1.2
    20%
    5
    3.5
    35%
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    M
    N
    2
    Absence Average
    Absence Percentage
    3
    =AVERAGE(Table1[@[Mon]:[Wed]])
    =SUM(Table1[@[Mon]:[Wed]])/[@[Employees total]]
    4
    =AVERAGE(Table1[@[Mon]:[Fri]])
    =SUM(Table1[@[Mon]:[Fri]])/[@[Employees total]]
    5
    =AVERAGE(Table1[@[Sat]:[Sun]])
    =SUM(Table1[@[Sat]:[Sun]])/[@[Employees total]]
    Sheet: Sheet1

    I did not complete the last column because I don't understand your needs. Please explain your criteria/logic for this item as it could vary depending upon your needs.

  3. #3
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    47

    Re: Calculate Absence in a Time sheet based on working days per week!

    Thanks a lot alansidman!

    That's great! but is there a way to apply the same formula for all rows from [Mon] to [Sun] but to exclude empty cells?

    So that I do not need to change the formula for each row?

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,150

    Re: Calculate Absence in a Time sheet based on working days per week!

    Change the schedule from M-T-W, M-T-W-T-F and S-S TO [Mon]:[Wed], [Mon]:[Fri] and [Sat]:[Sun] respectively.

    Try the following formulas:
    M3:=AVERAGE(INDIRECT("Table1[@"&[@Schedule]&"]"))
    N3:=SUM(INDIRECT("Table1[@"&[@Schedule]&"]"))/([@[Employees total]]*COUNT(INDIRECT("Table1[@"&[@Schedule]&"]")))
    O3:=100%-[@[Absence Percentage]]

  5. #5
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    47

    Re: Calculate Absence in a Time sheet based on working days per week!

    Thank you all, it works.

  6. #6
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets
    Posts
    355

    Re: Calculate Absence in a Time sheet based on working days per week!

    @josephteh

    im learning as i see some of the interesting methods used, and your solution here interests me.

    i would like to ask, how would someone enter the Schedule if the person/group worked specific days with or without a range of days?
    for example (Mon, Wed, Fri), or (Mon to Thu and Sat)

+ 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. Need a formula to calculate the working time for the week
    By skeener118 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-02-2019, 07:44 PM
  2. [SOLVED] Calculate # days in week based on date range
    By Peelah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2016, 07:01 PM
  3. Calculate sick absence by days per month.
    By Pegs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2013, 06:57 AM
  4. Replies: 10
    Last Post: 09-25-2012, 09:24 AM
  5. Replies: 0
    Last Post: 09-13-2012, 05:28 AM
  6. [SOLVED] Using days of the week to calculate payment to part-time workers
    By pschatz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2011, 12:53 AM
  7. How to calculate days of the week in a given time frame
    By fsoares22 in forum Excel General
    Replies: 2
    Last Post: 03-17-2011, 12:09 PM

Tags for this Thread

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