Hours and minutes difference between two times in Excel

Question

I have two columns of data. The first one is sunset on a particular day X and the next column is a time a few hours later but on day X+1 (as at 00:00 day X+1 comes in). How can I calculate the number of hours between sunset on day X and the time in the second column which is just a few hours later but on day X+1?

The data is in hh:mm:ss format as below:

Answer 1

Include the date in the cell with the time (cell must be formatted to time HH:MM:SS). You would write, for instance, [13/05/2020 20:47:00] in the cell, but only [20:47:00] would show, as you have it in time format.

In the cell you wish to have the time difference in, you find the difference by subtracting the last time by the first—for example see image I have typed [=B3-A3] in cell D3.

Or just do what Scott Cramer did :'D

Answer 2

Hi bz,

According to what you've asked, the fastest resolution is to use the formatting text formula as follow to calculate these hours. I have added an screenshot of the formula being tested in a google sheet just now.

Keep in mind that the formats for A1 and B1 should be time format, you can also add a custom format for the 24 hours version and it will work exactly the same. I certainly hope this helps. Have a good day, mate!

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