Excel time format & how to use NOW and TIME functions to insert time

In this tutorial, you will learn the specificities of Excel time format as well as how to add a timestamp using shortcuts or insert an auto updatable time with the NOW function. You will also learn how to apply special Excel time functions to get hours, minutes or seconds from a time stamp.

Microsoft Excel has a number of helpful time features and knowing them a little in depth can save you a lot of time. Using special functions, you can insert the current date and time anywhere in a worksheet, convert time to a decimal number, sum different time units or calculate the elapsed time.

To be able to leverage powerful Excel time functions, it helps to know how Microsoft Excel stores times. So, before digging deeper into the formulas, let's invest a couple of minutes in learning the basics of the Excel time format.

Excel time format

If you have been following our Excel Dates tutorial, you know that Microsoft Excel stores dates as sequential numbers beginning with January 1, 1900, which is stored as number 1. Because Excel treats time as a portion of a day, times are stored as decimal fractions.

In Excel's internal system:

  • 00:00:00 is stored as 0.0
  • 23:59:59 is stored as 0.99999
  • 06:00 AM is 0.25
  • 12:00 PM is 0.5

When both a date and time are entered in a cell, they are stored as a decimal number comprised of an integer representing the date and a decimal portion representing the time. For example, 1 June 2015 9:30:00 AM is stored as 42156.3958333333.

How to get a decimal representing time in Excel

A quick way to pick a decimal number representing a certain time is to use the Format Cells dialog.

Simply select a cell containing the time and press Ctrl + 1 to open the Format Cells dialog box. On the Number tab, select General under Category, and you will see the decimal in the Sample box.
Get a decimal representing the time in a cell

Now, you can write down that number and click Cancel to close the window. Or, you can click the OK button and have the time replaced with a corresponding decimal number in the cell. In fact, you can think of it as the fastest, easiest and formula-free way to convert time to decimal in Excel. In the next part of our Excel Time tutorial, we'll take a closer look at special time functions and calculations to convert time to hours, minutes or seconds.

How to apply or change the time format in Excel

Microsoft Excel is smart enough to recognize a time as you type and format the cell accordingly. For example, if you type 20:30, or 8:30 PM, or even 8:30 p in a cell, Excel will interpret this as a time and display either 20:30 or 8:30 PM, depending on your default time format.

If you want to format some numbers as times or apply a different time format to existing time values, you can do this by using the Format Cells dialog, as demonstrated below.

  1. In an Excel sheet, select the cell(s) where you want to apply or change the time format.
  2. Open the Format Cells dialog either by pressing Ctrl + 1 or by clicking the Dialog Box Launcher icon next to Number in the Number group, on the Home tab.
    Click the Dialog Box Launcher to open the Format Cells dialog.
  3. On the Number tab, select Time from the Category list, and choose the desired time format from the Type list.
  4. Click OK to apply the selected time format and close the dialog box.
    Applying or changing the time format in Excel

Creating a custom Excel time format

Though Microsoft Excel provides a handful of different time formats, you may want to create your own one that fits the best for a particular sheet. To do this, open the Format Cells dialog box, select Custom from the Category list and type the time format you want to apply in the Type box.
Creating a custom time format in Excel
The custom time format you've created will be in the Type list the next time you need it.

Tip. The easiest way to create a custom time format in Excel is to use one of the existing formats as a starting point. For this, click Time in the Category list, and select one of the predefined formats under Type. After that switch to Custom and make the changes to the format displayed in the Type box.

When creating a custom time format in Excel, you can use the following codes.

Code Description Displays as
h Hours without a leading zero 0-23
hh Hours with a leading zero 00-23
m Minutes without a leading zero 0-59
mm Minutes with a leading zero 00-59
s Seconds without a leading zero 0-59
ss Seconds with a leading zero 00-59
AM/PM Periods of the day
(if omitted, 24-hour time format is used)
AM or PM
Tip. To create custom formatting for dates and time, use various combinations of time codes and date codes.

The following table provides a few example of how your Excel time formats may look like:

Format Displays as
h:mm:ss AM/PM 1:30:00 PM
h:mm 13:30
dddd, m/d/yy h:mm AM/PM Tuesday, 1/13/15 1:30 PM
ddd, mmmm dd, yyyy hh:mm:ss Tue, January 13, 2015 13:30:00

Custom formats for time intervals over 24 hours

When you are calculating times in Excel, a cell containing the sum of time amounts may exceed 24 hours. To get Microsoft Excel to display times beyond 24 hours correctly, apply one of the following custom time formats.

Format Displays as Explanation
[h]:mm 41:30 41 hours and 30 minutes
[h]:mm:ss 41:30:10 41 hours, 30 minutes and 10 seconds
[h] "hours", mm "minutes", ss "seconds" 40 hours, 30 minutes, 10 seconds
d h:mm:ss 1 17:30:10 1 day, 17 hours, 30 minutes and 10 seconds
d "day" h:mm:ss 1 day 17:30:10
d "day," h "hours," m "minutes and" s "seconds" 1 day, 17 hours, 30 minutes and 10 seconds

Custom formats for negative time values

The custom time formats discussed above work for positive values only. If the result of your calculations is a negative number formatted as time (e.g. when you subtract a bigger time amount from a smaller one), the result will be displayed as #####. If you want to format negative time values differently, the following options are available to you:

  • Display an empty cell for negative times. Type a semicolon at the end of the time format, for example [h]:mm;
  • Display an error message . Type a semicolon at the end of the time format, and then type a message in quotation marks, e.g. [h]:mm;"Negative time"

Generally speaking, a semicolon acts as a delimiter to separate positive values' format from negative values' formatting.

If you want to display negative times as negative values, e.g. -10:30, the easiest way is to change the Excel's Date System to 1904 date system. To do this, click File > Options > Advanced, scroll down to When calculating this workbook section and check Use 1904 date system box.

The default time format in Excel

When changing the time format in the Format Cells dialog, you may have noticed that one of the formats begins with an asterisk (*). This is the default time format in your Excel.

The default time format in Excel

To quickly apply the default Excel time format to the selected cell or a range of cells, click the drop-down arrow in the Number group, on the Home tab, and select Time.

A quick way to apply the default time format in Excel

To change the default time format, go to the Control Panel and click Region and Language. If in your Control panel opens in Category view, click Clock, Language, and Region > Region and Language > Change the date, time, or number format.

Changing the default time format

Note. When creating a new Excel time format or modifying an existing one, please remember that regardless of how you've chosen to display time in a cell, Excel always internally stores times the same way - as decimal numbers.

How to insert time and a timestamp in Excel

There are a numbers of ways to insert time in Excel, which one to use depends on whether you want a static timestamp or a dynamic value that updates automatically to reflect the current time.

Add time stamp using shortcuts

If you are looking for a way to insert a timestamp in an Excel cell, i.e. a static value that won't automatically update whenever a workbook is recalculated, then use one of the following shortcuts:

  • To insert the current time, press Ctrl + Shift + ;
  • To enter the current date and time, press Ctrl + ; which inserts a date, then press the Space key, and then hit Ctrl + Shift + ; to insert the current time.

Inset a timestamp in Excel using shortcuts

Add today's date and current time using NOW function

If you aim to insert the current date and time as a dynamic value that refreshes automatically, then use the Excel NOW function.

The formula is as simple as it can possibly be, no arguments are required:


When using the NOW function in Excel, there are a few things to keep in mind:

  • The NOW function retrieves time from your computer's system clock.
  • NOW is one of Excel's volatile functions that cause the cell with the formula to recalculate every time the worksheet is re-opened or recalculated.
  • To force the Excel NOW function to update the returned value, press either Shift + F9 to recalculate the active worksheet or F9 to recalculate all open workbooks.
  • To get the NOW function to automatically update at a specified time interval, add a VBA macro to your workbook, a few examples are available here.

Insert current time as a dynamic value

If you'd rather insert only the current time in a cell without a date, you have the following choices:

  1. Use =NOW() formula, and then apply the time format to a cell(s).

    Please remember, this will only change the display format, the actual value stored in a cell will still be a decimal number consisting of an integer representing the date and a fractional part representing the time.

  2. Use the following formula:

    =NOW() - INT(NOW())

    The INT function is used to round the decimal number returned by NOW() down to the nearest integer. And then, you subtract the integer part representing today's date to output only the fractional part that represents the current time.

    Since the formula returns a decimal, you will need to apply the time format to the cell to make the value display as time.

The following screenshot demonstrates both formulas in action. Pay attention that although the formatted time values look the same (column C), the actual values stored in cells (column D) are different - D4 contains only the fractional part:

Inserting the current time as a dynamic value

Insert time using the Excel TIME function

The TIME function in Excel is used to convert a text string showing a time into a decimal that represents the time.

The syntax of the Excel TIME function is very straightforward:

=TIME(hour, minute, second)

The hour, minute and second arguments can be supplied as numbers from 0 to 32767.

  • If hour is greater than 23, it is divided by 24 and the remainder is taken as the hour value.

    For example, TIME(30, 0, 0) equates to TIME(6,0,0), which is 0.25 or 6:00 AM.

  • If minute is greater than 59, it is converted to hours and minutes. And if second is greater than 59, it is converted to hours, minutes, and seconds.

    For example, TIME(0, 930, 0) is converted to TIME(15, 30, 0), which is 0.645833333 or 15:30.

The Excel TIME function is useful when it comes to merging individual values into a single time value, for example values in other cells or returned by other Excel functions.

Inserting time using the Excel TIME function

How to get hours, minutes and seconds from a timestamp

To extract time units from a time stamp, you can use the following Excel time functions:

HOUR(serial_number) - returns an hour of a time value, as an integer from 0 (12:00 am) to 23 (11:00 pm).

MINUTE(serial_number) - gets the minutes of a time value, as integers from 0 to 59.

SECOND(serial_number) - returns the seconds of a time value, as integers from 0 to 59.

In all three functions, you can input times as text strings enclosed in double quotes (for example, "6:00 AM"), as decimal numbers (e.g. 0.25 that represents 6:00 AM), or as results of other functions. A few formula examples follow below.

  • =HOUR(A2) - returns the hours of the timestamp in cell A1.
  • =MINUTE(A2) - returns the minutes of the timestamp in cell A1.
  • =SECOND(A2) - returns the seconds of the timestamp in cell A1.
  • =HOUR(NOW()) - returns the current hour.

Getting hours, minutes and seconds from a timestamp

Now that you've got down to brass tacks of the Excel time format and time functions, it will be much easier for you to manipulate dates and times in your worksheets. In the next part of our Excel Time tutorial, we are going to discuss different ways of converting time to numbers. I thank you for reading and hope to see you on our blog next week!

159 responses to "Excel time format & how to use NOW and TIME functions to insert time"

  1. anant says:

    please time 48 hours out put

  2. anant says:

    48 hours example

  3. Ali_Munaz says:

    I want the negetive time to show as ZERO when subtracting the time..
    23:40 - 23:30 = ##### instead of that i want it to be 00:00

  4. Zlatan Vukic says:

    Hi Svetlana, is there any wy I could insert the time greater than 24h (80 hours for example) using TIME(hh;mm;ss) function? Or is there any solution to compare the time... Ig the time is greather or equal to 80h?

  5. JRK PRASAD says:


    My query is,I will have to maintain In time and out time register for our staff to monitor overtime of work for which "I PREPARE FOUR COLUMNS, NAME, FATHER NAME DESIGNATION, GENDER IN TIME AND OUT TIME COLUMN, When I enter in time value, I need the out time value should be fill by counting 9 Hrs. from the time of In time Entry.

    Pleas advice me Sir.


    JRK Prasad

  6. ahmed says:


    could you please help me ?
    I have cell contain date like (03/01/2015)
    and I have cell contain time (11:34:01)

    how can I put them in one cell to become (03/01/2015 11:34:01)

    could you please give me the format

    from Iraq/ basra
    petroleum engineer

  7. miquey says:

    type '03/01/2015 on cell A1(or on any cell) and '11:34:01 A2(or on any cell).
    Then on any cell type =concatenate(a1," ",a2)
    typing apostrophe (') sign before any numerical value or any special character displays it as it is.

  8. SURESH says:

    I want to calculate over time of employees. Would you like to help me in this regards as 10:30PM-10:00AM= ?

    • MISAB says:


    • Harish Koduri says:

      A B C D E
      Working Starting Time Working Ending Time Actual Work hours Worked hours Over time
      10:30 AM 10:00 PM 9:00:00 11:30:00 2:30:00
      =C1-B1 =E1-D1

      All Cell Format should be : Format cells (Cntrl + Shift + F) > Number Tab > Time > Selecet "HH:MM:SS" I can be 7th one in list

  9. Shadows says:

    My time format is set to HH:MM:SS in excel but, it shows as H:MM:SS in spreadsheet, ie: 1:15.00; I want it as 01:15:00. Please help.

  10. John says:

    Whilst certainly not commonly used... I'm trying to keep my spreadsheets presentable and not confusing.

    Cell Formats for Time:

    Within the accounting function/format; if the cell = 0.00, -
    How do I get excel to do the same with time; if the cell = ([h]:mm) 0:00, - or "blank"

    You help is appreciated

    • John says:

      Hi All,

      If you've seen this and started to play with it, thanks for your attempt... I have found my solution by playing:

      [h]:mm;;_-* "-"??_-;_-@_-

      Kindest Regards

  11. Erik Viking says:

    I can't get this formula to work: =TEXT(TIME(HOUR(A1);MINUTE(A1);0);"hh:mm")
    Regardless of how I write the formula or format the cell, the result shows 'hh:01' and not the time given in the source cell, which itself contains a formula. The source cell lists both date and time, and I'm trying to extract the time. The file is an .xls-file, not sure whether that has an impact. I've run out of ideas of how to solve it, does anyone have a good solution?
    Kind regards

    • shabista shekh says:

      please tell me
      16/11/2016 10:03:40 AM
      -16/11/2016 11:08:08 AM
      show me hours ..

      • Anshu Giri says:

        Just type NOW()-INT(NOW())in Cell A1
        Then extract hours, mintues & seconds details from Cell A1
        I hope, it would serve as a solution to your issue & I'm not able to attach screenshot of the whole demonstration

  12. Naveen says:


    How do i convert 53212 mins into hh:mm:ss format i used the following to get hh:mm but im not getting SS please guide me in this behalf

    Tpe1 =TEXT(FLOOR(A1/60,1),"00")&":"&TEXT(MOD(A1,60),"00")

    Type2 =INT(A1/60)&":"&TEXT(MOD(A1,60),"00")

  13. Lakshmanakumar says:


    how to use Sum if for time units sum ??

    i have the list of time units for a month for more staffs ! how to get sum of Time units for staffs ! (Using Sum if)

  14. sam says:

    I have cells that time within for a schedule.
    ex: 0800-0830
    next cell underneath 0830-0900.

    I want t update the time in the 1st cell, is there a way for the time in the cell underneath to automatically update or to I have to go into each cell to update the times? thank you

  15. Damon says:

    I am trying to calculating elapsed time in hours but I need to account for end times that are blank and to count those times as current time or newtwork hours.

  16. Danny says:

    I want to find hours left on a project ,so I have hours worked and hours billed =hours left ,how do i create a formular

  17. Ifeanyi says:

    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

  18. Sachin Mane says:

    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.

  19. Balaji says:

    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

  20. steven says:


    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

  21. chandru ramachandran says:

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

  22. chandru ramachandran says:

    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

  23. Lenny says:

    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?

  24. Mike says:

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

  25. cez says:


    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

  26. Debbie says:

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


  27. noor says:

    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.

  28. G says:

    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


  29. Samsad CV says:

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

  30. EMMR says:

    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.

  31. Jo says:

    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.

  32. Mahesh says:

    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.

    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.


  33. Mahesh says:

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

  34. John says:

    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

  35. Lyuba says:

    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!

  36. Kuldeep says:

    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

  37. Nandha says:


    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

  38. Vasanth k says:

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

  39. Aizaz says:


    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.


  40. Bharat says:


    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

  41. Saad Kapadia says:


    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?

    Saad Kapadia

  42. Jason says:

    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!

  43. Mits says:

    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

  44. Logesh says:

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

  45. Dawn says:

    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?

  46. Saad Kapadia says:

    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.

  47. Sowmya says:

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


    • Mary Trifuntova (Ablebits.com Team) says:

      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:

      Hope it helps.

  48. zaki says:

    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

  49. Dan Moore says:

    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?

  50. Louise says:

    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

  51. sushmit roy says:

    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

  52. Tonka Morris says:

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

  53. Susy says:

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

  54. Austin says:

    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.

  55. Tablespider says:

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

  56. Prashant says:

    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

  57. alpine says:

    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.

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

  58. shathish says:

    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

    • 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!

  59. Ernie says:

    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?

  60. Sonny says:

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

  61. Sys says:

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

  62. khurram says:

    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


  63. Cortni Jo says:

    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.

  64. Ram Prakash says:

    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

    • Doug says:

      Where the first cell, first column is A1, enter this in the first cell, second column:
      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".

  65. Krishna says:

    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

  66. rishi jhoomuck says:

    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

  67. sureshraj says:


    can you help me in speed data entry getting info by phone
    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

  68. saleh says:

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

  69. Roland Bharara says:

    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 ?

  70. Shanna says:

    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?

    • William Schenck says:

      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.

  71. P.Kajan says:

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

  72. William Schenck says:

    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?

  73. Adam says:

    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

  74. David Ellis says:

    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?

    • David Ellis says:

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

  75. Les says:

    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.

  76. Sreejith says:

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

  77. Rach says:

    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;@
    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 #######

  78. Juvencio says:

    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.
    End: NOW


  79. moody says:

    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.

  80. Usman says:

    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



  82. Kundan Kumar says:

    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

  83. janardhan rao says:

    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.

  84. Steph says:

    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

  85. lakshman vivek says:

    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 ?

  86. DBUB says:

    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?

  87. Eunice says:

    How can I just display time instead of date & time in the cell. As I need 22:00 but the cell will show 14/05/1903 10:00:00pm.

  88. karthikeyan says:


    I've a doubt, i want to compare two columns in Time format and highlight the one that exceeded. I can only able to compare it, but I would like to know how to highlight it?

    For example:
    Shift time Login Time Status
    2:30 2:25 Met
    2:30 2:29 Met
    2:30 2:45 NotMet
    2:30 2:15 Met
    2:30 3:15 NotMet
    2:30 2:50 NotMet
    I can compare whether the condition is met or not, but how to highlight the cells in 2nd column automatically.

  89. Al says:

    I am trying to input hours in one column for example input and subtract
    in one column i have 24:13 minutes to be deducted from 113:45 minutes when i input 24:13 it changes to 0:13 also the current formula i use is =(C3-B4) where C3 is 113:45 and B4 is 24:13


  90. Arya says:

    Hi, I want to record only time of a data entry is it possible to do so?

  91. Dave Thomas says:

    Is there any format that will make my time inputs in PM only. SO if I type in 5:00 it will automatically pick it up as PM and not AM.

  92. Alex Lee says:

    I have custom formatted cells with 00/:00 enable simple entry just directly key in number for instant 830 will display 8:30am,but when come to calculating the duration it display hh:mm, may I know how can I get it converted to single minute unit please.

  93. Bharath says:

    Any one please help!
    i need to assign formula, if employee works after 9:00pm i need give one food allowance.

  94. Seleko says:

    I would like to record 24h00 in Excel 2013

  95. Mary Beth Russo says:

    I want to add up hours for an employee and then multiply those hours by her rate to get total cost spent. How can I do this if the hour is in this format hh:mm:ss? The formula needs a whole number like 1.75 rather than 1:45.

  96. Mary Beth Russo says:

    Can Excel actually convert the time from 1:45 to 1.75 through a formula?

  97. Mary Beth Russo says:

    Can Excel actually figure out the time of 1:75 is actually 135 minutes?

  98. Gabriel Igne says:

    Hi how could i calculate the total time lapse i tried =sum(f2:f4) but its not working

  99. BIJON SARKAR says:

    Hi how could i calculate the Night duty hrs which is inside the total hours & btn 22 to 06
    Id of Crew|Name |In |Out |D.Hrs|Night D.Hrs
    N10001 |AAA |05-06-2020 16:00|05-06-2020 23:00|07:00|01:00 (btn 22 to 06)
    N10002 |BBB |06-06-2020 23:00|07-06-2020 08:00|09:00|08:00 (btn 22 to 06)
    N10006 |XYX |08-06-2020 03:00|09-06-2020 23:30|20:30|03:00 (btn 22 to 06)
    N10010 |LMN |08-06-2020 21:00|08-06-2020 23:45|02:45|01:45 (btn 22 to 06)

  100. Khalid M. Fituri says:

    I have sum all my employee working house with =SUM(C2:C26), I got a total of 810:07 hours (format [h]:mm) When I tried to change the formatting to
    dd "day," h: "hours," mm "minutes. I got 2 days, 18 hours 07 minutes which is wrong! How to fix that?

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