Substracting time from date & time to determine the date & time

Closed
Posts
2
Registration date
Monday July 1, 2013
Status
Member
Last seen
July 2, 2013
-
Posts
2849
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 27, 2022
-
I have a 'simple' spreadsheet with five columns (warehouse #; part #; departure date & time; transit time and arrival date & time).

I have packages of parts from different warehouses and they need to arrive at the same time at our only factory; therefore, I need to write a formula to subtract a transit time which is formatted 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 starting date and time which is formatted as dd.mm.yyyy hh:mm:ss (i.e. 01.07.2013 09:06).

A package from warehouse # 1 needs to arrive at factory # 1 on July 3, 2013 at 1:00 AM (i.e. 03.07.2013 01:00). It will take 39 hours and 54 minutes (i.e. 39:54:00) to move the package from warehouse # 1 to factory # 1. In order for the package to arrive at the scheduled time of July 3, 2013 at 1:00 AM, when should the package leave the warehouse # 1? In this example, the package should left the warehouse on July 1, 2013 at 9:06 AM.

I need to know how write the formula to subtract hh:mm:ss from dd.mm.yyyy hh:mm:ss to determine the time when the package should leave the warehouse.

Thanks in advance.

2 replies

Posts
2849
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 27, 2022
491
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
Posts
2849
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 27, 2022
491
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
Posts
2
Registration date
Monday July 1, 2013
Status
Member
Last seen
July 2, 2013

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.