Calculate time differential - Mon - Fri workday basis

[Don]

I'd like to calculate the time worked on a project using the Start and End
times, however, the resultant number has to take into consideration only
those hours worked during a regular day, and also has to account for no work
being done on the weekends. For example, if a standard workday/workweek is
8a - 5p Monday thru Friday and the Start time is Friday at 3p and End time is
Monday at 11a, the actual work time would be 2 hours on Friday (3p - 5p) and
3 hours on Monday (8a - 11a), for a total of 5 hours. Similarly, Start time
of Tuesday at 4p and End time of Thursday at 4p would equate to 18 hours, 1
hour on Tuesday, 9 on Wednesday, and 8 on Thursday. Can someone help me
calculate this in excel? Thanks!

 

[Bernie Deitrick]

Don,

With the start date and time in A2, and the end date and time in B2, this formula should work - the
values must the date/times and not just strings that look like dates and times:

=IF(B2>A2,MAX(0,(NETWORKDAYS(A2,B2)-2))*9 +
IF((INT(B2)-INT(A2))>0,"17:00"-(A2-INT(A2))+(B2-INT(B2)-"8:00"),(B2-A2))*24,"Bad times")

If the NETWORKDAYS is not available (returns the #NAME? error) install and load the Analysis ToolPak
add-in. Also, you can add a list of holidays to the NETWORKDAYS function as a third argument to
account for those days in the formula.


HTH,
Bernie
MS Excel MVP

 

[Don]

Thanks Bernie!!! Exactly what I was looking for!!

 

[Bernie Deitrick]

Thanks Bernie!!! Exactly what I was looking for!!

Glad to hear it -- and thanks for letting me know.

Bernie
MS Excel MVP