Comments on: Excel time format: 12/24 hour, custom, default

Microsoft Excel has a handful of time features and knowing them in depth can save you a lot of time. To leverage powerful time functions, it helps to know how Excel stores times. Continue reading →

Comments page 5. Total comments: 271

  1. i am trying to create a timesheet that will limit the amount of "time worked" in a cell to 12 hours, because everything over 12 hours is considered overtime i was fairly certain my formula would be =IF ((C1-B1, "h:mm:ss") *24) > 12 , 12 ,(C382-B382, "h:mm:ss") but i keep getting a #ERROR, i have tried multiple different variations and cant seem to get this to work. any suggestions?

  2. I need a small help that I am unable to convert hours into seconds as I have been trying 7:00/ 1440 for this I am getting a perfect answer but as coming to 6:30/1440 the calculator answer and this answer is not matching can you help me with this ?

  3. How can I convert days, hours, minutes, seconds to time format Hh:mm:ss with a formula? The data in excel was extracted from a database and not formatted as an actual time format.
    Example: Convert 4 days, 22 hours, 36 minutes, 58 seconds to 118:36:58

  4. hi madam,
    I have one doubt , i am key in excel my workers overtime day shift time in 6.00 out 19.30 i use formula =((19.30-06.00)-9) 4.30 , 9 meaning company hours the rest overtime this is good result , but night shift how to use formula, in 18.00 out 08.00 , please madam can you explain me formula.

  5. Hi Svetlana,
    Good day!
    Could you please let me know how to convert 1/2/1900 2:57:10 AM data format to HH:MM:SS. I have an excel full of different types of values (12:15:34 AM, 1/2/1900 2:57:10 AM). Any help would be appreciated. Thanks

  6. IF 17:30PM-9:00AM=8:30 HOURS OR 16:30PM-9:30AM=7HOURS THAN HOW TO I KNOW 31 DAYS CALCULATION IN EXCEL

  7. hi guys,

    i need to know in my scenario..... how to show the result when its get mature
    like... i have some checks due in next month which i want that when the date come, it automatically appear in my another maintained sheet.although, the date and all details with despcrition explained in first sheet but in other sheet i need their appearance
    so i need to know how these details appear at the time of maturity

    appreciate your response back

  8. Hi team, I want to enter exact time, using Ctrl+Shift+; but it omits seconds, only shows hours and minutes (in hh:mm:ss format, i.e., for example: 11:34:00 against the timing of 11:34:45) please help.

  9. Hi i would like to extract a data of the last 24 hours in my data base. which means i have to use intervals. i would like to use Now function to return me the last 24 hours data from now.
    these values will be updating hourly as the data base keeps changing.

    Ex. when i want to extract data for the last 30 days i use today function for the time interval.
    Start: today-32
    end: today
    this gives me 32 data and will always change the last day as time goes.

    i would like to use similar method for hourly data extraction using NOW Function.
    Start:
    End: NOW

    HOW CAN I GET THE STARTING TIME USING NOW'S FUNCTION AS I EXPLAINED WITH TODAY'S FUNCTION

  10. Have a column to check if timesheet add-up correct. Am getting a ##### error sometimes and othertimes get what 0:00 result when using formula: =IF(G21="","",G21-SUM(I21:P21))
    G21 is the sum of start, end, & breaks times. Columns I-P are the division of total work hours (G21). The problematic QC formula is the check if G21 and the sum of projects are equal & equal 0:00 hours. Note: All cells are in custom format: [h]:mm;@
    EXAMPLE:
    cell G21 formula: =IF(D20="","",($E20-$D20)-F20)
    cell G21 result: 8:30
    cells I21-P21 values: 0:30/1:00/1:30/0:30/1:00/1:30/2:00/0:30
    cell I21-P21 result: 8:30
    cell Q21 checking formula: =IF(G21="","",G21-SUM(I21:P21))
    Sometimes given Q21 result of 0:00 and other times #######

  11. I need to split date and time in two different cells from the below format
    2019-06-21 18.36.30

  12. Please can you tell me the easiest way to insert a static timestamp including seconds in a cell. If I use 'Ctrl + Shift + ;', I get say 23:35, but I need the seconds to appear too, e.g. 23:35:33. Please can you help? Thank you.

  13. I am doing a Work Project where we need to calculate minutes used for activity, we use "=(P9-O9)*1440" but when going 23:00-00:10 instead of 70 it gives us -1370 Any tips?

    1. Incase anyone comes across this same problem, you just have to add the Date before the time to solve it.

  14. Hi!!
    Im trying to use the =today()+1 formula, i need it to updated the date when i open the file but i need it to stay the same date (not update) at midnight

  15. I want to determine if the current time is between two preset times. Let's say I want to know if the current time is between 10:00 (in A1) and 11:00 (in A2). If it is currently 10:30 and I enter "=now()>A1", I get a return of "TRUE". But if I then enter "=now()<A2", I get "FALSE". Why is that?

  16. How do I add to this formula to start calculating time-sheets Where the first cell,first column is A1 (in time),second column is B1(out time) third column is C1(working hours), enter this in the first cell, fourth column:
    =IF(C1>9,9,C1)
    Nine is should be time format
    I need fourth column (normal working hours it should blow the nine) and fifth column(it is OT hours C1-D1)
    Noted all are Time format please help me....

  17. Hi, I am trying to calculate how much time it took someone to close a ticket. The report that I get out of Service pro shows the dates like this:

    Ticket # Time Logged Time Closed
    12/28/2018 1:50 PM 1/7/2019 8:23 AM

    I need to calculate the difference between the 2 time periods so that I can then average this amongst all of the tickets that were closed. Can anyone help me?

    1. Let's say 12/28/2018 is in A1 and 1/7/2019 is in A2 and the number of tickets (let's it is 400) is in A3. =((A2-A1)*24+INT((((A2-A1)*24)-INT((A2-A1)*24))*60))/A3 will give you 0.668875, which is the number of minutes per ticket. Multiplying that number by 60 gives you 40.1325 seconds per ticket, on average.

  18. I'm trying to convert the cells with a text format 5 days, 6 hours, 0 minutes and 0 seconds to
    custom format like d hh:mm:ss in order to complete an in cell calculation. How can this be done ?

  19. can anyone say me how i can convert 0:00 to 00:00 until to 9.

  20. Hi

    can you help me in speed data entry getting info by phone
    0730
    0835
    0955
    1045
    1515
    if my data entry like above i need this to convert to time or else need to put ":" in mid (I used left and right formula but i want the same cell value directly change to time format)
    is there any formatting or other option to support my requirement please.

    Thanks in advance

  21. hi i have 79 hrs 30 mins which is written as 1/3/1900 7:00:00 AM on the dialog box ....now i want to convert it into 79.30...
    how can i do this?? please help urgent

  22. Please guide me to get the difference between two time stamps in the following format
    20-09-2018 13:00:43 20-09-2018 15:30:31

  23. Hi I am having a doubt in excel, I am having a table with 4 columns in which two fields are date field, If i enter a value in first column the current date and time should be entered in the second column(date column), is there any way to be done for this.'Thanks in Advance

    1. Ram:
      Where the first cell, first column is A1, enter this in the first cell, second column:
      =IF(A1"",NOW(),"Empty")
      It says: If A1 is not empty enter current date, otherwise enter "Empty".
      Format the first cell, second column as DATE with the time included.
      You can enter any text you deem appropriate in place of "Empty".

  24. I am trying to edit a basic time card in Excel made by others. I put the time in: ie: Cell G:25, I want to write: 0800, and it fill in as 8:00, then Cell G:26 write 1300, and it fill 1:00, Cell G:27 write 1330, fill 1:30, Cell G:28 1630, fill 4:30. Then Cell G:29 totals how many hours I worked that day. 8.00. And finally in Cell M:29 a total of how many hours were worked that day.
    I apologize if I don't make sense, but any help would be amazing!
    Thank you.

  25. 7:46 7:46:00 AM
    1:05:32 1:05:32 AM
    CELL SHOWING 51:45:00 BUT INSIDE CELL 02-01-1900 3:45:00 AM

    THIS IS EXPORT SITUATION FROM WEBSITE REPORT ACTUALLY THIS IS MINUTES AND SECONDS 51:45

  26. Do I need formul to subtract time, like 88657:53 - 745563:32
    Or do I have to change time in to numbers?

  27. How do I add to this formula to start calculating time after a 24hr allotment.... I want to start calculating after the first 24hrs - HELP!!

    =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","")

  28. how can you get the time elapsed in h:mm from to date & times. example( 12/30/2017 18:16 / 1/2/2018 4:39. What is the time elapsed between these two?

  29. DOWN_DATE DOWN_TIME CLEARED_DATE CLEARED_TIME
    15.12.2017 5:27:04 15.12.2017 8:18:38
    14.12.2017 18:34:24 14.12.2017 20:56:45
    30.12.2017 12:17:42 30.12.2017 13:28:11
    13.12.2017 7:11:34 14.12.2017 8:53:06
    15.12.2017 5:27:14 15.12.2017 8:18:12

    how to calculate diffrence of down duration .in excel 13.12.2017 7:11:34 -14.12.2017 8:53:06 showing as 1/1/1900 1:41:32 AM .which is not correct .tell me correct formula pl

    1. Hello,

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

      =ROUNDDOWN(B1-A1,0) & " days " & TEXT(TIME(HOUR(B1-A1),MINUTE(B1-A1),SECOND(B1-A1)), "hh:mm:ss")

      Where cell A1 is “13.12.2017 7:11:34”, cell B1 is “14.12.2017 8:53:06”

      Hope this will help you!

  30. Hi guys. I need help please...
    I need to calculate the number of lates and undertime using this details
    B2 - date of time in/out
    C2 - time in/out
    E2 - work schedule in date
    F2 - time schedule
    Thanks guys hope for you immediate response.

    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 a coloum with time format - 1 h 10 m 16 s
    How can i convert to show 24:00:00.
    plz guid

  32. Hi - I'm trying to evaluate a planned timetable against the actual journey times. Generally straightforward (a simple a-b gives the right answer) but if my journey time took less time than scheduled (ie I'm planned to arrive at 10:15 but arrived at 10:05) how can I automatically calculate this to show a response of -00:10?

    Your guidance as ever...

    1. Hello, Tablespider,

      Please try the following formula:

      =IF(A1>=B1,A1-B1,"-"&HOUR(B1-A1)&IF(MINUTE(B1-A1)<10,":"&"0"&MINUTE(B1-A1),":"&MINUTE(B1-A1))&IF(SECOND(B1-A1)<10,IF(SECOND(B1-A1)=0,"",":"&"0"&SECOND(B1-A1)),":"&SECOND(B1-A1)))

      Hope it will help you.

  33. Hi,
    I have a column with Date and Time. I need to write a formula to show, if the date and time is 10-10-2017 12:53 PM, if time is after 2:30 PM the date to change to next date. Please advise.

    1. Hi, Austin,

      could you please specify where is that "next date" located? Should the formula just return the next date from the list or do you have an additional list?

  34. 145:59:38 i have a question how do I covert it to seconds.

  35. How can get a formula result of time blocks in my column to display as: 9.30am-10.00am
    10.00am-10.30am
    10.30am-11.am

  36. Hello All,
    I exported a data from the timekeeping software which exported in the following format as 07/10/2017 06:58, but we need to see the output coming as 7/10/2017 6:58:00 AM. The problem is
    1- The format function is not working on the cell as its an exported data for some reason it doesnot apply to this.

    The format does change to 7/10/2017 6:58:00 AM automatically on ONLY WHEN I click inside the cell and hit ENTER, but then we have to do this for thousands of cells one by one.

    I forgot the formula to convert this to decimal, if someone can help me with that would be a great help

  37. Hi, I have in a formatted the cell to display a date and time (27/01/2017 15:04). I would like a formula to calculate/conditional format to work out if the time is between 07:00 and 00:00

  38. I simply want to display AM or PM in a cell based on the current computer time, no time or date.

    I have tried =IF(A2<0.5,"AM","PM") it will show AM but doesn't change for me when the computer time changes and I refresh the spreadsheet.

    I've also tried the =NOW() and a custom time format of AM/PM with no time.

    This seems to be a simple problem but the correct solution has eluded me.

    Any one have suggestions?

    1. Use [$-F400]h:mm:ss AM/PM

      But you have to be on a 12 hr clock not 24 hr

  39. hi, i need help in excel time sheet.
    Example data available
    A = 15:55
    B = 20:25
    C = 30:30
    Total = 66:50 (by time formula)

    I need result in number
    A = 15.55
    B = 20.25
    C = 30.30
    Total = 66.10 (by number formula)

    Please help us

  40. Hi How to calculate 108 hrs post from 11/07/2017, 6:00, kindly share the formula.

    Regards,
    Sowmyashree

    1. Hi Sowmya,

      Thank you for your question.

      Please first select the cell with your data, click Format Cells -> More Number Formats -> Date and set format 7-11-17 6:00 AM. Then try the following formula:
      =A1+1/24*108

      Hope it helps.

  41. I have added daily overtime for the month in format hh:mm. Now I want to display separately Hours and Minutes.

    I can extract minutes from formula =Minutes(cell) but for hours greater than 24, it gives me wrong answer. for example, if the hours are 34, formula will minus 24 and give answer 10.

    Kindly advice.

  42. I hope someone can help. I have extracted time from an application. When I put it in excel it reads it as a text, that makes sense. Now, I am trying to convert back to time...it reads 126 as 1:26 - the time is actually 00:01:26...I have tried every trick I can think of. Can anybody help me figure this out?

  43. How i can get a current time with the seconds in the excel and also need to get current time in the below cells.

  44. Hi what excel formula would I need to enter to work out the How much overtime has beern worked between 09:00-12:00? From the example data

    A. B
    Start. End
    10:00 18:00
    18:00. 22:00

  45. I have two cells each with the MS Date and Time Picker Control 6.0 (SP4) in a different cell I want to return the difference in # of business days between the two selected dates.

    thanks in advance!

  46. Hi

    Can I extract hours from format HH:MM having more than 24 hours. For example, I summed-up overtime for month = 56:34, How can I extract "56" using hour function?

    Regards,
    Saad Kapadia

  47. Hi,

    i am having a problem with the date.
    Issue : i have a excel sheet i have exported it from a software. When is share this file from personal machine to my work machine the date changes from \ to - but when i send it to my client it works fine from same machine.
    i have changes the format locale to English(United States), now i am able to get the date as 14\02\2017 but the seconds in time stamp is missing in few cells
    for E.g date(in my machine)-14\02\2017 02:10:55
    in work machine -14\02\2017 02:10
    But in few cells the date is displayed as expected but not in few.
    Moreover, when i select the cell where ss is missing i am able to see date in formula bar as 14\02\2017 02:10:55 PM
    Will you be able to help me ? it's bit urgent

    1. Hello, Bharat,

      this may happen if the date and time are formatted differently on your computers. Please, take a closer look at this part of the article to make sure that you apply one and the same time format on your machines.

  48. Hello!

    I have formatted Cell to CUSTOM dd"d"hh:mm and getting results 04d04:04
    for 04 Days 04 HRS 04 MIN. 3.16997 Days which is exactly what i want.

    But considering the same formatting i am getting result 01d04:04
    for 00 Days 04 HRS 04 MIN instead of 00d04:04.

    Hope you understand my query, and please help me to get result 00d04:04 as per above explanation.

    Aizaz

  49. Hi.... please help me in using time (0,0,0) for more than 24 hours format. I mean the railway time.urgent

  50. Hi

    I looking for the calculation of date and time like

    01/03/16 4.15 PM to 01/03/17 5.15 PM

    Need Answer like this ways....

    Total Years Total Months Total Days Total Hours Total Min Total Sec

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