How to display difference in minute between 2 times in Excel 2016

Question

Is their any way to to calculate difference in Hours , minutes , seconds using excel function

• start time : '23-DEC-19 09.00.17.393922000 AM'
• end time : '23-DEC-19 09.00.26.453921000 AM'

I have tried this , but it does not show any result :

=INT(B2-A2) & " days, " & HOUR(B2-A2) & " hours, " & MINUTE(B2-A2) & " minutes and " & SECOND(B2-A2) & " seconds"

Expected like this output :

Output

It displays some like error : #VALUE

Answer 1

That is because 23-DEC-19 09.00.17.393922000 AM is not a format that Excel can parse automatically from text to an actual time.

You will need to change the text to a format it can parse. To do this you need to change the first two . to : You can use SUBSTITUTE to do that:

SUBSTITUTE(SUBSTITUTE(A2,".",":",2),".",":",1)

This will turn the string into:

23-DEC-19 09:00:17.393922000 AM

Which can be parsed.

So your formula will be:

=INT(SUBSTITUTE(SUBSTITUTE(B2,".",":",2),".",":",1)-SUBSTITUTE(SUBSTITUTE(A2,".",":",2),".",":",1)) & " days, "
& HOUR(SUBSTITUTE(SUBSTITUTE(B2,".",":",2),".",":",1)-SUBSTITUTE(SUBSTITUTE(A2,".",":",2),".",":",1)) & " hours, "
& MINUTE(SUBSTITUTE(SUBSTITUTE(B2,".",":",2),".",":",1)-SUBSTITUTE(SUBSTITUTE(A2,".",":",2),".",":",1)) & " minutes and "
& SECOND(SUBSTITUTE(SUBSTITUTE(B2,".",":",2),".",":",1)-SUBSTITUTE(SUBSTITUTE(A2,".",":",2),".",":",1)) & " seconds"