Thursday, August 21, 2014

Excel formula to show difference of time in proper format


There are 2 columns in my excel which tells 2 times say EndTime and StartTime. I have to show the difference between these 2 columns in next column.


If the EndTime is in column B and StartTime is in column A and want to show the difference in column C

Just use the cell formula =B1-A1 in the C1 cell apply the same formula to all the cells by simply dragging.

In the normal scenario we can see the difference in h:mm:ss format. But actually speaking we are not telling column C that the format should be h:mm:ss. This leaves an option in future that the difference may be shown as decimal number. So how to include the same in cell formula.

Use =TEXT(B1-A1,"h:mm:ss")