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

  1. Hello Ma'am

    I want answer this 2 questions.

    Question-Get work hours between dates custom schedule using the following formula
    options
    a) {=SUM(CHOOSE(WEKDAY(ROW(INDIRECT(date1&":"&date2)),1,2,3,4,5,6,7))}
    b) {=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1":"date2))),1,2,3,4,5,6,7))}
    c) {=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&":"&date2))),1,2,3,4,5,6,7))}
    d) {=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&";"&date2)),1,2,3,4,5,6,7))}

    Quesiton-Which of the following can be accomplished by Creating Highlighted Cell Rules?
    Note: There may be more than one right answer.
    Options
    a)Which cell values are less than 0?
    b) What are the top 10 values?
    c) Which cell values are above average, and which are below average?
    d) Which cells values are duplicate?
    e) Which cell values are less than 010?

    Thanks

  2. how to calculate total hours when date and time written in separate columns like.

    start time . start date . end time . end date ----- total hours

  3. How do you create an invoicing formula that multiplies an hourly pay rate (£) with total hours, mins?

  4. what formula should i use to get time difference in quarters like 7:00AM - 8:15AM = 1.25 ? IF 15min equal to .25 and 30 mint to .50 ?

    1. If 7:00am is in cell A1 & 8:15am in cell B1 formula should be =sum(b1-a1)*24 hope this helps (don't forget to format the results cells as "number")

  5. Hello everyone, i m working as a MIS Analyst, pls suggets me to time Calculation like shift started in the evening at 6.30 PM and Leaving time is morning 4.00AM, pls suggets with Formula for Time Calculation.

    its very urgent...

  6. how to put an If function in the following scenario:

    time-in schedule no. of mins late
    7:10 AM 7:00 AM 10 mins
    6:45 AM 7:00 AM O mins

    Thank you

  7. I noted in your formulas above the date and time are in the same cell. How do you work these formulas with the dates and times in separate cells? Is it possible or do they have to be in the same cell. I'm trying to calculate the time an individual would be held in restraints. The individual cells have Event Begin Date, Event Begin Time, Event End Date, Event End time. All the cells are in general format since they are exported from a database into Excel. What guidance can you give me.

  8. I am trying to calculate the sum of two time(S).
    I tried to add 15:05:30 and
    14:02:00 and used the formula =sum(DE19:DE20)resulting into 5:07:30. After using =sum($DE$19:$DE$20), I got the same result. Please help.

    Also please tell me if I want to add multiple timestamps each timestamp of the format(day hour:month:second),which formula I should apply?

    1. Your expected results would be 29:07:30. Since one day is 24 hours, the balance you see is 5:07:30. It is a question of formatting the results (Control+1). Try using a "custom" format of [h]:mm:ss.

  9. Hi,
    If i insert a time in a colum, i need to change the time automatically in another colum.
    for example if i put 10:00 in A1 then automatically B1 need 10:05 and C1 will 10:20 like.
    Please help me.
    Thx

  10. I need a formula to calculate the time difference with sum subtraction on time

    for example:

    11:00 PM -01:00 AM = 02:00 HRS

    but 11:00 PM -01:00 AM to show me nothing

    Kindly help me on this.

    Thanks in advance

  11. If I have total hours of let's say 52 for a work week and only want 4o hours to show as "Regular Hours" and the additional 12 hours to be in a different cell for "Overtime Hours"?
    I have tried IF and MIN formulas but neither seem to work. I have a feeling it has something to do with the way I am writing them, i.e., =IF(XX>40,40,XX) or =MIN(40,XX) where "40" is not in a "TIME" format.

    What am I doing wrong?

  12. Hello there,

    None of these formulas work on my time cell. It is in the following format: 9:23am and I have tried all of the formulas to subtract one time from another also, to convert the time to 24 hour clock and nothing works on my time column! It is exported data from Xero accounting software and I split out the date from the time, but now I'm really stuck. Help?

  13. I am using the following formula to calculate difference in time. =INT(B1-A1) & " days, " & HOUR(B1-A1) & " hours, " & MINUTE(B1-A1) & " minutes and " & SECOND(B1-A1) & " seconds" however I wanted to see if it was possible to put some time of if, then formula with it to remove any hours that are between 5pm and 8am also any hours that are on a week end so 48 hours for Saturday and Sunday?

  14. I need to create a spreadsheet with 40 names for start lunch and end lunch

  15. Hi,

    I have shift start time & shift end time
    I want to calculate total hours worked during 22:00-6:00
    Please help me with the formula

  16. Thanks

    It is very convenient and useful to us.

  17. Dear Sir,

    I use this formula to calculate time difference in Days hours and minutes

    =INT(B2-A2) & " days, " & HOUR(B2-A2) & " hours, " & MINUTE(B2-A2) & " minutes"

    Which shows the difference of time in Column C now in C i have 10 differences of time from C2 TO C12 now i want to take sum of these 10 values in Column C as a total like Total Days & Hours & minutes what formula should i apply ??

  18. How can we change the real time to accumulative time:

    11:20:32
    11:32:36
    11:41:28
    11:53:36
    12:02:48

    how can I change this to
    0
    12
    .
    .
    .

  19. Hi, I need your help please let me know how to calculate the difference between two date using networkdays formula.

    exp: Start date 4/12/16 3:51 PM and end date 4/20/16 5:00PM.

    currently I am using below formula,
    =IF(INT(AJ3-F3)>0,INT(AJ3-F3)&"d","")&IF(HOUR(AJ3-F3)>0,HOUR(AJ3-F3)&"h")

    Above is not working for networkdays.

    Please suggest.

  20. HI, I need help with a time formula. C1 is 13:40, C2 is 16:40 and C3 gives me the difference of 3:00. I have these formulas set up. What i am trying to do is check another cell, (C5) for "specific text" prior to returning a result of C3 as the full value (3:00) or half value (1:30) to C6.

    Thanks in advance
    Doug

  21. HI, I need help with a time formula. C1 is 13:40, C2 is 16:40 and C3 gives me the difference of 3:00. I have these formulas set up. What i am trying to do is check another cell, (C5) for "specific text" prior to returning a result of C3 as the full value (3:00) or half value (1:30) to C6.

    Thanks
    Doug

  22. =A2 + TIME(2, 0, 0)

    hello

    thanks a lot it was very good but I have a question.

    I want to add one hour to my time but I have a large data in one column, how can I write the formulation to apply it for whole of the data in the column once?

    thanks in advance

  23. I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. Any input would be appreciated.

    (Start Date/time) A1 = 11/25/2016 14:00
    (Hour to fix) B2 = 20 hours
    (Fix date/time) C1 = fix time?
    Business hour = Mon - Sun 08:00 - 20:00

    I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task including weekends (End Date). This would be based on a certain number of "business hours" that would be calculated in another cell.

  24. I'm Trying to Subtract the Below said wrong time format, Answer should be =30, Please help me

    A1 = 03.00PM
    A2 = 2:30 PM

  25. Hi,

    I need to calculate day wise hour wise penalty.

    In a day (24 hours) exemption hours are 20 (total 24 months).

    If down time is > 4 hours in a day, Per Hour Rs.200/- penalty.

    Pls share formula..

  26. Best explanation I have ever seen... Many thanks to Svetlana Cheusheva.

  27. I am trying to calculate total hours worked in a day with the result being in number instead of time. For Example 8.5 instead of 8:30. Using clock in(C27), lunch out(G27), lunch in(H27), clock out(L27) to calculate total hours. I am using =(L27-H27)+(G27-C27). I also have the cell with the results formatted as (Custom [h]:mm). What can I add to the formula or how should I format the cell to change the results to my liking?

  28. i am calculating day and month difference in twodays
    cell a1 07/07/2016
    cell b1 07/10/2016
    formula =TEXT(B1-A1,"m") it displaying 4 but the difference in month is 3

    =TEXT(B1-A1,"dd") its displaying 1 but the difference in days should b 0

    plz revert

    and plz let me know also can v calculate years in two dates

  29. does anyone have a template?

  30. I would appreciate help with formulas for the following:
    Sign in time: 9:35am
    Minus max duty day: 18 hours
    Minus debrief: 30 minutes
    Minus flying time: 14 hours, 20 minutes
    What I need is the final time after the above calculations.

    Thank you!

  31. I would like to calculate end time.
    I have Start time and number of hours but looking to calculate end time. Example: Start time 5 am, 9 hours, can you help me calculate end time in excel? I have 350 entries.

  32. Hiii.,
    I need a formula for creating a sheet in excel for notification means today is 25th Oct 2016, so i need from 25th to every 29days have to remind in excel and auto deduct amount on 29th day of every month.

  33. Many of the help requests posted here exhibit a basic misunderstanding about how Excel looks at time. When we see hh:mm we may be thinking "duration", and 29 hours 57 minutes makes perfect sense to us. When Excel sees hh:mm it always thinks "clock", and I've never seen a clock with 29 hours marked on the face.

    On a blank Excel spreadsheet, pick an empty cell formatted as "General" (default). Enter "hello" in that cell. Note that the format didn't change.

    Now replace "hello" with "7:30". Did the format of the cell change to "Custom (h:mm)"? Look at the Formula Bar. Does it say "7:30:00 AM"? That's a clock reading. We may have meant 7 and a half hours, but Excel understood it as 7:30 AM.

    Now enter two durations, 14:27 and 19:42, in cells A1 and A2. In A3, sum A1 and A2.

    We expect a sum of 34:09, but we get 10:09 instead. Why? Excel is adding clock times, and there's only 24 hours on the clock. So Excel's answer is 1 day, 10 hours and 9 minutes. But cell A4 doesn't automagically format as d:hh:mm. It formats as h:mm, so all we see is 10:09 - the hours and minutes. Now change the format on cell A3 to d:hh:mm, and it will make more sense.

    But we really wanted the result in just hours and minutes, not days. Enter this into cell A4 to convert the total to hours and minutes:
    =DAY(A3)*24+HOUR(A3)&":"&TEXT(MINUTE(A3),"00")

    Note that cell A4 is formatted as General. That isn't an Excel date/time format, so we can't perform math functions on/with it.

    Excel dates are basically integers - one for every day starting with 1/1/1900. For example, 6/6/2016 is day number 42,527.

    Excel times are decimal numbers representing a portion of a day, and there are 86,400 seconds in a full day. 11:53:15 equates to
    11 hours x 60 minutes x 60 seconds
    + 53 minutes x 60 seconds
    + 15 seconds,
    or a total of 42,795 seconds. Dividing that by 86,400 seconds, we see that 11:53:15 converts to 0.4953125 days.

    So Excel represents 6/6/16 11:53:15 AM as 42527.4953125.

    Because dates and times are just numbers to Excel, they can easily be added and subtracted. The results can be formatted simply as numbers, or in a goodly number of standard date/time formats.

    If the result of your date/time manipulations don't look right, check the cell format - there may be more data there than meets the eye.

    And if you want the results of date/time manipulations to be formatted in some way other than the standard Excel date/time formats, do all the math first using standard formats - then convert the results to the format you require.

  34. That did the trick. Thank you!!!

  35. I need to add seconds with the final result of minutes and seconds i.e. 30+30+15+5 which sums to 1 minute and 20 seconds and should display as 1:20. Anyone have any idea of a formula that will work?

    1. Enter your seconds in col A as numbers.
      =SUM(A:A)/(86400) {formatted as "m:ss"}

  36. I'm trying to create a time sheet to calculate the hours for the employee where I need to round up In time and out time by quarters and deduct lunch break. For example

    In time Break In Out Out time
    08:09 = 8:15 13:00 13:50 17:38 = 17:45 Total???

    How can I formulate the last column into calculate the total hours worked?

    Thank you!

    1. Viktoria,
      Per the rules as stated (starttime 8:00 = 8:00, starttime 8:01=8:15; similar for stoptime; breaktimes not adjusted to quarter-hours), this should work for the Total column (E):
      =(HOUR(D1)+CEILING(MINUTE(D1)/15,1)/4)/24-(HOUR(A1)+CEILING(MINUTE(A1)/15,1)/4)/24+B1-C1

  37. I answer phones virtually and I am trying to calculate the total # of mm:ss.
    Each call I take has a total talk time. So like I took 6 calls.
    4:23 4:23
    1:45 6:08
    2:24 8:32
    2:00 10:32
    5:36 16:08
    10:52 3:00 (This should be 27:00). But anything over 24:00 won't calculate correctly. So how do I fix this?

    1. You think you're entering minutes:seconds, but you're actually entering hours:minutes, which is why anything over 24 minutes goes haywire.
      Format the columns as "mm:ss", but enter the data as h:m:ss - in other words enter 0:5:36 instead of 5:36. Then just subtract col A from col B.
      02:00 10:32 08:32

  38. i need over time formula
    eg.
    a2 09:00 b2 18:00 (c2 ans =a2-b2 we get 09:00)

    but we need over time after this result we want -08:00 hour working day
    if i do b2-a2-08:00=######

    what is the perfect formula for over time result will you help me please

    1. =B2-A2-8/24
      (subtract 8/24 of a day rather than 8 hours)

  39. i want formula for
    3:15 pm to 12:15 am

    if 12:15 am - 3:15 pm = ######
    what is correct formula for it how it is come
    12:15 am - 3:15 pm = 9:00

  40. Hi, I am trying to find the average for response times.
    Here is a sample of data. I have 1,607 entries.

    0:04:50 0:04 04:50 04:50
    0:23:16 0:23 23:16 23:16
    0:11:12 0:11 11:12 11:12
    0:20:38 0:20 20:38 20:38
    0:00:15 0:00 00:15 00:15
    0:02:59 0:02 02:59 02:59
    0:03:32 0:03 03:32 03:32
    0:02:54 0:02 02:54 02:54
    0:13:58 0:13 13:58 13:58
    19 11:14 7075:11:14 7075:11:14
    days hours minutes

  41. I need to figure out the total time worked by an employee. we have a program that time stamps every action they make. I need to subtract any time gaps over 15 minutes and then total the time. Please help.

    16:21:47 '06/25/2016
    16:21:43 '06/25/2016
    16:21:37 '06/25/2016
    16:21:37 '06/25/2016
    16:21:37 '06/25/2016
    16:21:34 '06/25/2016
    16:20:43 '06/25/2016
    16:19:57 '06/25/2016
    16:19:57 '06/25/2016
    16:19:57 '06/25/2016
    16:19:50 '06/25/2016
    16:19:48 '06/25/2016
    16:19:48 '06/25/2016
    16:19:47 '06/25/2016
    16:19:36 '06/25/2016
    16:19:35 '06/25/2016
    16:19:14 '06/25/2016
    16:18:58 '06/25/2016
    16:18:52 '06/25/2016
    16:18:46 '06/25/2016
    16:18:46 '06/25/2016
    16:18:21 '06/25/2016
    16:18:14 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016

    1. Add column B for gaps of 15+ minutes:
      =IF(A1-A2>1/96,A1-A2,"")
      Then hours worked =LARGE(A:A,1)-SMALL(A:A,1)-SUM(B:B)
      {this presumes the same date for all timestamps}

  42. oops

    =IF(AND($Login = start time),1,0) which is
    =IF(AND($H3=M$2),1,0)

  43. I want to populate "1" for the time between login and logout
    currently Im using =IF(AND($Login=Start time),1,0) wich is =IF(AND($H3=M$2),1,0). however for the time login 10:30PM and loug out time is 7:00 am it does not give a required results . Could anyone help me on this

  44. hi,
    I want to subtract 11/09/2016 8:20:00 PM - 12/09/2016 2:00:00 AM

  45. i want to multiply time by currency with if statement like
    1:10 if time is 1:10 show me $150
    how i can make this astatement in excel

  46. Hello,

    I am Adam and just joined this forum. Could anyone help to calculate time durations in excel when data (In and Out) like in punch cards are in adjacent column and row. Is there any formula to handle this situation.

    Thank you

  47. Hello I m Sufiyan

    How to Calculate in Time and Out Time suppose here workers punching machine but machine showing this type timing 15:02:00 .but i need in one cell in time and other cell out time i need this type formula who came before 11:30:00 then will be in time and after 11:30:00 then out time
    =IF(AND(L2>TIME(11,30,0),L2<TIME(24,0,0)),"out time","in time") i m using this formula but not getting accurate result
    L2 is timing 15:02:00

    pls suggest formula

  48. A have several times in one column. first cell is starting time, last cell is ending time. Multiple combinations where number of cells between starting and ending time is not same number.
    I need to calculate durations for each.

    thanks in advance

  49. Please give me a positive feedback soon!!!!!!!.

  50. 8/22/2016 2:20:00 PM
    8/19/2016 4:35:00 PM
    how to calculate total time
    pls suggest formula and send to my mail ID

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