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!

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" <(E-Mail Removed)> wrote in message
news:E697BB9F-4791-4044-917C-(E-Mail Removed)...
> 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!

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

"Bernie Deitrick" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:E697BB9F-4791-4044-917C-(E-Mail Removed)...
> > 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!
>
>
>

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


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

Bernie
MS Excel MVP