Comments on: How to calculate time in Excel - time difference, adding / subtracting times

The tutorial explains different ways to calculate times in Excel and demonstrates several methods of adding times and calculating time difference. You will learn a few useful formulas to sum times and add hours, minutes or seconds to a given time. Continue reading →

Comments page 18. Total comments: 1023

  1. Caption under figure in Formula 4 should say "less than" it seems.

    1. Hi Dave,

      Absolutely so. Fixed, thank you!

  2. Is there a way to calculate time with a format where you just use 7-4. Meaning that the 7 would in the AM and the 4 bing in the PM

  3. Outstanding explanations!! This (=IF(INT(B2-A2)>0…) was exactly what I needed. That you for the excellent explanations and demos.

  4. Hi All,

    I'd really appreciate some help with this. I'm trying to calculate a time cost per spend for a restaurant split between Pre 17:00 & post 17:00.

    I have in column A start time and column B finishing time.

    So it could be: 09:00 18:59 or 19:03 01:15 or 09:30 16:11

    How can I calculate the hours pre 17:00 without it adding straight up to 17:00 and calculate the the ones post 17:00 without it adding in extra time or going negative due to going past the midnight threshold.

    I'd really appreciate an answer because it is racking my brains something rotten.

    Kind regards

    Nick

  5. I have successfully used formula:

    =IF(INT(W47-V47)>0,INT(W47-V47)&" days, ","")&IF(HOUR(W47-V47)>0,HOUR(W47-V47)&" hours, ","")&IF(MINUTE(W47-V47)>0,MINUTE(W47-V47)&" minutes")

    but am getting FALSE for some Minutes and #NUM when time is set to same day.
    Any ideas?

    How fields Look

    Previous Current Time between Events
    End Event
    Time Start

    3/13/18 16:48 4/20/18 05:31 37 days, 12 hours, 43 minutes
    4/20/18 05:31 4/24/18 18:43 4 days, 13 hours, 12 minutes
    4/24/18 18:43 6/16/18 00:00 52 days, 5 hours, 17 minutes
    6/16/18 00:00 6/21/18 00:43 5 days, 43 minutes
    6/21/18 00:43 7/02/18 06:43 11 days, 6 hours, FALSE
    7/02/18 06:43 7/10/18 06:14 7 days, 23 hours, 31 minutes
    7/10/18 06:14 7/10/18 19:55 13 hours, 41 minutes
    7/10/18 19:55 7/31/18 06:00 20 days, 10 hours, 5 minutes
    7/31/18 06:00 8/03/18 08:47 3 days, 2 hours, 47 minutes
    8/03/18 08:47 8/08/18 03:36 4 days, 18 hours, 49 minutes
    8/08/18 03:36 8/10/18 10:48 2 days, 7 hours, 12 minutes
    8/10/18 10:48 8/14/18 04:48 3 days, 18 hours, FALSE
    8/14/18 04:48 8/14/18 01:12 #NUM!
    8/14/18 01:12 8/17/18 17:31 3 days, 16 hours, 19 minutes
    3/13/18 16:48 4/20/18 05:31 37 days, 12 hours, 43 minutes
    4/20/18 05:31 4/24/18 18:43 4 days, 13 hours, 12 minutes
    4/24/18 18:43 6/16/18 00:00 52 days, 5 hours, 17 minutes
    6/16/18 00:00 6/21/18 00:43 5 days, 43 minutes
    6/21/18 00:43 7/02/18 06:43 11 days, 6 hours, FALSE
    7/02/18 06:43 7/10/18 06:14 7 days, 23 hours, 31 minutes
    7/10/18 06:14 7/10/18 19:55 13 hours, 41 minutes
    7/10/18 19:55 7/31/18 06:00 20 days, 10 hours, 5 minutes
    7/31/18 06:00 8/03/18 08:47 3 days, 2 hours, 47 minutes
    8/03/18 08:47 8/08/18 03:36 4 days, 18 hours, 49 minutes
    8/08/18 03:36 8/10/18 10:48 2 days, 7 hours, 12 minutes
    8/10/18 10:48 8/14/18 04:48 3 days, 18 hours, FALSE
    8/14/18 04:48 8/14/18 01:12 #NUM!
    8/14/18 01:12 8/17/18 17:31 3 days, 16 hours, 19 minutes
    8/17/18 17:31 8/30/18 09:11 12 days, 15 hours, 40 minutes

    V column is calculated as =TEXT(A60,"m/dd/yy ")&TEXT(R60,"HH:MM")
    W column is calculated as =TEXT(A61,"m/dd/yy ")&TEXT(R61,"HH:MM")

    where
    A60 is a manually entered field say 8/30/2018
    and
    A61 is a manually entered field say 8/17/2018
    and
    R60 and R61 is a manual entered field for outage time Say 5.5 representing 5 hours and 30 minutes.

    Thanks

  6. Cell A1 is having 20-09-2018 18:14:10
    Cell B1 is having 20-09-2018 20:00:30
    Objective is to find the time difference and used B1-A1.
    But error is coming like this #VALUE!
    Appreciate if you can suggest solution to get the time difference.

    1. Siva:
      I believe this is a formatting issue. Your Excel doesn't recognize 20-09-2018 as a date.
      So, then the idea is to get the date formatted in a way your Excel sees as a date. The way it stands, Excel sees it as text.
      Try changing the two dates to 9/20/2018, leave the time as is and see if you can then simply subtract the cells using B1-A1. If that works then you need to work on the formatting issue.
      Try changing the formatting of the cells to the date using the location option. In the Date there's the option to specify a locale or location. Find yours and change it there.
      You can try replacing the "-" with "/" and see if that works.
      If these approaches don't work, then you can split the dates and times into two columns, then split the dates into three columns and concatenate them into one cell using DATEVALUE, format the cell as Date subtract the cells and Bob's your uncle. This sounds harder than it is, in fact I used this last approach many times with data sets containing thousands of rows of data.
      Let me know how it goes.

      1. Cell A1 is having 20-09-2018 18:14:10
        Cell B1 is having 20-09-2018 20:00:30
        Objective is to find the time difference and used B1-A1
        As per your suggestion, I changed the date part to 09/20/2018 and applied B1-A1, it worked. But it is difficult to change it manually in all cells. Tried to format the cell using the location option. It didn't work if I replace the "-" with "/". Please suggest workable solution. My file is having thousands of rows of data with this format.

  7. I have a start_date and end_date, wants to calculate the number of hours & minutes between the date excluding weekends as well as time from 12:00 AM to 8:00 AM.

    Start_date End_Date Hour spend (excluding weekends & time between 12AM to 8AM)
    6/5/2018 6:54:15 PM 6/10/2018 11:28:46 PM

    Your prompt response is highly appreciated.

    Thanks & Regards
    Chander

  8. Hello !
    I´m struggeling with making a calculation between two times that consists of minute, seconds, hundredths
    ex 01:54,36

    The calculation should be
    00:47,26 - 00:46,58 = DIFF
    or
    01:14,29 -01:16,32 = DIFF
    or
    01:13,12 - 00:56,24 = DIFF

    The examples shows that the first time could be either faster och slower then the second one.

    Any ideas ?

    Thank you.

  9. 1935 to 0135 how many hours

  10. time in time out total hrs
    6:29:41 AM 5:28:07 PM ?
    6:44:17 AM 4:57:45 PM ?

    this is the case
    - our time in should be 7am and hrs should be counted from 7am to 11:30 for the morning time and from 1:30 pm onwards whatever the time is.
    - i want to know the formula for this.
    - lunch break should be less from 11:30am to 1:00pm same with 6:00pm to 7:00pm

  11. Anurika:
    I think this is what you're looking for.
    Where the time data is in E1 enter this in an empty cell:
    =IF(E1<=TIME(9,0,0),E1,"0:00")
    Now, you can format the cells as Time and you can work with the data as time.

  12. Hi,
    Please help me.
    ColumnA ColumnB
    4:00 4:00
    5:00 5:00
    11:00 00:00
    10:00 00:00
    3:00 3:00
    This is my question.
    I need to get the hours which are below 9:00 to columnB from ColumnA as i showed above.Please give a formula.

    Thank you.

  13. we will calculate the time as indian time how to calculate
    Start time 07:00 AM
    End time 02:00 AM

  14. I have a question. I work in payroll at my work and we have 2 shifts. Is there an easy way to calculate differential time. What I mean is like this

    We have shifts that start at 14:20 and the differential kicks in at 18:00 and they work to 22:50. Is there a way for excel to calculate the amount of differential time that they worked?

    It would be nice if there was a way to do total time and differential time but since the guys cant make up their mind when they want to go to lunch it makes flat calculations hard. It is always nice to have the computer back up your numbers too.

    1. Adam:
      Do the employees clock in and out during their shifts?
      Are these times recorded in Excel?
      What's the differential pay?

  15. Hai good day
    Dear plz tell me how Sum in excel sheet day,hour,and minutes
    Exampla: "4 days 3 hours 45 minute and 16 second"

    1. Riaz:
      What other times do you want to sum?

  16. Hi,

    I need a solution for excel sheet I am maintaining to track activities on issues I get from client.

    Working hours are 09.00 am to 05.00 pm and every issue has 4 hours of time to get it acknowledged for the first time. For every issue creating after 05.00 pm (today) till 09.00 am(next day) must calculate acknowledge time from 09.00 am.

    How can we do this?

    Thank you,
    Ankita

  17. Hi,
    I am calculating retirement remaining years, while i used "Yearfrac" formula, all went good. but still some is not functioning, ex. one person already reached to retire in the previous year and formula is not showing -1 year, its showing remaining 1. please someone help me to get out from this problem.

    Thank you,
    Safiur Rahman

  18. How can I get an AVERAGE of start times, Cleaning crew starts cleaning equipment at different times due to production:
    Mon cleaning crew started at 12:50AM (entered as 00:50)
    Tue cleaning crew started at 12:15AM (entered as 00:15)
    Wed cleaning crew started at 12:10AM (entered as 00:10)
    Thu cleaning crew started at 23:45PM (entered as 23:45)
    Fri cleaning crew started at 23:50AM (entered as 23:50)
    I need an AVAERAGE start time for the week.
    Using AVERAGE(D4:D8) gives me 09:46AM. I'm looking for a time closer to 00:16. I appreciate your help, thank you in advance.

    Greg

    1. Greg:
      Try entering the time as 12:50, 12:15, etc. and then AVERAGE. When I do this the return is 12:10.

  19. If I
    have

    Specific hour (3:30am) and I want to use in a formula to calculate how many employee punched after 3:30 am
    I will appreciate your help.

    Thanks for your time and help

  20. I just want to add durations (D5 through D308)...I need a total in hrs, mins, and seconds. DOes anyone know a formula for this?

    1. Joe:
      If the data in D5:D308 is formatted as Custom [h}:mm:ss
      you should be able to sum them with SUM(D5:D308) or whatever combination of cells you need to sum.
      Right click, Format Cells, Custom and choose the [h]:mm:ss option.

      1. Thanks Doug, I've been doing exactly as you prescribed and I'm only getting 0:00:00. I'll gladly try anything else you might have. Thanks

  21. My data has a start time and end time for a behaviour
    i.e.

    09:20:45 09:21:09 So this behaviour lasted for 24 seconds
    09:21:09 09:21:30 So this behaviour lasted for 21 seconds

    I have 20,000 data points. I am looking for a formula to look for all behaviours that occur only on the minute. So in the example above, only the first data point would be marked true as the second point does not occur on the minute. In this example, the first data point would be the data point for 09:21:00 and the second data point would be deleted.

    I hope that makes sense!

  22. Desperately need formula for the following:

    If < 6.05, then subtract .50

    Thanks

    1. Darren:
      Where the data is in cell A1 the formula is:
      =IF(A1<6.05,(A1-0.5),"Number is Bigger than 6.05")
      If your data is in another cell, enter that cell address in place of A1.
      After you've entered the formula in the appropriate cell, you can copy it down the column to calculate more cells.

  23. 2-May-18 4:35 1-May-18 14:19 HOW CAN I CALCULATE DIFF IN HOUR

    1. Manoj:
      The formula is:
      =((End Date + End Time)-(Start Date + Start Time))*24

  24. i want to know how to calculate time when u have a negative sign. see example below:
    if
    start time end time Hours worked
    7:45am 3:15pm ???

    so what is the way forward?

    1. Hello, Andrew:
      There are three ways negative time can be displayed in Excel. The first two ways are explained in the article above this post, so no need to rehash them here. I would recommend you use one of them.
      The third way is to custom format the cell holding the negative time as -h:mm. This method works only if you always want a negative time value displayed. It also requires that you always subtract the earlier time from the later time. This means times returned really will be positive and will only appear negative. Probably not the best method.

  25. I am hosting a running event where people guess how fast they can run a mile, then they run the mile, and I have to be able to calculate the difference between their guess and what they actually ran, but I can't figure out how to format the cells because everything is in time as it relates to the day. I type in 5.22 to represent 5 minutes and 22 seconds but no matter what I put as the format it changes it to 5:22 am or pm and throws off the calculation. Can someone please help???

    1. Tiffany:
      If you change the format of the cells that will contain the times to h:mm the cells and the subsequent calculations will work the way you want.
      Select the cells, then right click and choose Format Cells, then choose Custom where you will find the h:mm option in the list. I know it means "hours:minutes" but maybe for your purposes it will work.

  26. Hi there,

    I am trying to calculate the time difference of the following function on my excel spreadsheet;

    =TEXT(E4-D4,"HH:MM:SS") and i press enter but it gives me #VALUE
    what does that mean and if you can help solve this...it took me an hour to fix this

    thanks
    bobby
    Bobby
    Bobby

    1. What do you have in E4 and D4? Are the values in there formatted as time?

  27. Hi there,

    I am trying to calculate the time difference of the following function on my excel spreadsheet;

    =TEXT(E4-D4,"HH:MM:SS") and i press enter but it gives me #VALUE
    what does that mean and if you can help solve this...it took me an hour to fix this

    thanks
    Bobby

  28. Hello, please help me with the formula for below:

    Example 1:
    If email Time received Date & Time =25/5/2018 18:00 Hrs and email processed Date and Time is 26/5/2018 14:15 Hrs

    Example 2:
    If email Time received Date & Time =25/5/2018 22:01 Hrs and email processed Date and Time is 26/5/2018 14:15 Hrs

    The time should calculate total hours taken only between my shift is 13:00 to 22:00 Hrs. If email received is after 22:00 hrs the total time taken to process should calculate from next day 13:00Hrs

    1. Hi Doug, any thoughts here?

      1. Ramana:
        After much searching I found a solution that works for your situation and several others, too.
        The solution is waaaay too complicated to type here, so I'll provide the link.
        You'll want to download the sample workbook that is at the bottom of the article as it is really nice and contains all the examples and formulas on several sheets.
        To download the workbook click the enlarge icon at the bottom right of the embedded worksheet. When you can see the big sheet, click on the download button at the top of the sheet.
        The link to the site is: https://www.exceltactics.com/calculate-net-work-hours-using-networkdays/

  29. Suppose opening quantity of JW Black Lable whisky (750 ml)1.300ml ie,1 bottle & 300 ml.received from godown is 1(750ml) bottle, so total is 2.3 ie,2 bottle & 300 ml.And sale is .6 ml ie,600 ml.what formula to be used to get the closing stock in one cell.

  30. Hello! Is there a predefined formula for calculating time it takes to complete a project if the data are in seconds or min/unit?

    Example: 1 unit = 15sec, 5 units must be completed

    Therefore, 5 units = 1 min:15 sec
    10 units = 2 min:15 sec

    Trying to estimate packaging times for product units. Thanks!

  31. Ash:
    Not sure of your question, but try this and see if it works for you:
    Enter this in say B2 =IF(A10<1,0,A10)
    Where A10 is the cell that holds the data.
    You can format B2 to show the number of decimal places to fit your needs.

  32. please help me...
    i need formula that if the excess hour is less than 1hr it will answer to 0:00 and if its 1hr to up it will show the excess hour starting only in 1hr to up.
    is it possible??/
    thank you

  33. sign in time sign out time Late Attendence Work Hours
    10:12:07 17:21:11 0:12:07 7:09:04

    Start Time 10:00:00 AM
    End Time = 17:00:00 PM

  34. That's amazing formula which is this

    =IF(INT(B2-A2)>0, INT(B2-A2) & " days, ","") & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & " hours, ","") & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & " minutes and ","") & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & " seconds","")

    But i have problem if the second is 0 the result is like this
    "33 minutes and"
    How to get rid " and" in this case?

  35. I'm using the following formula to calculate the number of working hours.
    The result is a decimal number which can be later multiplied by the hourly rate if you need to.
    For ex.
    8h 15min = 8.25hrs

    You can also enter the time over the midnight.
    Ex.
    Start 20:00 Finish 04:00 = 8.00h

    =IF(E10=0,0,IF(E10>D10,(E10-D10)*24,(24-D10*24+E10*24)))

    D10 - Start time
    E10 - Finish time
    Time format 12:00:00

    1. Hi Yuriy Roshchupkin,
      I tried your formula with the exact data that you have and it gave me a zero as the answer. The Time format is the same, is there something that I am missing?

    2. Hi Yuriy Roshchupkin,

      can you give me the formula for to change 8h 15min to 8.25hrs.

      Thanks
      Aditya

      1. thanks a lot.
        this one im looking for 1hour

  36. please tell me how to do this calculation in excel.

    calculation should done by right side to left side

    (signs-degrees-minutes-seconds)
    (12-30-60-60)

    Example for Addition:-

    09-20-13-40
    00-13-10-35
    ------------
    10-03-24-15
    ------------

    Example for Subtraction:-(in the below case we cannot delete 8 from 7. because we get 1 minute from minutes (i.e. 60 seconds) and from 67 seconds subtract 08 seconds)

    03-10-05-07
    00-00-04-08
    -----------
    03-10-00-59
    -----------

    i need code for this calcultion.

    Thanks
    Sankar.

  37. How I protect the cell from adding or deleting at a certain time. for example:
    I do not add or delete on this cell from 1:00 PM to 2:00 PM at 04/20/2018.
    Is there a formula in Excel? Please

  38. Hi
    Good Day
    Please help me in excel sheet

    Start Time Finish Time Mints

    07:00am 17:00pm ?

    Note: we don't want create any new calm & line between Start Time and Finish Time.

    1. use this formula to calculate diffidence between both of time .mod(finish time-start time,1)

    2. Change 17:00pm to 05:00pm and it should work. 1700 is 24 hour format while 05:00pm is 12 hour format. You cannot calculate using two different time formats.

  39. Please data to data calculate send mi

  40. If a car travel distance 190km @ the speed of 120km/h. it took 1 hour and 35 mint to cover 190km distance. i want result out put as "1 hour 35 min" on excel please help me

  41. In time (Column BF14)15/02/2022 2:27 PM and out time (Column BF15)16/02/2022 2:52 PM, how to find the no of hours, minutes between these two dated

  42. Hi
    I am Raju, looking for solution to get total spent market visit time by an employee. for example, below are the rows for daily spent time, and 3rd column is the daily spent duration, bu how can I get the total cumulative of a week or month auto sum in 3rd columns down. the formula I used between time in and time out is as =TEXT([@[TIME OUT]]-[@[TIME IN]],"h:mm") and need to get total time spend from 3rd column
    TIME IN TIME OUT Total Time Spent
    09:00:00 09:30:00 0:30
    09:35:00 10:00:00 0:25
    10:10:00 10:30:00 0:20

  43. how do you add/subtract time with milliseconds? I don't see a formula for this.

    IE: m/d/yyyy h.mm.ss.000

  44. i'm trying to replicate the example you have in Formula1 but i only get #VALUE!
    i am not sure why this happens and i am using the exact same values as you.

  45. I'm trying to subtract 500:00 from 15000:00 and it gives me #VALUE!.
    PLEASE ANY HELP.

  46. How do I calculate hours above 10000:00 ?

  47. Is there a way to calculate the difference in time between these two values?

    Dec 30 2017 4:09PM and Jan 3 2018 9:18AM

    Or is there a way to reformat these cells so I can? The date and time are in one single cell.

    Thank you,
    Jenna

  48. Thank u Mr. rajesh peshiya for the reply.
    But i need to below format
    4:30 - 4:00 = use formula =text(a1-b1,"h:mm") result 0:30 ITS OK
    4:40 - 4:40 = use formula =text(a2-b2,"h:mm") result #VALUE! NEED TO RESULT 0:00
    4:55 - 5:45 = use formula =text(a3-b3,"h:mm") result #VALUE! NEED TO RESULT -0:50
    4:55 - 5:30 = use formula =text(a4-b4,"h:mm") result #VALUE! NEED TO RESULT -0:35
    4:30 - 5:00 = use formula =text(a5-b5,"h:mm") result #VALUE! NEED TO RESULT -0:30

    Pls Help me..

    Regards
    Bandara

  49. How to calculate
    A B C
    1 5:20AM-2.20AM= =TEXT(A1-B1,"H:MM") RESULTS 3:00

    BUT

    2 2:20AM-5:20AM= =TEXT(A1-B1,"H:MM") RESULTS #VALUE! (-3:00)

    5 - 2 = 3
    3 - 5 = -2 Why didn't work (-) Time

    pls Help me

  50. Dear Friends i am in problem solving the issue.
    i want to make attendance record. requirement is
    Fixed Time = 8:30 Am
    if employ come to office up to 8:40 its ok, after 8:45 to 10:00 am he will be consider Late, after 10:00 he will be consider Half leave, and if he is absent then formula show the person will be absent.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)