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 6. Total comments: 271

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hello,
    I have taken static time format and found the time difference by =INT(F25-H25)&" Days "&HOUR(MOD(F25-H25,1))&" Hour "&MINUTE(MOD(F25-H25,1))&" Minutes" this formula.
    Now, I want to include only the working minutes and exclude the non working days( saturday,sunday and festivals etc) and non working hours (after 6.30pm to 9.30am). It will be very helpful to me if you could tell me the solution asap

  32. Hello, here is my example.
    A plane departs at 10/01/2017 01:00:00 and passengers come to gate 02:00hrs prior, which is the previous day 23:00:00.
    How to subtract those 2 hrs from cell containing 10/01/2017 01:00:00? Many thanks!

  33. I would like to track my improvement over time as I run. I would like to design an Excel spreadsheet that I can glance quickly at and see the number of days I have achieved, or nearly achieved, this goal by the color of the cell. Is it possible to design a spreadsheet which works like this?
    All of this would apply to each cell in a column thirty-one rows high:
    If time Entered is greater than or equal to 14:52:00, then shade cell red
    If time entered is between 13:52:00 and 14:51:00, then shade cell yellow
    If time entered is between 12:00:00 and 13:51:00, then shade cell green

    One of the major problems I have encountered doing this is that excel converts my minute:second input into time (for example, when I type 15:30, it corrects it to 3:30 PM). I really appreciate your help with this. I am fairly new to Excel and have learned a lot using the information on your site. John

  34. Hi,
    Pls suggest me how to count the current date, the date is with timestamp as well

  35. Hi,
    I am using the form data,and there are n number of rows, since i want to count the current date(today) responses. i have used the formula as below but it is not reflecting the result.
    =COUNTIFS('CCR-2017'!$C$3:$C,"="&TODAY(),'CCR-2017'!$E$3:$E,"GUNA")

    if the C:C column is with only date (Without time stampl) it will reflect the result. due the time stamp my formula not calculating the current date.

    Can you pls help me on this.

    Regards,
    Mahesh

  36. I'm trying to calculate time difference (start time - end time = total time) but also enter start/end times without having to type the colon. If I format the time so I don't type the colon my formulas won't work.

    I'm using this formula: =TEXT(+H2-E2,"00\:00")+(E2>H2) which works .... but ONLY if the seconds in my end time are larger than start. Otherwise, it calculates based on 100 versus 60 (for time).

    The time format I'm using so I don't have to type the colon is: 00\:00

    Is there a way to quickly enter times (w/o the colon) but have it calculate based on 60 minute increment.

  37. how could i use =NOW() , which shows time but it will not change/update automatically. when i'll use this then it will only shows insert time only. please help for it.

  38. How can i check if there is an update in excel file

  39. Hi Svetlana,

    Is there a way to format the cell to show GMT-0500 at the end of a date/time cell to indicate the time zone?

    Example: 2016-10-17 03:10:42 -0500 or 2016-10-17 03:10:42 GMT-0500

    Thanks,
    G

  40. i have to print a value in another column.The condition is if the time exceeds 12:00:00,add 1. Time is mentioned in H column and the data in which addition is to be done is in column G. The value to be returned is in column J.
    I am using formula "=if(G2>"12:00:00",sum(G2,1),G2)".This formula doesn't implement in some cases. How to do it?
    PS: Time is in 23:59:59 format.

  41. Hi - how do I set a conditional formatting that accounts for a potential date change due to the 24hr time? For example, if a therapist visits a patient on X date but at the end of the day 4pm, the date will change to the following day. Can you assist with the formula for this?

    I currently am using this formula: =IF($J$25="","",($J$25+139))+1 for date
    &
    this formula for time: =IF($W$25="","",($W$25-TIME(0,15,0)))

    Thanks!

  42. Hello-

    How can i have the time enter automatically when someone selects or clicks a name from a drop down list in a different column. Is that even possible?As opposed to entering time manually.For exmample lets say column B has a drop down list of people and i want the time to auto populate in column q with the start time when someone clicks a name in that cell(B2). Each person would have to manually or short cut the end time. Time can vary between people.

    B3 Q3
    Moe 10:30 PM
    Larry 10:45 PM
    Curly 10:57 PM

  43. I want set the all the values till end of the record to the previous day 11:59 pm how to I do that

  44. I have some time based trading data in seconds, but some of the seconds are excluded from the table since no trading happened then. How can I insert the missing seconds?

  45. In time 21.50 ( night 11.50) and closing time 7.00 am but time not calculate can u help me.

    no need negative value

  46. In time 21.50 ( night 11.50) and closing time 7.00 am but time not calculate can u help me.

  47. Hi,

    Just wondering how to get the correct duration (time elapsed), for example, between 23:00 and 00:05. I used simple subtraction but it returns negative result.

    thank you

    1. Hi Steven,

      I have just tried this:

      A1 is 23:00
      B1 is 00:05

      The formula =A1-B1 returns 22:55

      Do you get a different result in your Excel?

  48. Hello Sir,
    I need to represent data to show the jobs completion time on a given week.
    On 30th july, the job completed the next day ie 31st july at 12:43 am.
    since my x-asis has time frame of 0- 24 hours, the graph is not picking the next day completion time.

    Pl can you help me out to get the graph even though the job completed the next day
    thx

  49. How to convert DD:h:mm in to H:mm formate For Ex. 02(Day):01(Hours):10(Min.) to 49(Hours):10(Min) in excel.

  50. KAD6336 1 01/06/2024 13:49
    RVS2196 1 01/06/2025 20:39
    LAG3131 1 01/07/2015 21:59
    ADA2259 1 01/07/2016 03:28
    LAG0060 1 01/07/2016 05:15
    ADA2312 1 01/07/2016 09:06
    UW3274 1 01/07/2016 09:24
    LAG0127 1 01/07/2016 11:10.
    Second post
    KAD6336 1 06/24 13:49
    RVS2196 1 06/25 20:39
    LAG3131 1 07/15 21:59
    ADA2259 1 07/16 03:28
    LAG0060 1 07/16 05:15
    ADA2312 1 07/16 09:06
    UW3274 1 07/16 09:24
    LAG0127 1 07/16 11:10
    LAG3216 1 07/16 13:44
    LAG3114 2 07/16 13:18

    Second post is what i got when i copied from whatsapp and pasted on world while the first is what i got when i copied and pasted on excel. please how can i get the time format to appear with the same format when i copy from whatsapp. Note:this occurs when whatsapp message is sent to me from another organisation. i dont have such issue when it is sent from my organisation. how can i get the format of the first post as it is in the original message. thanks

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