Calculate time in Excel

[icsupt]

I am trying to calculate working time between two dates.

Example:
A1 = 7/28/2004 7:00:00 AM
A2 = 8/02/2004 7:00:00 AM

I would like to calculate the minutes lapsed between the two dates, however I do not want to include any Saturdays, Sundays or holidays.

Is there a way to accomplish this?

Thanks in advance.

 

[xlbo]

are your times always going to be the same ??

Also, you say "Working Time" - is that 9-5 ?? 8-6 ?? need more detail here

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PeterMoran]

Hi icsupt,

Try this url for Chip Pearson's site:

http://www.cpearson.com/excel/DateTimeWS.htm

This does not give times in minutes - to do this you will have to format the cell as Custom and modify one to be "[mm]" (no quotes).

Good Luck!

Peter Moran

 

[icsupt]

Excellent question Geoff.

We should only consider:
Saturday starting at 12:00 AM
Sunday ending at 11:59 PM.

Thanks again.

 

[bluedragon2]

Take a look at the networkdays function...

It is part of one of the add-ons...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[icsupt]

Networkdays only gives me Days. I need hours and minutes.

 

[byundt]

Be careful with Chip Pearson's formula (link in PeterMoran's comment). Sometimes it works, and other times it gives spurious results--and it's so complicated that you really don't want to debug it.

 

[TechVol]

I'm not sure this is what you want but.....

A1 = 7/28/2004 7:00:00 AM (time format 3/14/01 1:30 PM)
A2 = 8/02/2004 7:00:00 AM (time format 3/14/01 1:30 PM)
A3 = ((A2-A1)*1440)-2880) (number format)

((date stamp1 - date stamp2))* total min. in day) - total minutes to leave out in Saturday and Sunday

** Genius is one percent inspiration and Ninety-nine percent perspiration **

 

[torturedmind]

networkdays function can give what you need. it's just a matter of converting days to hours and hours to minutes. (and if you like, minutes to seconds).

hope this helps. peace!

kilroy
philippines

"Illegitimis non carborundum!"