Related:

- Substracting time from date & time to determine the date & time
- You've tried to log in too many times. to protect your account, a temporary block has been placed on logging in. please try agai ✓ - Forum - Facebook
- Whatsapp web time wrong - Guide
- Looks like you’ve tried to sign in unsuccessfully too many times. recover your id or reset your password to continue. care code: - Forum - Yahoo Mail
- You have guessed too many times ✓ - Forum - WhatsApp
- Gmail time is wrong ✓ - Forum - Gmail

## 2 replies

TrowaD

- Posts
- 2849
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 27, 2022

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
- 2849
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 27, 2022

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

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 "

I need to write a formula to subtract a transit time which is formatted as

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

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.