Date and Time formula (Excel 2003 SP3)

[steveh]

Good morning

I need to wotk out some transit times in a very large WB.

I have the following set up

B42 = Pick up Date xx/xx/xx
C42 = Pick up Time xx:xx
J42 = Delivery Date xx/xx/xx
K42 = Delivery Time xx/xx/xx

and use this formula =J42-B42+(INT(K42)+MOD(K42,1)/0.6-INT(C42)-MOD(C42,1)/0.6)/24 (Thanks Slinky!!)

The problem is that I need to factor in a +3 hour time difference as the pick up date is in Doha and the delivery date is in the UK. No matter where I seem to change the formula to reflect this I get a string of ###### ?

Any ideas please

[StuartR]

What happens if you just add
(3/24) +
after the opening = sign
to give
=(3/24) + J42-B42+(INT(K42)+MOD(K42,1)/0.6-INT(C42)-MOD(C42,1)/0.6)/24

[steveh]

Hi Stuart

Thanks for the fast response.

If I do that I get a result of 2.1165 instead of the answer which I currently have which is 47:47. I had thought of using a hidden intermediate column which would subtract the time but that still errors.

[StuartR]

Is 47:47 hours and minutes?
What is the format for your result cell?
(I tried this and formatted the result as "d hh:mm" and it seemed to work fine)

Edited to add: Here is a sample with the result in M42

[steveh]

Hi Stuart

Thans for the example but when I format the cells in my workbook as d hh:mm I still get the 2.1165 answer. C and K in my WB are formatted as Customs Time hh:mm and the result cell as [h]:mm because some of the result will come in over 24 hours.

I have attached a small example as it may demonstrate better what I am trying to explain

[StuartR]

Steve.

I just added 3/24+ to each formula in your workbook and it seems correct to me.

Screenshot.png

Here is the edited workbook

Transit Times.xls

Does it look different to you?

[steveh]

Hi Stuart

Thanks very much all sorted.

When I copied your original formula in the post above and pasted it straight into my WB I did not notice the gaps in =(3/24) + J42 which I guess must have given me the erroneous result. deleting the spaces made it work.