Substracting time from date & time to determine the date & time
Closed
AzRdWarrior
Posts
2
Registration date
Monday July 1, 2013
Status
Member
Last seen
July 2, 2013
-
Jul 1, 2013 at 04:52 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 4, 2013 at 10:28 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 4, 2013 at 10:28 AM
Related:
- Substracting time from date & time to determine the date & time
- Popcorn time download - Download - Movies, series and TV
- Ocarina of time rom - Download - Action and adventure
- Whatsapp time setting - Guide
- Summer time saga - Download - Adult games
- Time of day clock stopped - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jul 4, 2013 at 10:28 AM
Jul 4, 2013 at 10:28 AM
Hi AzRdWarrior,
As I thought, you are not having issues with formula but with format.
Take a look at the following file and you will see 3 different formats for column D.
You will notice that the formula and the result in column C is the same in all 3 cases.
Here is the file:
http://www.ge.tt/7FywUxk/v/0
Best regards,
Trowa
As I thought, you are not having issues with formula but with format.
Take a look at the following file and you will see 3 different formats for column D.
You will notice that the formula and the result in column C is the same in all 3 cases.
Here is the file:
http://www.ge.tt/7FywUxk/v/0
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jul 2, 2013 at 11:09 AM
Jul 2, 2013 at 11:09 AM
Hi AzRdWarrior,
The formula is simple: =E2-D2.
Are you having formatting issues?
Make sure when entering a value, the value shifts to the right. This means Excel understands the format and can calculate with it.
Best regards,
Trowa
The formula is simple: =E2-D2.
Are you having formatting issues?
Make sure when entering a value, the value shifts to the right. This means Excel understands the format and can calculate with it.
Best regards,
Trowa
AzRdWarrior
Posts
2
Registration date
Monday July 1, 2013
Status
Member
Last seen
July 2, 2013
Jul 2, 2013 at 04:08 PM
Jul 2, 2013 at 04:08 PM
Thank you for your response but you didn't answered my question.
If all transit times were under 8 hours or 24 hours (which most are not) then it is easy as you suggested. But most if not all of the transit times are more than 24 hours.
You can't subtract a cell with a value of "hh:mm:ss" from a cell with a value of "dd.mm.yyyy hh:mm:ss" to determine the starting date and time unless you write a formula.
I need to write a formula to subtract a transit time which is formatted as hh:mm:ss (i.e. 39:54:00) from the arrival date and time which is formatted as dd.mm.yyyy hh:mm:ss (i.e. 03.07.2013 01:00) to determine the DATE and TIME when the package should leave the warehouse.
Column A is the Warehouse and is formatted as text
Column B is the Part # and is formatted as a number
Column C is Departure Time (which I don't have and have to calculate) and is formatted as "dd.mm.yyyy hh:mm:ss"
Column D is the Transit Time (which is provided to me) and is formatted as hh:mm:ss (i.e. 39:54:00)
Column E is the Arrival Date & Time (which is provided to me) and is formatted as dd.mm.yyyy hh:mm:ss (i.e. 03.07.2013 01:00)
The formula should be =E3 - D3 (if D@ is more than 23:59:59....)
Example: Yesterday, I received an order that a part needs to be delivered at the factory at 1:00 AM on July 3, 2013. The transit time is 39 hours and 54 minutes (39:54:00). In a just in time environment, when should the part leaves the warehouse so that it will arrive at 1:00 AM on July 3, 2013. The answer for this example was 01.07.2013 09:06 (or it had to leave the warehouse at 9:06 AM on July 1, 2013.
D3 is 39:54:00 (transit time) and E3 is 03.07.2013 01:00 (the Arrival Date and Time). As I stated above, it will be easy if the transit time was less than 8 hours because you could drop the dd.mm.yyyy portion from dd.mm.yyyy hh:mm:ss. Since the transit times are over 24 hours, I need to be able to count days in the formula.
If all transit times were under 8 hours or 24 hours (which most are not) then it is easy as you suggested. But most if not all of the transit times are more than 24 hours.
You can't subtract a cell with a value of "hh:mm:ss" from a cell with a value of "dd.mm.yyyy hh:mm:ss" to determine the starting date and time unless you write a formula.
I need to write a formula to subtract a transit time which is formatted as hh:mm:ss (i.e. 39:54:00) from the arrival date and time which is formatted as dd.mm.yyyy hh:mm:ss (i.e. 03.07.2013 01:00) to determine the DATE and TIME when the package should leave the warehouse.
Column A is the Warehouse and is formatted as text
Column B is the Part # and is formatted as a number
Column C is Departure Time (which I don't have and have to calculate) and is formatted as "dd.mm.yyyy hh:mm:ss"
Column D is the Transit Time (which is provided to me) and is formatted as hh:mm:ss (i.e. 39:54:00)
Column E is the Arrival Date & Time (which is provided to me) and is formatted as dd.mm.yyyy hh:mm:ss (i.e. 03.07.2013 01:00)
The formula should be =E3 - D3 (if D@ is more than 23:59:59....)
Example: Yesterday, I received an order that a part needs to be delivered at the factory at 1:00 AM on July 3, 2013. The transit time is 39 hours and 54 minutes (39:54:00). In a just in time environment, when should the part leaves the warehouse so that it will arrive at 1:00 AM on July 3, 2013. The answer for this example was 01.07.2013 09:06 (or it had to leave the warehouse at 9:06 AM on July 1, 2013.
D3 is 39:54:00 (transit time) and E3 is 03.07.2013 01:00 (the Arrival Date and Time). As I stated above, it will be easy if the transit time was less than 8 hours because you could drop the dd.mm.yyyy portion from dd.mm.yyyy hh:mm:ss. Since the transit times are over 24 hours, I need to be able to count days in the formula.
