Difference between 2 time stamps

[Raj]

Hi All

I require a formula to compute the difference between 2 date stamps. Here is
my requirement. We have time the issue is raised in K2 and target time to
complete is in O2
for example
K2 : 5/29/2009 4:15:21 PM
O2: 6/1/2009 4:15:21 PM

The difference between this 2 must be 8 hours as we work from Monday to
friday
timings 9.00 to 17.00 daily.

Can anyone help me regarding this.

Raj

 

[Jacob Skaria]

If O2 and K2 are in date/time formats..

=O2-K2

Use the above formula and format the cell to the below format. Right
click>Format Cells>Custom Format>Type:=

[h]:mm:ss

If this post helps click Yes

 

[Stefi]

=O2-K2-IF(AND(WEEKNUM(O2,2)>WEEKNUM(K2,2),WEEKDAY(O2,2)<WEEKDAY(K2,2)),
(WEEKNUM(O2,2)-WEEKNUM(K2,2))*(2+TIME(16,0,0))
+4*(WEEKNUM(O2,2)-WEEKNUM(K2,2)-1)*TIME(16,0,0),0)
Regards,
Stefi

„Raj†ezt írta:

 

[David Biddulph]

For me that gives 72 hours, not the 8 hours that Raj requested.
It doesn't take account of the 8 hour working day, nor does it allow for
weekends.
--
David Biddulph

If O2 and K2 are in date/time formats..

=O2-K2

Use the above formula and format the cell to the below format. Right
click>Format Cells>Custom Format>Type:=

[h]:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi All

I require a formula to compute the difference between 2 date stamps. Here
is
my requirement. We have time the issue is raised in K2 and target time to
complete is in O2
for example
K2 : 5/29/2009 4:15:21 PM
O2: 6/1/2009 4:15:21 PM

The difference between this 2 must be 8 hours as we work from Monday to
friday
timings 9.00 to 17.00 daily.

Can anyone help me regarding this.

Raj

 

[David Biddulph]

It might be worth trying
=(NETWORKDAYS(K2,O2)-1)*8+(MOD(O2,1)-MOD(K2,1))*24 if you want an answer in
decimal hours, or
=(NETWORKDAYS(K2,O2)-1)/3+MOD(O2,1)-MOD(K2,1) if you want an answer in
Excel time [h]:mm:ss

If you have start or end times which fall outside your defined working week
you'll have to define how to treat them, and modify the formula accordingly.
--
David Biddulph

For me that gives 72 hours, not the 8 hours that Raj requested.
It doesn't take account of the 8 hour working day, nor does it allow for
weekends.
--
David Biddulph

If O2 and K2 are in date/time formats..

=O2-K2

Use the above formula and format the cell to the below format. Right
click>Format Cells>Custom Format>Type:=

[h]:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi All

I require a formula to compute the difference between 2 date stamps.
Here is
my requirement. We have time the issue is raised in K2 and target time
to
complete is in O2
for example
K2 : 5/29/2009 4:15:21 PM
O2: 6/1/2009 4:15:21 PM

The difference between this 2 must be 8 hours as we work from Monday to
friday
timings 9.00 to 17.00 daily.

Can anyone help me regarding this.

Raj

 

[Jacob Skaria]

Oops..Sorry I missed the actual query/. Thanks David for pointing that out

With start date in A1 and end date in B1 you can try...

=((B1-WEEKDAY(B1,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,WEEKDAY(A1,2))+MIN(5,WEEKDAY(B1,2)))*("17:00"-"09:00")+MOD(B1,1)-MOD(A1,1)

If this post helps click Yes
---------------
Jacob Skaria

For me that gives 72 hours, not the 8 hours that Raj requested.
It doesn't take account of the 8 hour working day, nor does it allow for
weekends.
--
David Biddulph

If O2 and K2 are in date/time formats..

=O2-K2

Use the above formula and format the cell to the below format. Right
click>Format Cells>Custom Format>Type:=

[h]:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi All

I require a formula to compute the difference between 2 date stamps. Here
is
my requirement. We have time the issue is raised in K2 and target time to
complete is in O2
for example
K2 : 5/29/2009 4:15:21 PM
O2: 6/1/2009 4:15:21 PM

The difference between this 2 must be 8 hours as we work from Monday to
friday
timings 9.00 to 17.00 daily.

Can anyone help me regarding this.

Raj