Search code examples
excel-formulatime-tracking

Hours Worked Based on Hire and Termination Date in Excel

I am looking to calculate hours worked in a month based on a salaried employee's hire date and termination date (if it exists), based on a salaried worker's hours of 2,080 hours in a year. I have been basing per month on the Excel formula "NETWORKINGDAYS" and multiplying by 8 (hours per day) to turn the days into hours. I have done this in a manual fashion in the past - filtering to see if someone was employed during the full month - meaning they did not have a hire date or a termination date during a given month. I would then go back and use the NETWORKINGDAYS formula to handle the employee records that did have a hire or termination date within the given month.

I am looking for a way to automate this calculation. Criteria I need to handle the below, using January 2023 as an example. January 2023 has 176 salaried working hours in it (based on US calendar):

  • If an employee has a start date prior to or on January 1st, 2023 and no termination date (or a termination date greater than the end of the month), calculate 176 hours.
  • If an employee has a start date prior to or on January 1st, 2023 and a termination date within that month, calculate hours based on starting date of 1.1.2023 and end date of the termination date.
  • If an employee has a start date prior to January 1st, 2023 and a termination date before January 1st, 2023, calculate 0 hours.
  • If an employee has a start date later than January 1st, 2023 but within the month, calculate working hours based on the start date and end date of the month.
  • If an employee has a start date greater than January 1st, 2023 and is not within that month, calculate hours as 0.

I am trying to make this extendable - right now I am doing this month by month, one month at a time, but I want to be able to calculate an employee's monthly hours for a whole year using one formula.

Formula examples from the attached image:

Test Employee, February 2023 hours: =NETWORKDAYS("2/1/2023",E3)*8

Test 2 Employee, February 2023 hours: =NETWORKDAYS(D4,EOMONTH("2/1/2023",0))*8

Test 3 Employee, January 2023 hours: =NETWORKDAYS("1/1/2023",EOMONTH("1/1/2023",0))*8

Excel example attached

Solution

  • I would create a helper row above the labels with an actual date like 1/1/23, you can hide this row after but it will save you the trouble of calculating. I'll assume the table we're looking at goes from A1:G4, A1:G1 being the headers. If you move this down and put the dates above those headers this is the formula I'd write into E3:G5. (specifically to E3), referencing the dates above the header in now the new E1:G1...

    First this checks if the start date is after the end of that columns month, if so 0. Then it checks that if the term date is not blank and earlier than that month also 0. With those out of the way we start the networkdays but within the start date, we check if it's the same month & year as the date. If it is, we want the greater of the 2, start date, otherwise the date. Similarly for the end date if it's the same month/year as the term date then use the term date otherwise use the eomonth of that date.

    Added note, with NETWORKDAYS if you need to you can add a list of the holidays you want excluded as the final condition in order to have those considered as well.

    =IF(OR( $C3>EOMONTH(E$1,0),AND(ISNUMBER($D3), $D3<E$1)),0, NETWORKDAYS( IF(AND(MONTH($C3)=MONTH(E$1), YEAR(E$1)=YEAR($C3)),$C3,E$1), IF(AND(MONTH($D3)=MONTH(E$1), YEAR(E$1)=YEAR($D3)),$D3,EOMONTH(E$1,0))))*8

    The Results