Calculating time difference over midnight

[Rleow1112]

Hi There,
Happy New Year !
Need some help please...I am struggling to compute the total time (In HH:MM) taken in excel with 2 sets of date where one is past days or midnight.
Example one set of date/time is 12DEC23/23:00 and another set is 14Dec23/02:00.

May I know what's the best and easy formula I can use to calculate the total time taken for these 2 dates ?

Thank you so much in advance for your expertise advise.

Regards/Robert

[HansDouwe]

If the dates are formatted as dates, and 12DEC23/23:00 is in A1 and 14Dec23/02:00 is in A2, please try =A2-A1 and format the result cell: [hh]:mm
If the dates are formatted in another way, please upload a sample workbook.

[FlameRetired]

Hello Robert. Welcome to the forum.

While waiting for you to respond to HansDouwe's request and with the understanding your data is text .... again with values in A1 and A2 try this in B1

Please Login or Register  to view this content.

[Rleow1112]

Good morning, Dave & Hans,
Thank you for your prompt reply and attached is the sample workbook that was extracted from system that the date/time is a mess !
So I am trying to compute the total time taken from issued that was reported till it was resolved.

Attached sample workbook for your reference.

Appreciate your expertise advise.
Regards/Robert

[HansDouwe]

Please try in D2 and copy down:

Please Login or Register  to view this content.

Format column D: [hh]:mm:ss

[DJunqueira]

It seems that you have 4 negative results.

[Rleow1112]

Please try in D2 and copy down:

Please Login or Register  to view this content.

Format column D: [hh]:mm:ss

Hello Hans,
I had tried the copy/paste the formula in D2 column and also formatted the column D as "hh:mm".. The outcome is most of the result in column D are shown as "#VALUE!"

[Rleow1112]

It seems that you have 4 negative results.

These 4 results are expected due to human error where the "resolved time" is earlier than the "reported time". Please ignore this. Thank you !
Regards/Robert

[HansDouwe]

The outcome is most of the result in column D are shown as "#VALUE!"

Please upload a sample workbook with the error.

[Rleow1112]

Please upload a sample workbook with the error.

Here you go.. Thank you so much.
Regards/Robert

[Tommy90]

Hi,

Use TextToColumns for column B then column C and choose format of date as you want.
Select data in column B then Data --> TextToColumns --> Next --> Next and here choose your format date (dmy or mdy or....)
Idem for column C

Some data are data in > data out.

[HansDouwe]

The Value error is caused by local settings.
The dates are written in format dd/mm/yyyy, but your Excel expects format mm/dd/yyyy.

Please try this formula in D2 and copy it down.

Please Login or Register  to view this content.

This should work for you.

[popipipo]

These 4 results are expected due to human error where the "resolved time" is earlier than the "reported time". Please ignore this. Thank you !

If you correct these results then this should work

Please Login or Register  to view this content.

[josephteh]

Delete delete

[Rleow1112]

The Value error is caused by local settings.
The dates are written in format dd/mm/yyyy, but your Excel expects format mm/dd/yyyy.

Please try this formula in D2 and copy it down.

Please Login or Register  to view this content.

This should work for you.

Dear HansDouwe,
This formula worked like a charm !
One more question... How do I get the result in MINS for all data in column D ? I used =SUM(D2:D1576) with format as "Number", but get error "#VALUE!".
Appreciate your help on this matter ? Thank you so much !

Regards/Robert

[Rleow1112]

Thank you for that !
Regards/Robert

[HansDouwe]

How do I get the result in MINS for all data in column D

Please try and format cell as a number

Please Login or Register  to view this content.

or try and custom format cell as [m]

Please Login or Register  to view this content.

[Rleow1112]

Please try and format cell as a number

Please Login or Register  to view this content.

or try and custom format cell as [m]

Please Login or Register  to view this content.

Thank you so much, great appreciated!
Regards/Robert

[HansDouwe]

You are Welcome!

Thanks for the feedback. Glad to have helped. .

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[Rleow1112]

Thanks for sharing and indeed I am not aware of this feature. Nevertheless, I had did it this round ! Once again, Thank you so much for your help !

[HansDouwe]

Thanks for the rep . Glad to have helped.