Calculate working hours between two dates

L

Legacy 98055

Guest

• Aug 17, 2002

• #2

Will you be excluding Saturdays, Sundays. and holidays?
Tom

 

Upvote 0

P

Pat from Melb.

New Member

Joined

Aug 16, 2002

Messages

4

• Aug 17, 2002

• #3

Thanks for the prompt reply.
Yes I will be excluding Saturdays , Sundays and Public Holidays

 

Upvote 0

L

Legacy 98055

Guest

• Aug 17, 2002

• #4

Is 51 hours and 15 minutes the answer to your example above?
I have an old UDF I wrote to figure net hours worked. Wanted to see if it might help you out???
If you would like to include the valid holiday dates for (Australia?), say... for the next year, I can incorporate these holidays into the function.
Not hardcoded, but I would like to use valid holiday dates so you can test it out for yourself.
Thanks,
Tom
This message was edited by TsTom on 2002-08-17 23:43

 

Upvote 0

P

Pat from Melb.

New Member

Joined

Aug 16, 2002

Messages

4

• Aug 18, 2002

• #5

On 2002-08-17 23:29, TsTom wrote:
Is 51 hours and 15 minutes the answer to your example above?
Yes , this is the answer, Thaks.
I have an old UDF I wrote to figure net hours worked. Wanted to see if it might help you out???
Yes, I would appreciate this.
If you would like to include the valid holiday dates for (Australia?), say... for the next year, I can incorporate these holidays into the function.
Australian Holidays
26- Jan (Aust. day)
25- Dec (Christmas day)
26-Dec (boxing Day)
1-Jan (new years day)
26- Jan (Aust. day)

Thanks
PAt

Click to expand...

 

Upvote 0

D

Dave Patton

Well-known Member

Joined

Feb 15, 2002

Messages

6,357

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 18, 2002

• #6

Hello Pat
credit for following to Daniel.M (daniel.maher@bigfoot.com)

1. your times in Col A and Col B, threshold times in cells named sT and eT, holiday list named H_D (it is not essential to name the ranges)
2. ensure Analysis ToolPak is installed
3. result in hours
=NETWORKDAYS(A3,B3,H_D)*(eT-sT)-(NETWORKDAYS(A3,A3,H_D)*MAX(0,MIN(eT,MOD(A3,1))-sT))-(NETWORKDAYS(B3,B3,H_D)*MAX(0,eT-MAX(MOD(B3,1),sT)))

4. result converted to rounded decimal (relevant if you multiply result by a rate per hour)
=ROUND((NETWORKDAYS(A1,B1,H_D)*(eT-sT)-(NETWORKDAYS(A1,A1,H_D)*MAX(0,MIN(eT,MOD(A1,1))-sT))-(NETWORKDAYS(B1,B1,H_D)*MAX(0,eT-MAX(MOD(B1,1),sT))))*24,2)

Note. adjust the references to fit your information.

HTH Dave

 

Upvote 0

L

Legacy 98055

Guest

• Aug 18, 2002

• #7

Hi Dave.
I tried the above function with a return of 3:15?

=NETWORKDAYS(A3,B3,H_D)*(eT-sT)-(NETWORKDAYS(A3,A3,H_D)*MAX(0,MIN(eT,MOD(A3,1))-sT))-(NETWORKDAYS(B3,B3,H_D)*MAX(0,eT-MAX(MOD(B3,1),sT)))

Cell A3 = 8/6/2002 9:00:00 AM
Cell B3 = 8/13/2002 3:15:00 PM
Range("sT") = 8:00:00 AM
Range("eT") = 5:00:00 PM
Range("H_D") = 25-Dec-02,26-Dec-02,1-Jan-03,26-Jan-03

The answer should be 51 hrs and 15 minutes.
Do I have the arguments set up correctly?

Thanks,
Tom

PS Nevermind. It works! I had the formatting wrong. Returned 51.25
This message was edited by TsTom on 2002-08-18 11:42

 

Upvote 0

P

Pat from Melb.

New Member

Joined

Aug 16, 2002

Messages

4

• Aug 18, 2002

• #8

Tom and dave,
Thanks heaps !!!
you have got me out of a bind with the boss.

I am very grateful

 

Upvote 0

P

purplegardener

New Member

Joined

Jun 22, 2005

Messages

31

• Aug 8, 2005

• #9

Update on this formula w/calendar - PLEASE HELP TODAY......

Please refer to the e-mail located at www.mrexcel.com/board2/printview.php?t=20483&start=0. We are looking at implementing this type of format, but we will need to use a variable calendar (meaning that the work day won't ALWAYS be, say 6:00-4:00 p.m. ). Does the user or Mr. Excel know how to implement this into the equation? We'd like to get an answer today, if possible.

Thanks for your awesome help!

Laura

 

Upvote 0

C

cazan

Board Regular

Joined

Feb 23, 2004

Messages

52

• Aug 9, 2005

• #10

Hello Everyone,

I have a need for calculating the hours in decimal format from a start date cell to an end date cell in the format mm/dd/yyyy hh:mm in much the same way described here in this post. I vaguely remember how to setup the ranges, so all you have to do is reference "sT", "eT" and "H_D". Could someone refresh my memory on this, please.

 

Upvote 0