Date formatting

  • Thread starter Thread starter Niall 84
  • Start date Start date
N

Niall 84

Hope someone can help me with this.

I have two colums's of date and time

i.e. Cell H12 has "05/02/2008 11:03"
and Cell N12 has "07/03/2008 11:35"

H13:H60 and N13:N60 has more times

I need to find the difference between these two times in hours and
have it in Cell O12

The problem i'm having is as follows, I need it to be hours in working
days, i.e Monday - Friday 8AM - 6PM.

Is this possible?

Looking forward to any help possible,

thanks,

Niall.
 
=(NETWORKDAYS(H12,N12)-(WEEKDAY(H12,2)<=5)-(WEEKDAY(N12,2)<=5))*10+
((("18:00"-MIN("18:00",MOD(H12,1)))*(WEEKDAY(H12,2)<=5))+
((MAX("8:00",MOD(N12,1))-"8:00")*(WEEKDAY(N12,2)<=5)))*24
 
Hi Bob,

Thanks very much for the formula,

It does not seem to be working though,

Should the cells be formatted in any particular way?

Thanks,

Niall
 
You need to be using the Analysis Toolpak (the NETWORKDAYS function call
requires it). Click Tools/Add-Ins on Excels menu bar and put a check mark
next to the Analysis Toolpak entry.

For future reference, you should always say what "isn't working" instead of
just saying it "doesn't work" so we have some idea about what is going on at
your end.

Rick
 
Thanks for both the help and the tip.

Much appreciated, and its working perfect now.

Niall.
 
Back
Top