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
Date and Time formula (Excel 2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Date and Time formula (Excel 2003 SP3)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 12612
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Date and Time formula (Excel 2003 SP3)
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
(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
StuartR
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Date and Time formula (Excel 2003 SP3)
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.
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.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 12612
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Date and Time formula (Excel 2003 SP3)
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
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
You do not have the required permissions to view the files attached to this post.
StuartR
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Date and Time formula (Excel 2003 SP3)
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
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
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 12612
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Date and Time formula (Excel 2003 SP3)
Steve.
I just added 3/24+ to each formula in your workbook and it seems correct to me. Here is the edited workbook Does it look different to you?
I just added 3/24+ to each formula in your workbook and it seems correct to me. Here is the edited workbook Does it look different to you?
You do not have the required permissions to view the files attached to this post.
StuartR
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Date and Time formula (Excel 2003 SP3)
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.
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.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin