Calculating working hours between 2 dates

G

Guest

Guest
I wish to calculate how many WORKING hours between 2 dates. The ricky bit is I want it to also disregard weekends and ideally bank holidays. eg 10/02/02 09:00 and 14/02/02 10:00 (where 11th and 12 are Sat/Sun), I want it to come back with 9 hours.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
On 2002-02-23 03:31, Anonymous wrote:
I wish to calculate how many WORKING hours between 2 dates. The ricky bit is I want it to also disregard weekends and ideally bank holidays. eg 10/02/02 09:00 and 14/02/02 10:00 (where 11th and 12 are Sat/Sun), I want it to come back with 9 hours.

Just curious: How did you compute 9 hours?
 
Upvote 0
Sorry.....I knew what I meant.....8 working hours a day (9 till 5 is 8 hours)and in my example i quoted till 10am the next working day i.e. 8 hours plus 1 hour the next day. Basically, if I use networkdays function I get a result of 1, where as I want to know the number of hours, not days! I need the calculation to take weekends out of the result.
 
Upvote 0
Edited:

=IF(NETWORKDAYS(A1,B1)>2,(NETWORKDAYS(A1,B1)-2)*8+(("17:00"-MOD(A1,1))+(MOD(B1,1)-"9:00"))*24,IF(INT(A1)=INT(B1),(B1-A1)*24,(("17:00"-MOD(A1,1))+(MOD(B1,1)-"9:00"))*24))
to allow for correct calc on same day.

Original
=IF(NETWORKDAYS(A1,B1)>2,(NETWORKDAYS(A1,B1)-2)*8+(("17:00"-MOD(A1,1))+(MOD(B1,1)-"9:00"))*24,(("17:00"-MOD(A1,1))+(MOD(B1,1)-"9:00"))*24)

do the trick?
start time date is A1, end time date is B1.


On 2002-02-23 07:42, Anonymous wrote:
Sorry.....I knew what I meant.....8 working hours a day (9 till 5 is 8 hours)and in my example i quoted till 10am the next working day i.e. 8 hours plus 1 hour the next day. Basically, if I use networkdays function I get a result of 1, where as I want to know the number of hours, not days! I need the calculation to take weekends out of the result.
This message was edited by IML on 2002-02-23 09:55
 
Upvote 0
Fantastic!! Many thanks for your help. Works a treat! (how come you know how to do this?)
 
Upvote 0
I'm glad it works for you. As for why I know how, that's a good question. I think it all goes back to reading a Mark W. posting for probably the 100th time that he said excel stores time as a fraction of a day (1 = 24 hours). Once you understand that and the odd formats that sometime pop up, a farily simply formual can usually solve the problem. By the way, if you need this formula to show true time, just divide it by 24 with a [h]:mm format
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top