Comments on: Show time over 24 hours, 60 minutes, 60 seconds in Excel

When subtracting and adding time in Excel, you may sometimes want to display the resulting time intervals over 24 hours in the hh:mm format, over 60 minutes in the mm:ss format, and over 60 seconds in the ss format. The task is a lot easier than it may sound, and you will know the solution in a moment. Continue reading →

Comments page 2. Total comments: 200

  1. I need to work out a formula to count down a 24hours clock example

    Start time end time time elapsed Time remaining Time clock 24 clock runs out

    19/10/22 - 00.55hrs 19/10/22 - 12:05hrs 12hrs 00 mins 12 hrs0 mins 20/10/22 - 00.55hrs
    28/10/22 - 1500hrs 28/10/22 - 2100hrs 6hrs 00 mins 6 hrs 0 mins 28/10/22 - 2100hrs
    30/10/22 - 1700hrs 30/10/22 - 2100hrs 4hrs 00 mins 2 hrs 0 mins 30/10/22 - 1100hrs
    31/11/22 - 1700hrs 2 hr 0 mins 31/10/22 - 1900hrs

    This will continue for a further 4 lines of code to.

    Each entry is linked to the previous entry to count down the 24hrs clock

    Does anyone know how to write this formula please

    thanks

    1. Hi!
      Unfortunately, I can't understand what you want to count. Describe in detail what result you want to get from your data. As it's currently written, it's hard to tell exactly what you're asking.

  2. i'd like to start a count down of hours left - 3000 hours minus what cell B2 shows. Can you please share how to do this?
    much appreciated.

      1. thank you!! :)

  3. I have a report that gives seconds and I need an excel formula that will display that as HH:MM:SS - Anything over 86400 displays improper number of hours. Thanks

    1. Hi!
      Did you use the [h]:mm:ss time format as recommended in the article above? How many hours do you get and what do you want to get?

    2. Go it - Divide by 86400 and format as {h}:MM:SS -

  4. Hi, I've date-time format like 15-03-2020 01:50, Now I want to display the time as 13:50 and so on.. the problem is these times are autogenerated and don't have AM or PM. How I need to accomplish.

  5. Thank you very much for explaining this topic in a very simple way!
    This helped me alot. :)

  6. Hi,
    Iam working in aviation field, because of that i need to calculate the larger values in hours.
    For eg: 12346:00 - 12345:00, I know that answer should come as 1 hour.
    Instead, its showing as "#VALUE!".

    Please suggest some formulas for the same.

    1. Hello!
      Unfortunately, Excel does not allow you to enter more than 4 characters in the number of hours. Your values are written as text.

      1. Thank you for that,
        Is there any other way to track these hours calculation.

        For example, if the current value is 12345:6 hours, but after each flights these values need to be updated.

        Thank you in advance.

        1. Hello!
          You can get the number of hours from a string using the formula -

          =--LEFT(A1,SEARCH(":",A1,1)-1)

          The number of minutes can be extracted by the formula

          =--MID(A1,SEARCH(":",A1,1)+1,10)

          Here is the article that may be helpful to you: Extract number from text string.

          1. Hi, I have same issue, working in aviation and want to add/subtract aircraft flying hours/mins using excel e.g
            52166:40 - 52163:49

            I see your previous response above but dont understand how extracting th number of hours and minutes is the solution. Please advise if there is a way to do this calculation and show correct results in hh:mm format .

  7. Hi,

    Please suggest formula to get working time beyond 24 hrs. Like in time 5:50 am in 21st jan, Out time 8:24 am next day. I need a formula which display total working hrs like 27 hrs 34 min.

      1. Hi is there any error in my message? which article you are suggesting? please share the link

        1. Its not working. calculation includes midnight span.

          IN TIME OUT TIME TOTAL WORKING TIME
          5:50 AM 8:24 AM 2:34:00

          I NEED TO PLAY 27[H]:34 [MM] in total working time cell.

          I used =IF(BN7>BO7,BO7+1,BO7)-BN7 this formula but its not working beyond 24 hrs.

          1. Hi!
            As far as I can see from your second comment, your task is now different from the original one. To get a difference of more than 24 hours, you need to use the date and time, not just the time.

  8. Is there a formula to take total hours worked in a year but only change the format for minutes to a decimal?
    Ex: 574:40 ([h]:mm cell format
    convert to 574.67 hours
    * I want to keep the hours the same but convert the number after the : to a decimal
    Thanks

  9. i have clock in and clock out as am to pm in one row like wise for a week ,i need to add total no of hours for that week FOR EXAMPLE
    ram
    9:30AM-2:30PM
    3:20PM-9:40PM
    1:30PM-9:50PM IN THIS WAY PER WEEK

    HOW TO USE FORMULA PLZ HELP ME.

    1. Hello!
      To convert text to time and find the difference, use the formula

      =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("-",A1,1)+1,50),"P"," P"),"A"," A")) -TIMEVALUE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,SEARCH("-",A1,1)-1),"A"," A"),"P"," P"))

      Then you can find the sum of these values.

  10. I have a spreadsheet with accrued vacation leave and accrued floating holiday totals available. I need to let the employee know how much time they have to use by the end of the year so they don't loose any time. They can carry over 160 hours into the new year. Currently one field is formatted as text and the other total is formatted custom ( [h]:mm;@)

    1. Hi!
      Your description assumes that I know your details. But I don't know what "floating holiday totals" means, which field is formatted as text, and what is written in it. Describe in detail your data and what you want to calculate.

  11. How do I calculate time remaining till 40 hrs.

    For example: I have clock in, clock out, lunch, and total time for the day. Clock in 8:00, clock out 4:30, lunch 0:30, total time 8:00. Then I have SUM total time Mon-Fri in hr:mm.

    How do I calculate 40 hr - current total time to see how many hr:mm I have left till I’m working 40 hr?

    Thanks for your help!

    1. Hi!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data.
      Without seeing your data, I can suggest the following formula:

      =TIME(40,0,0)-SUM(D2:D6)

  12. Hii sir i need total hrs calculattion like this example. 102hrs:45mns + 01hrs:10mns = 103hrs:55 mns how to use the formula in excel

  13. i need the duration formula for 23:40 to 02:00

  14. If I have an end time of 9:00am and I need to subtract 160 minutes to get a start time, how do I do that?

    1. Hi!
      Pay attention to the following paragraph of the article above: "How to add / subtract more than 24 hours, 60 minutes, 60 seconds"

      1. Alexander,

        I can't get it to work, I receive the Value error.

        1. 26 Hours 70 Minutes 70 Seconds

          1. Hi!
            Please remember of the time units. since it can be only up to 60 mins and 60 secs.
            27 Hours 11 Minutes 10 Seconds

  15. Hi,

    How to filter data between date and time range as below:

    From Date:
    To Date:

    From Time:
    To Time:

    I have four criteria to extract data from that.

    Please advise.

    Kiruban

  16. Hi, can you help me, i want to change 12 hrs to 24hours for example [hh:mm am - hh:mm pm] all these are in one cell [hh:mm:ss - hh:mm:ss]

  17. Hi there, My system produces a data dump and shows a booking timeslot in the following format. 1330-2030 .I am currently using =RIGHT(D2,4) and =LEFT(D2,4) to get the values of 1330 in a seperate start column and 2030 in the end column.

    However there is colon : in this data, how can I
    A) Convert this into time
    B) Do a calculation on excel so that I can subtract both values (end time and start time) to get the booking time of 7 hours.

    1. Hello!
      Here is the formula that should work perfectly for you:

      =TIME(LEFT(D1,2),MID(D1,3,1),0)
      =TIME(MID(D1,6,2),RIGHT(D1,2),0)

      Hope this is what you need.

      1. Dear Alexander,

        Can you please help me to get the below requirement:

        Start Time Minutes Time should be
        2:00 PM 45 2:45 PM
        32 3:17 PM
        30 3:47 PM

        1. Hello!
          To add minutes to the time, divide the number of minutes by 1440.

          =C1+D1/1440

          C1 -- 2:00 PM
          D1 -- 45

  18. Hi,

    How do I subtract 30 minutes from 00:00 (12 midnight) using excel formula?

    1. Hello!
      The formula below will do the trick for you:

      =IF(A1<A2,A1+1-A2,A1-A2)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  19. Sir
    For time comparison in a column the time format is in [H]:MM:SS and for to get results using IF function for greater than 24 hours as beyond time and less than 24 hours what formulae is to be used. Please reply sir.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

      1. Ex if we have times in columns like 34:34:43, 46:57:54, 12:32:56, 44:56:32, 08:45:46 and they are in time format. I want to get result using IF function as for greater than 24 hours to come as "Beyond time" and less than 24 hours as "Within time". Please text the formula and reply sir

        1. Hi!
          If I understand your task correctly, the following formula should work for you:

          =IF(A1>1,"Beyond time","In time")

          1. Hello, in continuation to above query, how can we use if condition for time format that is [h] beyond 24 hours format. Ex. If [h]:mm:ss - 44:19:23 is greater than 2 hours or not.

            1. If I’ve to use if formula for : cell A2 value is 44:20:12 (format of cell value is [h]:mm:ss. Now wish to check if condition : =if(A2>=2 hours,“met”,“not met”)
              Please advise how to check the time in [h] format using if condition.. please help

            2. Hello!
              Time is a common decimal number in Excel. If 2 o'clock is written down as the number 2, then convert it to a decimal number, as described in this article.
              Then just compare the two numbers.
              I hope I answered your question.

              1. The time is calculated on condition that is from 6am to 8pm so it not 24 hours… so what shall be done in this case. That is [h]:mm:ss is 44:20:19 is calculated from networking days and business hours time… now how to convert it in decimal.. please help sir

  20. I have a spread sheet one cell has employee’s dispatch time (military time) example 23:59 (11:59 pm) the next cell has employee’s arrival time example 00:20 (12:20 am).
    Dispatch on one day, arrives a few minutes later (21 minutes) the following day.
    I have figured out the rest of the spread sheet with your help, however these two cells, as soon as I calculated them I get the death formula symbol of ####### any help would be appreciated.
    Thanks for your valuable time. Pat

  21. Good evening sir,
    I have downloaded a file from online and the time is in the format of 000:56:30, 0101:34:66, 132:43:00, 000:34:32, 0345:56:34, 0056:12:35 and so on in this format. While selecting the columns the task bar is showing count of numbers only but not sum of the time and the average. I have used TEXT(Value,"[H]:MM:SS") and after that used TIMEVALUE of that function and it is showing as #Value. I have tried using TIME(HOUR,MINUTES,SECOND) it is showing as #value. Please solve the above problem. I will post u file to Email also if you provide email. Pls solve it.Sir,
    I want to get total sum and sorting of these column in ascending or descending order sir.

    Hi,
    To convert the text 0345: 56: 34 to time, convert the text to a number. For example, if this text is written in cell A1, use the formula

    =--A1 you have given this reply sir.

    Sir I have used the formula it is giving the result #Value. Please provide your email I'd so that I can send file to you sir. Or please send me an email so as to send the file to you sir.

  22. I was able to come up with this formula however now I cannot find my duplicate. I think I've been looking at this for too long. Any suggestions??

    I'm coming up with: 2 months 18 daysG 18daysJ 22 hrs 18 min

    IF(L76936<60,TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysA ""h ""hrs"" m ""min""")),IF(L76936<3600, TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysB ""h ""hrs"" m ""min""")), IF(L76936<86400, TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysC ""h ""hrs"" m ""min""")), IF(L769361," daysD ", " dayE ") & TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysF ""h ""hrs"" m ""min""")), ROUNDDOWN(L76936/2592000,0) & IF(ROUNDDOWN(L76936/2592000,0)>1, " months ", " month ") & ROUNDDOWN(MOD(L76936,2592000)/86400,0) & IF(ROUNDDOWN(L76936/86400,0)>1," daysG ", " dayH ") & TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysJ ""h ""hrs"" m ""min"""))))))

    Thank you

    1. Did come up with this formula but now get a preceding 0 in days when I have 685984 seconds. I would like to eliminate the 0.

      Result: 07 days 22 hrs 33 min

      IF(ISERROR(IF(L76953<60,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")))),0,IF(L76953<60,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L76953<3600,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L76953<86400,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L769531," months "," month ")&TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")))))))

      1. And 2424119 seconds displays as 028 days 1 hrs 21 min

  23. Could you please assist? I am trying to convert seconds to months, days, hours and mins however if it cannot be converted to months then days hours and mins will do. Currently, I have 6819518 seconds which comes to 2months 18days 22hours 18min or 78days 22hours 18min. This is the formula I have currently am using however it becomes an issue when there is more than 30 days. I tried changing 86400 to 2592000 but not yielding the results I need. Any help is greatly, greatly appreciated!!

    IF(ISERROR(TEXT(A1/86400,CHOOSE(MATCH(A1,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""days ""h ""hrs"" m ""min"""))), 0, TEXT(A1/86400,CHOOSE(MATCH(A1,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""days ""h ""hrs"" m ""min""")))

    1. Hello!
      To convert seconds to days, hours, minutes and seconds, use these formulas:
      days (in G3)
      =INT(E2/(60*60*24))

      hours (in H3)
      =INT(E2/(60*60))-G3*24

      minutes (in I3)
      =INT(E2/60)-G3*24*60-H3*60

      seconds (in J3)
      =--RIGHT(E2,2)

      I hope it’ll be helpful.

  24. Good evening sir,
    I have downloaded a file from online and the time is in the format of 000:56:30, 0101:34:66, 132:43:00, 000:34:32, 0345:56:34, 0056:12:35 and so on in this format. While selecting the columns the task bar is showing count of numbers only but not sum of the time and the average. I have used TEXT(Value,"[H]:MM:SS") and after that used TIMEVALUE of that function and it is showing as #Value. I have tried using TIME(HOUR,MINUTES,SECOND) it is showing as #value. Please solve the above problem. I will post u file to Email also if you provide email. Pls solve it.
    Thank you.

    1. Hello!
      Explain what result you would like to get from these text values - 0101:34:66, 132:43:00, 0345:56:34 ?

      1. Sir
        I want to have the sum of those column in time format and want to have sort in ascending or descending order

      2. Sir,
        I want to get total sum and sorting of these column in ascending or descending order sir.

        1. Hi,
          To convert the text 0345: 56: 34 to time, convert the text to a number. For example, if this text is written in cell A1, use the formula

          =--A1

          Please check out this article to learn how to convert text to number with formula and other ways.

          1. Sir I have used the formula it is giving the result #Value. Please provide your email I'd so that I can send file to you sir. Or please send me an email so as to send the file to you sir.

            1. Hello!
              Use the following formula:

              =IF(LEFT(A1,2)="00",--MID(A1,3,20),--A1)

              Please note that 0101:34:66 is not the time. There are no 66 seconds.

  25. I am looking to identify the total minutes for the following that has been split into days, hours, minutes, seconds: in the format of 01:04:56:05
    01 - in theory 24 hours
    04 - 4 hours
    56 - minutes
    05 - seconds
    I should be able to see: 1736 minutes

    1. Hello!
      To convert hours into minutes, you must first multiply the specified time by 60 (the number of minutes in one hour), then by 24 (the number of hours in one day). In other words, we need to multiply time by 1440.
      I believe the following formula will help you solve your task:

      =TIMEVALUE(MID(A1,4,20))*1440+60*24*(--LEFT(A1,2))

      Hope this is what you need.

  26. How to add 6 hour 24 min 40 secs + 2 hours 39 min 30 secs

  27. How easy was that...?! Beautifully explained and so easy to follow. Thank you for the first rate help.

  28. Hi guys,

    I have a start time of 18:00 and end time is 01:00 the next day, when I calculate = End time - Start time I get a negative value of -17 instead of 7 hours . Which formula to use to to get the correct positive value of 6?

  29. Thank you after struggling to find it in the Excel help. 30 seconds of searching on Google and reading your article fixed it.

  30. Hi excel specialist
    How can I change from excel format from 24-00 to 24.00. what is the formula
    Pls. advise thanks in advance hope to hear from anyone.

  31. Setting up a time card, I have 9 columns, 3 sets of IN time and OUT time. I can get columns to add up using a 24 hour format when it's a low vs a high hour such as IN:0600 / OUT:1600 for a total of 10 hours. However, what I can't figure out is how to formulate In:2000 / OUT:0600 for a total of 10 hours as the amount of time worked was 10 hours.

    I know out how to get it to auto sum hours worked when I format the cell (mm/dd/yyyy h:mm) but that over populates the time card and makes it a lot harder to read. Is there a way to auto calculate the total hours worked using (h:mm) and auto sum the 3 column sets into a single total hours worked regardless of what day and time they clocked in?

    My crews work 24 hour rotating shifts logging between 8 to 14 hours at a time. They also check out between the start and end of the shift and have to clock back in.

    For Example; IN-19:00 (Begin Shift) OUT-24:00 (30 Min Meal) / IN-00-30 OUT-0330 (30 Min Rest) / IN-0400 OUT-0600 (Time to go Home) Total time worked: 10 Hours

    I want to set up a time card they can just put in the time they clock in and the time they clock out to simplify their lives. It'll also help keep from displaying unnecessary data for our finance clerk and speed the processing of payroll up.

    1. Hello Rick!
      If the end time is longer than the start time, then you need to add 1 day to their difference

      =IF(A1 > A2,A2-A1+1,A2-A1)

      If you need the number of hours to be more than 24, use the custom format [h]:mm:ss.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  32. Kindly help this condition
    my job time 9:00 am and grace time start 9:15 am
    I want to calculate 9:16 am to start late time in minutes

    reply

  33. I want to calculate the attendance sheet but the hours total in not coming right.
    ... .. but when i get the total time it gives wrong answer. i have change formatting using different time format but did not get the right answer.
    Please help me to solve this problem. total weekly hours 8 to 4 pm (wait for your reply)
    regard.
    Maqsood
    time in Time out time in time out
    8:30 3:45 8:00 4:00

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  34. Hi,
    I have a data dump from a scheduling system that displays the time in excel as follows:
    1.00:48:34 - I am reading this as 1 day, 48 minutes, 34 seconds.
    1.10:19:09 - 1 day, 10 hours, 19 minutes and 9 seconds.
    When I try to add these cells in Excel, I get nothing. What time format can I convert these to and/or what formula could I use?

    1. Hello Alan!
      If I got you right, the formula below will help you with your task:

      =LEFT(A1,FIND(".",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))

      Please go to Format Cells, choose Number -> Custom Format and set

      d"d."h"h."mm"m."ss"s."

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  35. Hi, I'm trying to subtract 2 hours from 01:30:00. It should give me 23:30:00 but I keep getting an "#########" answer.

    1. Hello Stan!
      If the end time is greater than the start time, the following formula should work for you:

      =IF(B3 > B1,B1-B3+1,B1-B3)

      To the start time you need to add 1 day.
      I hope it’ll be helpful.

  36. How do I get 1 hour to show as 60 minutes in excel? Example: it shows 1:00:00.0 currently but would like it to show 60:00.0 instead.

    1. Hello Adam!
      Please go to Format Cells, choose Number -> Custom Format and set
      [mm]:ss.0;@

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  37. HOW CAN I CONVERT 110.5HRS TO DAYS / HOURS / MINUTES IN EXCEL FORMULA

    PLEASE HELP

    1. Hello Jerry!
      Please use the following formula
      =A1/24
      Then go to Format Cells, choose Number -> Custom Format and set
      dd:hh:mm;@
      or
      dd"d." hh"h."mm"m.";@

  38. Thankyou so much for valuable information. It's working obsolutely fine.

  39. After Computing the formula i got Month 1 Total Extra Hours Worked A1= 17 hours, and computing month 2 total Extra hours Worked A2 = -5 Hours. Now please tell me the formula for getting 17 hrs (-) -5 hrs. How to get 12 hrs as answer.

  40. Thnaks so much

  41. I need to subtract dates and time where the numerator is MM/DD/YY HH:MM (military time, cell = O8) is in one cell and the denominator is in two separate cells one for date (cell = A8) the other for time (cell = W8).

  42. Very useful and helpful explanation and information.TQ

  43. I have 4 machines, which run for few hours in a shift,I note down the number of hours of breakdown/ idle time (In hours) for each machine individually. At the end i want to calculate run time of "all the machines combined" by subtracting the " SUM of idle times of all machines" from 32 hours.

    Please suggest a suitable formula/ technique for the same

  44. Hi I am trying to create a time sheet that displays elasped time between start time and end time. I have times that cross over 24 hours, where the end time is smaller than the start time. I have been using =mod(end time - start time,1). However this does not work when subtracting a smaller end time from a start time on another date. Is there one formula that I could use for all types of subtraction of time that would also capture the smaller end time - larger start time crossing over 24 hours?

  45. I am trying to show my kid when he improves in his swimming times. It is all good when he improves. Formula works great. But, if he did not improve the formula does not work. For example: last month it took him 00:36.55 (milliseconds) to swim 50 yards. This month it took him 00:35.70. So, he improved 00:00.85.
    But, if the numbers are opposite and he did worse the formula will not give me the negative number I am looking for. 00:35.70 - 00:36.85 = -00:00.85
    All cells are formatted mm:ss.00;@
    Is there a way to show a negative number in time when deducting?

  46. When I use custom format to represent times greater than 24 hours, the [h]:mm:ss displays 575:00:00.
    Why the addition of 551 hours to the total?

    1. Because the [h]:mm actually requires that you convert the total hours to DAYS.fraction. So take (calculated hours)/24. to display with [h]:mm format

  47. this is a time related query.
    when i do 23:59 - 23:30, i get an answer as 29 minutes formula=(b1-a1)*1440
    when i do 00:15 - 23:59, i get a negative with same formula, this is the problem.
    solution required = how do i get the number of minutes? which should be 16 minutes.
    Look forward to getting a solution, thanking everyone for their inputs!
    :)

  48. I have a question, If I have 01:40am and i want to minus 20:55:00pm what is the correct formula I need in order for it to see I have gone into the next day? I have tried and it will give me a -19... I need it to see that it is a 24hr period

  49. Hi, I had a similar problem working with time. For 24 hours Excel stores this as 1 day, 0 hours. My solution was to convert everything to seconds and work from there. If you have formatted as days, multiply by 86400 (seconds in a day), if you have formatted as hours, multiply by 3600 (seconds) in an hour, etc.

  50. Hi, i need help please. I have data that auto loads daily at 5pm. The process is broken into 2 parts (ETL1 and ETL2). ETL1 starts at 5pm till 11pm and ETL2 starts at 11 till 4 am. at 7 am i run a script to check if everything ran. each row has a start and end date time. i would like to flag all data after 5pm yesterday as todays data. Currently when i filter on today, i only see the rows where the date is after 00:00

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