Excel can calculate the time between two times, but it requires a bit of attention to detail. The data that we’ll be using today is a list of the first 8 Star Wars movies with a start and end time. If we wanted to calculate the total amount to watch these movies, we could use Excel to do this.
Subtracting Time
Similar to any other subtraction formula in Excel, we can simply do a formula to calculate the difference between the end and start time. If you just do this, Excel will display the time difference as an actual time. For Episode I, Excel tells us that this is 2:16 AM. We obviously don’t want it displayed like this, and it makes it difficult to add multiple lines.
If we click on the Comma formatting, it gives us a weird decimal. This is because Excel is calculating the formula as a decimal of a day. If we want Excel to show us an answer in hours, we can simply multiply our formula by 24. This shows us that the movie is 2.27 hours. This doesn’t show hours and minutes like we may like, but we’ll get to that below.
We can then pull this formula down to the other lines, and add a total line. This shows us that the total time to watch the first 8 movies would be 18.07 hours.
Displaying Hours and Minutes
While getting hours in decimals isn’t a big deal for a spreadsheet, it’s sometimes helpful to show information in hours and minutes. In order to do that, we need to get a bit creative with a formula.
The way that this calculation works, anything that’s to the left of the decimal point represents an hour. The numbers to the right of the decimal point represent a fraction of an hour. So, to get the number of minutes, we would need to multiply the decimal by 60.
With all that in mind, we can create a column for hours and a column for minutes. In the Hours column, we’ll simply do an INT function and reference the Total Time Column.
To get minutes, we have to get only the number that’s to the right of the decimal point. The easiest way to do this is to create a formula that takes the Total Time and subtracts the Integer function that we just used. This will then give us the fraction of an hour, so to get the minutes, we’ll have to multiply it by 60.
Watch the video above to get a few more options on time calculations.
Comments