When working in Excel with dates and times, there might be an instance where we need to calculate the difference between two times. For example, we may want to calculate how many hours, minutes and seconds have passed since our last birthday. In order to figure this out, little tweaks are needed.
1) Convert Time Difference to Hours, Minutes and Seconds
You can calculate the time difference between two times by subtracting the end time minus the start time. If you get a number that looks completely out of place, what you need to do is to right click on that number and choose Format Cells.
Go to Number – Custom.
Choose the type [h]:mm:ss.
The exact number of hours, along with the minutes and seconds will be shown.
2) Convert Timespan to seconds
Converting the time difference to seconds might be the best solution. In doing so, we can perform calculations on data, without having to worry about the incompatibility between different units, like numbers and dates.
The principle here is similar. Simply repeat the same procedure as before, but in Custom Type: type in [ss].
Bonus tip: You can easily convert hours and minutes into seconds by using the text function and the format for seconds.
To do the opposite is a bit more complicated, but we will be sure to explain it some other time.