Number of Hours Between Two Dates Not Working in Excel

1
votes

As requested, I have included a simplified screenshot that illustrates the issue.

enter image description here

As you can see, I subtracted the two dates and formatted it as "h:mm:ss". Why doesn't this give you the total amount of hours that have passed between the two dates? Is there a better way to do this? There is a great answer below, but I am trying to figure out why doing the way illustrated in this screenshot doesn't work.

END OF EDIT

I am aware that similar questions have been answered here, but for whatever reason this is not working for me at all.

I have two columns: one is a date, one is the time.

enter image description here

As you can see from the currently highlighted cell, the "time" column is actually stored as date with the time included. In column H, I have the date stored as a serial code so that the decimal number refers to a month, day, year, hour, minute, and second. When I subtract the serial code that refers to 2/16/2016 3:20:01 PM from the serial code that refers to refers to 2/14/2016 1:20:01 PM and format that cell as "h:mm", I am getting 2:00. Why?????

I have been hacking away at this for a while and this is supposed to be stupid easy and it's not working. Any help is greatly appreciated so I can move on to more important things.

2
excel
There are more than 2 hours between the 14th of February and the 16th of February. Am I not asking this properly? – Ryan_C
It's okay. I have no idea what to do and this starting to really frustrate me. Why doesn't this work?? – Ryan_C
There are more than 2 hours between the 14th of February and the 16th of February. Am I not asking this properly? It's not really clear what you are asking about, there's a table with too many rows and columns on the screenshot. Create one with only 3 values - A, B, C, format it to show full values and ask why A-B<>C – BrakNicku
I think you are getting bogged down with Excel's formatting, which admittedly can mask what is really going on underneath the hood. – Tim Biegeleisen
Is it possible that your formatting is simply discarding the day-difference? – Geoffrey Wiseman

2 Answers

1
votes

It does work. Just take the difference between the two dates, format the cell containing the difference as Number, and then use one of the following formulas to get the units you want. I will assume that:

A1 has the value 2/16/2016 3:20:01 PM

B1 has the value 2/14/2016 1:20:01 PM

Into C1 enter A1 - B1, and format it as Number

If you want the difference as days, leave C1 as is.
If you want hours, use =24*(A1 - B1)
If you want minutes, use =24*60*(A1 - B1)
If you want seconds, use =24*60*60*(A1 - B1)

0
votes

THE ANSWER TO THIS IS REALLY SILLY.

Below, you will find that Tim Biegeleisen provided an excellent answer to this question. I just wanted to further elaborate.

Sometimes having too many option for formatting can be a problem, which is what happened here.

When you subtract 2/14/2016 1:20:01 PM from 2/16/2016 3:20:01 PM and format it as h:mm:ss it only subtracts the hours portion and ignores days. You just have to somehow know that if you are trying to do what I'm trying to do, you have to format it as [h]:mm:ss and then it will calculate the total number of hours passed. The bracket around the "h" is the important part here. I hope this helps the next person who comes across this.