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 19. Total comments: 1023

  1. 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!

  2. 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.

  3. 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

  4. 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.

  5. 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.

  6. 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?

  7. 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

  8. 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/

  9. 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.

  10. 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!

  11. 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.

  12. 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

  13. 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

  14. 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?

  15. 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

  16. 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.

  17. 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

  18. 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.

  19. Please data to data calculate send mi

  20. 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

  21. 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

  22. 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

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

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

  24. 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.

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

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

  27. 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

  28. 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

  29. 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

  30. 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.

  31. hi,i have problem on time calculation:
    have to find the elapsed time example.
    start time= 23:45, end time= 02:15.answer must 2:30 .
    but it is not work. pls help

    1. Hello,

      If I understand your task correctly, try to use one of the following formulas:

      =TIME((HOUR(B1)+24)-HOUR(A1),MINUTE(B1)-MINUTE(A1),0)

      or

      =(B1+24)-A1

      Where cell A1 is “23:45”, cell B1 is “02:15”

      Hope it will help you.

  32. Thanks a lot man....
    =(clr_time-occur_time)*1440 worked for me!

  33. hello got an easy one for you.. hopefully.

    i have worked out my formula to calculate hours worked in total for a day eg;
    06:00 - 16:00 = 10 works fine. however when the time goes past midnight it doesnt work because its a negative eg;
    18:00-06:00 should be 12 hours but comes up #########

  34. Hey,,

    Thanks for the information you shared really useful!
    But let's say that we have this table
    Time of sample Seconds between samples*
    9:30 5550
    12:35 9450
    14:45 6150
    16:00 7650
    19:00 5400

    *assuming start sampling time 9:30 and end sampling time 19:00
    How I will find the Seconds between samples as above using formulas in excel??

    1. Hello,
      If I understand your task correctly, please try the following formula:

      =TEXT((HOUR(VALUE(A3)-VALUE(A2))*3600)+(MINUTE(VALUE(A3)-VALUE(A2))*60),"@")

      Hope it will help you.

  35. ABISHA J Nov 22 2017 03:28 Nov 22 2017 03:36 UG STUDENT
    ABISHA J Nov 17 2017 04:03 Nov 17 2017 04:40 UG STUDENT
    ABISHA J Nov 14 2017 02:56 Nov 14 2017 04:30 UG STUDENT
    ABISHA J Nov 13 2017 03:12 Nov 13 2017 04:02 UG STUDENT
    ABISHA J Nov 11 2017 11:14 Nov 11 2017 03:27 UG STUDENT
    ABISHA J Nov 09 2017 03:13 Nov 09 2017 04:48 UG STUDENT
    ABISHA J Nov 08 2017 03:46 Nov 08 2017 05:14 UG STUDENT
    ABISHA J Nov 07 2017 03:39 Nov 07 2017 05:47 UG STUDENT
    ABISHA J Nov 06 2017 03:42 Nov 06 2017 05:07 UG STUDENT
    ABISHA J Nov 02 2017 04:16 Nov 02 2017 05:17 UG STUDENT
    how to Calculate total hours
    ADARSH VIGNU M Nov 22 2017 03:32 Nov 22 2017 04:02 UG STUDENT
    ADARSH VIGNU M Nov 09 2017 02:54 Nov 09 2017 03:24 UG STUDENT
    ADARSH VIGNU M Nov 07 2017 03:49 Nov 07 2017 04:37 UG STUDENT
    ADARSH VIGNU M Nov 06 2017 03:49 Nov 06 2017 04:47 UG STUDENT
    ADARSH VIGNU M Nov 02 2017 04:41 Nov 02 2017 05:21 UG STUDENT
    ADARSH VIGNU M Nov 02 2017 03:16 Nov 02 2017 04:28 UG STUDENT

    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.

  36. Hi,

    I have a problem of summing spent time like below
    An employee has spent time as below in two ocation

    7hr and 48mnt
    52 mnts

    Altogether should be 8hrs and 40mnts
    I have summed as below but it doesnt come

    7.48+.52=8

    How do i take 8.4 (8hrs and 40 mnts) as answer.

    Appreciate your advice,

    Thank You

    1. Hello, Ruwanara,

      Please try the following formula:

      =TIME(7,48,0)+TIME(0,52,0)

      Hope it will help you.

  37. Hi I am working on a project where I would like to know the time duration between to given dates/times - HOWEVER I would like this to be based off of a work day (8 hours). So for example -

    A1:3/13/2017 8:47 AM *Creation Date
    B1:3/16/2017 7:29 AM *Closing Date
    C1:7:00 AM *Time in each day
    D1:4:00 PM *Time out each day

    I would also like this to exclude weekends if possible.

    I need the time difference between A1 and B1 based off of work day.

    Any tips would be appreciated

    1. Hello, BRITTANY,

      Please try the following formula:

      =IF(NETWORKDAYS(A1+1,B1-1)>0,NETWORKDAYS(A1+1,B1-1)*8,0)+HOUR(IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),0,IF((TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1))))+HOUR(IF(OR(WEEKDAY(B1)=1,WEEKDAY(B1)=7),0,IF((TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)))) & " hours " & MINUTE(IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),0,IF((TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(A1), MINUTE(A1),SECOND(A1))-C1)))) + MINUTE(IF(OR(WEEKDAY(B1)=1,WEEKDAY(B1)=7),0,IF((TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)>=(D1-C1),(D1-C1),(TIME(HOUR(B1), MINUTE(B1),SECOND(B1))-C1)))) & " minutes"

      Hope it will help you.

  38. WHAT FORMULA DID I USE FOR THIS FORMAT

    16:00 22:00 = 5

    1. Hello, MARCO,
      If I understand your task correctly, please try the following formula:
      =HOUR(TIME(22,0,0)-TIME(16,0,0))
      Hope this will help you!

  39. I have tried different formulas to get diference in days/hrs but due to following date/time format Im getting stuck can you please help 9each date & time is in the same 1 x cell
    2017.10.03 04:03:27 2017.10.04 16:49:50

    Regards

  40. Hi

    I'm trying to produce a production schedule for a manufacturing plant how can I add production-time to calculate finish time/date. From this I need to subtract weekends and non production hours ie 10pm-6am.

    Example

    Start date & Time = 8/10/17 & 06:10
    Total production time = 148hrs
    End date & time (minus weekends and hours between 10pm-6am) = ?

    1. Hi, Dave,

      our tech specialist has come up with the following formulas for you:
      1) if A1 contains start date & time, and A2 contains 148hrs, here's a formula to show day/month/year:
      =TEXT(IF(VALUE(TIME(HOUR($A$1),MINUTE($A$1),SECOND($A$1)))+VALUE(TIME(HOUR($A$2/(22-6)),0,0))<=VALUE(TIME(22,0,0)),WORKDAY.INTL($A$1,INT($A$2/(22-6)))+(($A$2/(22-6))-INT($A$2/(22-6)))+VALUE(TIME(IF(HOUR($A$1)<6,6,HOUR($A$1)),MINUTE($A$1),SECOND($A$1))),WORKDAY.INTL($A$1,INT($A$2/(22-6))+1)+VALUE(TIME(6,0,0))+(VALUE(TIME(IF(HOUR($A$1)>22,22,HOUR($A$1)),MINUTE($A$1),SECOND($A$1)))-VALUE(TIME(22,0,0))+($A$2/(22-6)-INT($A$2/(22-6))))),"DD/MM/YY hh:mm AM/PM")

      2) this one to show month/day/year:
      =TEXT(IF(VALUE(TIME(HOUR($A$1),MINUTE($A$1),SECOND($A$1)))+VALUE(TIME(HOUR($A$2/(22-6)),0,0))<=VALUE(TIME(22,0,0)),WORKDAY.INTL($A$1,INT($A$2/(22-6)))+(($A$2/(22-6))-INT($A$2/(22-6)))+VALUE(TIME(IF(HOUR($A$1)<6,6,HOUR($A$1)),MINUTE($A$1),SECOND($A$1))),WORKDAY.INTL($A$1,INT($A$2/(22-6))+1)+VALUE(TIME(6,0,0))+(VALUE(TIME(IF(HOUR($A$1)>22,22,HOUR($A$1)),MINUTE($A$1),SECOND($A$1)))-VALUE(TIME(22,0,0))+($A$2/(22-6)-INT($A$2/(22-6))))),"MM/DD/YY hh:mm AM/PM")

      Hope this helps!

  41. how can i calculate Total down time hours

  42. DATE Issue Start Time Issue End Time Issue Start Time Issue End Time Issue Start Time Issue End Time Issue Start Time Issue End Time Issue Start Time Issue End Time Total Network Down Time
    01 September 2017 12:17:00 12:47:00 13:17:00 13:47:00 14:17:00 14:47:00 15:17:00 15:47:00 16:17:00 16:47:00

  43. Hi,

    Calculate the time duration in different days 9/09/17 18:00 & 09/11/17 11:40.wht is the total time duration b/w the days

  44. how can i get the difference in years,month,days,hours,minute,seconds
    for example-
    (20-09-1993 19:00:00)- (27-08-2017 14:00:15)

  45. =IF($C7="","",(MOD(D7-C7,1))*24)+IF($E7="","",(MOD(F7-E7,1))*24)+IF($G7="","",(MOD(H7-G7,1))*24)+IF($I7="","",(MOD(J7-I7,1))*24)+IF($K7="","",(MOD(L7-K7,1))*24)+IF($M7="","",(MOD(N7-M7,1))*24)+IF($O7="","",(MOD(P7-O7,1))*24)

    I know that I am missing something in this formula. If anyone can spot it let me know. This is in/out times, same day, several jobs, i.e., 1 hr at 1 job, 6 at next job, total for the day is xxx hours.

  46. Need help with how to calculate duration between date with time, less 30 minutes for example:
    A1 08/22/17 8:00 am - B1 08/20/17 8:30 am = 2 days

    Thanks
    Maggie

    1. Hello, Maggie,

      Please try out the following formula: =ROUNDUP((A1-B1)*24,0)/24 & " days"

      Hope this will help.

      1. Hi Ekaterina,

        Thanks for the help but it's not calculated proper. Maybe, I did not explain well, for example:

        A1 5/2/16 17:32 - B1 5/3/16 19:59 should return 2 days not 1 day

        Regards,
        Maggie

  47. I am trying to sum some values which are in word forms (0 Days, 1 Hours, 10 Minutes + 0 Days, 17 Hours, 54 Minutes + 0 Days, 0 Hours, 12 Minutes). Can you help me to sum these values?

    1. Hello, Komal,

      you need to create a custom time format that will allow summing the values when they're written as you need. You can read how to create such a format in our article.
      Hope it helps.

  48. Hello,

    Can you help me create a formula to calculate time between these two?

    2017-08-16 18:24:00 2017-08-17 06:40:00

  49. How is it with 24 hour working time? For example:start 23:00 finish 01:30 should equal 150 minutes.

  50. Thanks a lot. its very helpful.

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 :)