Excel date and time calculation

[Guest]

I have two date sin two cells (15/6/2004 10:32) and (23/6/2004 15:00). I need to calculate the hours and minutes between the two dates excluding weekends and only counting the time between 08:00 and 17:00. Is this possible and how can it be done.

 

[Bob Phillips]

Here's a shot

=(NETWORKDAYS(A1,A2)-1)*9+((A2-INT(A2))-(A1-INT(A1)))*24

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have two date sin two cells (15/6/2004 10:32) and (23/6/2004 15:00). I

need to calculate the hours and minutes between the two dates excluding
weekends and only counting the time between 08:00 and 17:00. Is this
possible and how can it be done.

 

[Guest]

Thanks Bob. That will impress them at work on Monday. I had to format the cell to give a numeric reading and put the "add in".

Thanks

 

[Daniel.M]

Hi,

Is it possible you start working outside the business hours (so outside 8:00 to
17:00)?
Is it possible you start working on a week-end?
Is it possible you end working outside the business hours (so outside 8:00 to
17:00)?
Is it possible you end working on a week-end?

Regards,

Daniel M.

I have two date sin two cells (15/6/2004 10:32) and (23/6/2004 15:00). I need

to calculate the hours and minutes between the two dates excluding weekends and
only counting the time between 08:00 and 17:00. Is this possible and how can it
be done.

 

[Tom Ogilvy]

I believe the reason DanielM asks his questions is that if you put in time
like

6/24/04 18:00
6/25/04 20:00

then answer produced is 11 which would appear to be incorrect.

Perhaps you want to look at Chip Pearson's page on this topic:

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

--
Regards,
Tom Ogilvy

Thanks Bob. That will impress them at work on Monday. I had to format

the cell to give a numeric reading and put the "add in".

 

[Daniel.M]

Hi Tom,

Perhaps you want to look at Chip Pearson's page on this topic:
http://www.cpearson.com/excel/DateTimeWS.htm

This reference is very frequently given and contains a formula calculating the
hours. However, it shows the same problems when StartDT and/or EndDT are
'offlimit' (aka not within DayStart and DayEnd)
BTW, no disrespect for a very good site nonetheless.

We're talking about the second formula (the one, calculating the hours):
"=IF(AND(INT(StartDT)=INT ... DayEnd-DayStart)),2))))))

Example: For DayStart=8:00 and DayEnd=17:00 ' the user's param

StartDt: Thu 2002-08-15 10:00
EndDt: Thu 2002-08-15 18:00
Result = 8
Expected result : 7:00 (or 7 if you want)

The only formula that works in all cases:
=IF(StartDT>EndDT,0,NETWORKDAYS(StartDT,EndDT,Holidays)*(DayEnd-DayStart)
-IF(NETWORKDAYS(StartDT,StartDT,Holidays),
MAX(0,MIN(DayEnd,MOD(StartDT,1))-DayStart),0)
-IF(NETWORKDAYS(EndDT,EndDT,Holidays),
MAX(0,DayEnd-MAX(MOD(EndDT,1),DayStart)),0))

It's shorter and covers all cases AFAIK.

Regards,

Daniel M.

 

[Daniel.M]

The only formula that works in all cases:

I meant: "One formula that works in all cases:"


There are others I'm sure.

Regards,

Daniel M.