How do you calculate hours between two times?

I am trying the method of B2-A2 (Updated date/time - Created date/time) and then formatting the results cell with d "days," h "hours," m "minutes and" s "seconds" and all seems well until I get to a certain value and then it is not displaying results correctly. I worked around this by using =INT(B2-A2) & " days, " & HOUR(B2-A2) & " hours, " & MINUTE(B2-A2) & " minutes and " & SECOND(B2-A2) & " seconds" but now I'm trying to average the results of the original B2-A2 values and am getting a value of 61.26262 which when formatting the cell with d "days," h "hours," m "minutes and" s "seconds" I should get 61 days... but am getting 1 days, 6 hours, 18 minutes and 10 seconds. How can I format the cell values to return the correct 61 days? Is it as simple as changing the formatted value of 1 days, 6 hours, 18 minutes and 10 seconds to 61 days, 6 hours, 18 minutes and 10 seconds?

Tony B says:
February 21, 2021 at 10:05 pm

Hi Svetlana, you are obviously very skilled at using Excel. Your page on Excel time calculations came up in a search. I am hoping you can assist me in resolving a couple of issues within an existing Excel sheet i have made for home video logs of old videotapes.

I have many home videos of my kids growing up and I am entering the date, location, person, place, etc. information of each video into an Excel sheet.

I tried to copy and paste a sample of the heading of my sheet but the website would not allow me to do so. Let me know if there is a way to send you the sheet. If not here are the column headings:
DVD # Tape# Source Type Date of Event Time Start Time End Length (HR:MN:SC) Subject Names Event Name Location Notes

If you already have a sheet for my needs that would be great!

I would like your expertise on two matters. The first involves time calculations.

I found where I can format cells for Time/37:30:55 to help me calculate the time durations by entering the data in the Time Start and Time End columns to determine the duration in the Length column (this is calculated across a row. I also entered a formula to calculate the total duration in the Length column (going down) to determine the total length of the entire video tape or the total of all of the events.

Since the rows could be multiple because of the number of persons and descriptions of one event, I have to enter these formulas manually. Is there a way to put a formula into the Length (duration) column so when I enter the date in Time Start and Time End it will automatically subtract the Start from the End. For instance, one Length could be cell G3 and G5 and may skip to G9. Of course the end point to total the event durations in the Length column for the entire videotape would vary also since I do not know what row will be the last one.

The second request is hopefully more simple. I want to make sure that I am able to filter the logs if want to find all of the video clips in one video tape say of John or Disneyland or birthday. For instance in a cell naming characters within one event or row as I described above should I put a space or comma or colon between each word I want to search later? Before I start all of this data entry I want to make sure i have it formatted correctly. This will be important if I combine all of the individual video logs into one massive excel sheet to possible search for all of John's birthdays through the years to do a highlight movie.

Sorry for the lengthy request but I am a novice to Excel and making requests on forums.

Thank you in advance for your assistance and I look forward to your response.

Tony

Vignesh says:
February 8, 2021 at 2:20 pm

Hi,

I have a problem working with the excel formula to calculate the End Date & Time from Start Date & Time for given Duration.

Its a 24/7 work but there is a chance for festival and natural disaster holidays so will maintain the holiday list separately. We have 2 shifts a day. each shift has below details

--------------------------------------------------------------------------------------------------------------------------------------------------------

The complexity of calculating the number of hours between two times stems from times that cross midnight. This is because times that cross midnight often have a start time that is later than the end time (i.e. start at 9:00 PM, end at 6:00 AM). This article provides several formula solutions, depending on the situation. 

Simple duration calculation

When start time and end time occur in the same day, calculating duration in hours is straightforward. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula:

=end-start
=5:00PM-8:00AM
=0.375-0.708=.333 // 8 hours

To see the result in hours and minutes, apply a time number format like this:

h:mm

However, when times cross a day boundary (midnight), things can get tricky.

How Excel tracks time

In Excel, one day equals 1, which represents 24 hours. This means times and hours are fractional values of 1, as shown in the table below:

When times cross midnight

Calculating elapsed time is more tricky if the times cross a day boundary (midnight). For example, if the start time is 10:00 PM one day, and the end time is 5:00 AM the next day, the end time is actually less than the start time and the formula above will return a negative value, and Excel will display a string of hash characters (########).

To correct this problem, you can use this formula for times that cross a day boundary:

=1-start+end

By subtracting the start time from 1, you get the amount of time in the first day, which you can simply add to the amount of time in the 2nd day, which is the same as the end time. This formula won't work for times that occur the same day, so we need to use IF function like this:

=IF(end>start, end-start, 1-start+end)

When both times are in the same day, end is greater than start time, the simple formula is used. But when the times across a day boundary the second formula is used.

MOD function alternative

The MOD function provides an elegant way to simply the formula above.  By using the MOD function with a divisor of 1, we can create a formula to handle both situations:

=MOD(end-start,1)

This formula takes care of the negative time by using the MOD function to "flip" negative values to the required positive value. Because this formula will handle times in the same day and times that span midnight, we don't need a conditional IF statement.

Note: neither formula above will handle durations greater than 24 hours. If you need this, see the date + time option below. 

For more on modulo, here's a good link on Khan Academy.

Simplifying with date + time

You can simply the problem of calculating elapsed time drastically using values that contain both date and time, sometimes called "datetimes". To enter a date and time together, use a single space between time and date like this: 9/1/2016 10:00. If you format this date with General format, you'll see a value like this:

42614.4166666667 // date + time

The numbers to the right of the decimal represent the time component of the date. Once you have datetime values, you can use a basic formula to calculate elapsed time. In the screen below, start and end values contain both dates and times, and the formula is simply:

=C5-B5 // end-start

The result is formatted with the custom number format:

[h]:mm

to display elapsed hours. This formula will correctly calculate the hours between two times in a single day, or over multiple days.

Formatting time durations

By default, Excel may display time, even time that represents a duration, using AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM. To remove the AM/PM, apply a custom number format like:

h:mm

In cases where calculated time may exceed 24 hours, you should use a custom format like [h]:mm. The square bracket syntax [h] tells Excel to display hour durations of greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

How do I calculate hours from two times in Excel?

How do you calculate time interval?

What is the formula to calculate hours?

How do you calculate the number of minutes between two times?