Date & Time

[rendzina]

I am trying to calculate the difference between 2 cells. Both contain date & time (dd,mm,yy hh:mm). One contains a due date (a), and the other actual date of delivery (b).

I currently have it working for just the number of days difference, as long as 'b' is larger than 'a'. If, it is the other way around, it errors. + have not managed to include the time variance + then need to add in 'networkdays' so that weekends etc are excluded.

Any suggestions?

 

[2rrs]

Hi, this is covered on Chip Pearsons site.

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

I believe this covers what you are looking for.

HTH, 2rrs

 

[stickboy]

have you tried removing the time element out of the cell.

eg

36756.053421 would be 18th Aug 2000 01:16.

If you use =trunc("cell ref") this will give you 36756 (the date)
and if you have another cell that subtracts the "trunc cell" formula result from your original date and time cell, you get the time on its own.

Do this for both original dates and times.

Now you should find it easier to work out exactly how much time there is between the two dates and times.

 

[rendzina]

cheers!! sorted.

 

[2rrs]

Try this formula,

A2...2/5/03 3:19 PM

B2...2/25/03 1:25 PM

C2=(B2-A2)-((INT(B2)-INT(A2))-NETWORKDAYS(A2+1,B2))
C2...custom format..d, hh:mm

C2 results 13, 22:06....(13days, 22hrs:06mins)

2rrs

 

[rendzina]

Right thought was sorted, how wrong i was any ideas any1? am currently feeling like the blondest of the blondes!!!

Need this broken down, real basic like.

This sum only needs to be run when B is later than A, if B is less then answer needs to default to '0'. Probably best if answer is expressed in hours+ minutes. Also, No longer need it to just be network days, need entire time difference including weekends!

Does this make any sense?[/url]