Difference of time

[Raj]

Hi

I need to calculate the difference of time between to dates. Here is my
requirement

Column M2: 4/10/2009 12:45:00 PM
Column N2: 4/14/2009 8:11:00 AM

I would like to display the difference of this 2 columns in hh:mm format.
One important thing to note is we should not calculate weekends(Saturday and
Sunday)
And the business hours are from 9 AM to 6.30PM. So we have consider while
calculating the difference.

Can anyone please help me to resolve this.

Thanks in Advance
Raj

 

[Mike H]

Hi,

try this

=((NETWORKDAYS(M2,N2,Holidays)-1)*("18:30"-"09:00")+MOD(N2,1)-MOD(M2,1))

Holidays is a named range that can contain any dates of holidays you also
want to onit from the calculation or you can leave it out.

=((NETWORKDAYS(M2,N2)-1)*("18:30"-"09:00")+MOD(N2,1)-MOD(M2,1))

Mike

 

[Mike H]

Hi,

I forgot to mention , format as [hh]:mm

Mike

 

[Mike H]

Slight modification in case the 2 times are outside of the working day.

=((NETWORKDAYS(M2,N2)-1)*("18:30"-"09:00")+MIN("18:30",MOD(N2,1))-MAX("09:00",MOD(M2,1)))

Mike

Hi,

I forgot to mention , format as [hh]:mm

Mike

Hi,

try this

=((NETWORKDAYS(M2,N2,Holidays)-1)*("18:30"-"09:00")+MOD(N2,1)-MOD(M2,1))

Holidays is a named range that can contain any dates of holidays you also
want to onit from the calculation or you can leave it out.

=((NETWORKDAYS(M2,N2)-1)*("18:30"-"09:00")+MOD(N2,1)-MOD(M2,1))

Mike