Date & Time addition formula

[vani2004]

COLUMN A,B, & C include date and time related to "time when part was shipped"

Column A: Date entered as 07/15/2017
Column B: Time Entered as 60700
Column C: is converting time with this formula =TEXT(J18,"00\:00\:00")+0 (giving me result as expected 6:07:00)

COLUMN D, E & F related to when part was received/delivered

Column d: Date entered as 07/16/2017
Column e: Time Entered as 61000
Column F: is converting time with this formula =TEXT(J18,"00\:00\:00")+0 (giving me result as expected 6:10:00)

Now in COLUMN G: I need a formula that give me total time it took to deliver. For example 1 day and 3 minutes or 24 hours and 3 minutes. It don't matter which time format. hh:mm:SS or day:HR:MM etc

[Olly]

Try:

Please Login or Register  to view this content.

Apply numberformat as required - e.g.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[vani2004]

I tried that and it's not working. It says date and times that are negative or too large that displays as #######. It tried different time formats on the cell but it don't work. Some format does display time but it's not displaying correctly. For example If i leave time same 6 am for both days (6:00:00) but change date I day apart it display 12:00:00 instead of 24 hours (24:00:00)

[JeteMc]

I tested Olly's formula and got 24:03:00 as a result. I formatted cell G1 as [h]:mm:ss
I believe that all of this works in the 2003 version as I have saved the file as an .xls (97 - 2003)and did not get a warning.
I did change the time conversion formulas from =TEXT(J18... to =TEXT(B1... and =TEXT(E1...
Let us know if you have any questions.

[vani2004]

Thank you JeteMac & Olly! You guys are awesome....

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.