Calculate working hours between two dates

[Pat from Melb.]

How do I calculate the number of working hours between two dates
For example day 1 06-August-02 09:00am
day 2 13-August-02 03:15pm
I need to know the working hours between these two dates, based on a working day starting at 8:00am in the morning and finishing at 5:00pm.
Please excuse me I am new at this.

 

[barry houdini]

What do "sT" and "eT" refer to - are those the start and end times of the working day, e.g. 08:00 and 17:00?

Will you only have start and end times which fall within the working times?

 

[cazan]

Sorry, I'll figure out what it was that I was trying to remember. But I follow you. They're just variables. I'll work with it and see how it goes for me.

 

[barry houdini]

If your start time/date is in A1 and your end time/date in B1 and both of these fall within working hours then this formula

=24*((NETWORKDAYS(A1,B1,H_D)-1)*(ET-ST)+MOD(B1,1)-MOD(A1,1))

formatted as number

will give you the elapsed working hours between the two, in decimal format, assuming

H_D is a list of holidays
ST is start time of the working day
ET is end time of the working day

 

[cazan]

What if you do not want to include holidays? H_D would obviously reference a range of cells with holiday dates. Or, is the list generated differently. Please excuse my ignorance, since I have not worked with type of formula before. I have been hunting around in the message board to get familiar with the details of it.

Also, I take it the ST and ET are preferred to be in 24 hour format.

 

[barry houdini]

The 3rd argument of the NETWORKDAYS function is optional, if you don't want or need holidays just use

=24*((NETWORKDAYS(A1,B1)-1)*(ET-ST)+MOD(B1,1)-MOD(A1,1))

ET and ST can be in any format you want, as long as they are times, or you can just dispense with the named ranges altogether and put them in the formula, e.g.

=24*((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))

 

[cazan]

Thanks Barry,

It seems to be working, except I get a "-9.0" result when the same date is placed in both A1 and B1. I am trying to figure out what is causing it. Please excuse my brain deadness,... I have been away from Excel formulas for while.

 

[cazan]

Can't figure out the "-9.0"

Project Schedule.XLS
	F	G	H	I
2	STARTDATE	ENDDATE	DURATION(DY)	DURATION(HR)
3
4	07/14/200514:56	07/14/200519:56		5.0
5	07/16/200508:00	07/16/200508:00		-9.0
6
7	07/22/200500:00	07/22/200500:00		0.0
8	07/22/200500:00	07/26/200500:00		18.0
sheet1

P.S. I think my HTMLMaker is broken!

Any ideas?

 

[barry houdini]

This formula will only work correctly when your start and end times fall on working days within working hours - because 16th July 2005 was a Saturday it doesn't work correctly in that case.

If you want to have start and end times which fall outside working hours you can do it but you need a different formula....

You can fix HTML maker - read the known issues

http://www.mrexcel.com/board2/viewtopic.php?t=92622#problems

 

[cazan]

Darn it! I knew it was something easy and right under my nose. I should have know that was the day I flew to Aruba for Vacation,... on a Saturday. No wonder I'm brain dead. I drank too much rum and was exposed to the sun for too long.

Thank you, Barry!

 

[cazan]

I believe the error of the display of the HTMLMaker code has something to do with the IE 6.0 pop-up blocker installed on my system. I have to comb through the website to find anyone else who has the same problem.

Thanks,
Chris A. Zanowick