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

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.

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.

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.

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.

- In an Excel sheet, select the cell(s) where you want to apply or change the time format.
- 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.

- On the
*Number*tab, select**Time**from the*Category list*, and choose the desired time format from the*Type*list. - Click OK to apply the selected time format and close the dialog box.

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.

The custom time format you've created will be in the **Type** list the next time you need it.

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 |

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 |

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 |

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.

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.

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

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

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.

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.

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:

`=NOW()`

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.

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

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

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

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.

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.

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!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

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

I am entering 10.40 and the cell is changing it to 9.36 am automatically, please share why?

Hello!

Date is stored in Excel as a number. 10.40 is 1/10/1900 09:36:00. To get the time, you need to enter it correctly. Use a colon. (9:36:00). I recommend reading this article.

How can I do 23:50 - 00:00? For example if its 23:50 on a tuesday then 00:00 on a wednesday?

Hello Rebecca!

If I understand your task correctly, if the time difference is negative, add 1 day (24 hours)

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

To the start time you need to add 1 day.

I hope it’ll be helpful.

Hello - Is there a source that shows how to create a formula that calculates the "duration" it takes someone to do something?

Ex. All I have are "start time" in one column, and in the next "end time".

Example, one column says the time that Jeff texted me = 07/10/2020 at 12:00 p.m.

In the other column, it shows my response time to Jeff being the next day on 07/11/2020 at 06:00 p.m.

How can I create a formula that would automatically give me "30 hours" - since the response time took thirty hours?

Hello Isabella!

Subtract the start time from the end time. Then apply a custom time format. To be able to show more than 24 hours in a cell, please use this time format: "37:30:55". You can find it in the list of Excel time formats.

Hello i wanna do a time format that counts down days till expiration and the way its set up is there is the date format in each row is it possible to make 1 date show and each column follow that date with multiple items and different expiration dates?

216:00:00 212:16:00 -3:44 ok

but

same formula

216:00:00 182:19:00 -9:41 wrong -33:41

=IF(H7-G7>0, H7-G7, TEXT(ABS(H7-G7),"-h:mm"))

total duty Time - total Work Time

216:00:00 212:16:00 -3:44 this answer ok

but

i use same formula

216:00:00 182:19:00 -9:41 this answer wrong

why

this answer -33:41

2020-08-18T9:00:00+07:00

How to create custom formula for this in excel?

Hello,

I'm running into a problem where using the Format properties to change a time value from 24hr to 12 hr is not working. I have to dbl-click in the cell and hit enter for the format change to occur.

This is an example of the value in the cell: 17:00:00. It will not change to 5:00 pm unless I edit the cell and hit Enter.

I've tried to use the Calculate Now, Calculate Sheet operations under the Formulas menu but nothing changes the time values.

Help me, able-won-kenobi!

Thank you!

Hello!

Unfortunately, I was unable to reproduce your problem on my own. When you change the format, the appearance of the cell changes immediately. How do you change the format? Could you please describe it in more detail? It’ll help me understand it better and find a solution for you.

That's the problem, the appearance of the cell DOES NOT change immediately.

The only way it changes is if I edit the cell and hit Enter. Then it changes to the desired format.

I changed the format by highlighting the two columns of Time data, right click, select Format Cells ... and change to the desired format Time "1:30 PM".

That's it. Very annoying.

Hello!

There is probably an error with the data formats in your file. Perhaps this method will help. Copy the date column to Notepad. Then copy the data from the Notepad and paste it in its original place. Apply the format you want.

just go in data tab and refresh

Please help

two different date and time need difference of both date

for eg

12/12/2020 12:00 PM - 12/15/2020 17:00 PM = Difference time () in text format

hello

in excel i am using Now function to enter the current date and time.

But i want to fix it.

How i will do that?

please help me.

Hello!

Pay attention to this instruction: How to insert today date & current time as unchangeable time stamp

I hope I answered your question.

how to record share price rate change per minute in Live trade market

क्रमांक दिनांक नाम

12755414 12/02/2020 8:37 सुशिल कुमार

12993741 01/01/2021 17:36 मुन्नु लाल

13010945 01/04/2021 10:04 बाला

13081668 1/13/2021 12:39:49 PM मनोहर

13082304 1/13/2021 1:40:49 PM नितीश

13103407 1/16/2021 11:27:15 AM जितेश

13108341 1/16/2021 7:52:29 PM अनिल

13109480 1/17/2021 8:20:01 AM दीपक

How to distinct date and time in excel from formula

Hi!

I am not sure I fully understand what you mean. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the expected result.

It’ll help me understand it better and find a solution for you.

Use function "Text to Column" to separate these values in different columns

Hi

Hoping someone can help.

I have a time of day for example 09:00 but want it to be displayed as the hour it’s in I.e 09:00-10:00 is there some way to do this quickly please?

Hello!

Here is the formula that should work perfectly for you:

=TEXT(A1,"hh:mm")&" - "&TEXT((A1+TIME(1,0,0)),"hh:mm")

I hope it’ll be helpful.

I was using a spread sheet that required a time on every line. it was programmed to use ` in place of :

for instance, I could type 7`32 and the time 7:32 AM would appear.

How do i recreate this?

Helllo!

You may have used Auto Correct Options (Excel options - Proofing). You can set up automatic replacement for ` to :

I hope this will help.

how to record share price change per minute in Live trade market

Hello,

I need a formula that counts any time from a previous or current day as a full day.

Ex.

1/2/21 13:30 1/3/21 0700 2 full days

1/2/21 13:30 1/2/21 1400 1 full day

This is what I get:

1/2/21 13:30 1/3/21 0700 0

1/2/21 13:30 1/2/21 1400 0

Hello!

If I got you right, the formula below will help you with your task:

=ROUNDUP(B1,0) - ROUNDDOWN(A1,0)

I hope my advice will help you solve your task.

I want to calculate the duration over multiple days without having a date in the formula.

for example

C1 D1 E1

Start time End Time Duration

22:00

Hi,

Your task is not completely clear to me. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you.

I want to calculate the duration over multiple days without having a date in the formula.

for example

C1/ D1/ E1

Start time / End Time / Duration (what i want to work)

22:00 / 02:30 / 04:30 (calculated total)

I have also applied the 0/:00 Formula to the whole sheet and I tend to get totals of 0:00 now.

Hello!

If I understand your problem correctly, then this topic has already been discussed many times on the blog. For example, read here.

I WANT THAT EMPLOYEE WILL ENTER VALUE IN CELL B2 THEN IN C2 THEN IN D2 THEN IN E2 CELL.

THEN EMPLOEE WILL SAVE ENTRY. DATE AND TIME SHOULD AUTOMATICALLY APPEAR IN CELL A2.

HOW CAN I DO IT?

Hi, i would like to calculate the 'Total Hours' from the 'Start Time" and 'End Time" and afterwards i would want to find out the man-hours from the 'No. of Workers' but in Hours format (2.5h and etc)

Please help me with the format. Thanks!

Example:

No. of Workers Start Time End Time Task Duration Hours

2 09:00 11:00 02:00 4

Hello!

To convert time to decimal use the formula

=HOUR(A1)+MINUTE(A1)/60

Hope this is what you need.

How do I compute running hours, supposed from 06:00 AM to 09:00 AM? the result should show 3 Hours but it always shows error when I followed the instructions here. Thanks!

Hello!

For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred.

How to get a time using Number 0 and 1, when we type 1 in C column then D Column should reflect the system time and in case if it is 0 in C Column then D Column should reflect blank.

For example- If Column C1 is '0' then D1 should shows Blank similarly if Column C1 is '1' then D1 should show the system time.

Hi,

If I got you right, the formula below will help you with your task:

=IF(C1=0,"",NOW())

I hope this will help

it's ok , but have 1 problem . i input A1 columm 0>1 then show current time A2 columm and another day input B1 columm 0>1 then show A2 and B2 sametime.

Hi,

I didn't quite understand your problem, but maybe you will find this answer useful in our blog.

hi, I need an Exel formula to give the time "now" in A4 when an Input is made in A1

eg:

A1 A2 A3 A4

trailer No Dock Dist Time

Hi,

This comment answers your question.

Sample

London Dubai Bangkok Auckland

Mon 2 Feb 12:00 Mon 2 Feb 16:00 Mon 2 Feb 19:00 Tue 3 Feb 01:00

Scenario: I have an Excel file and it contains a future event date / time in London.

I would like that date / time to change dynamically, returning the correct date / time in wherever I open the file.

Can I use a formula which checks the time from the computer zone setting e.g. =NOW() and then add or subtract that local time from the given London time so that the future event time is correct for that specific location? Thanks.

Dennis

Hello!

For each time zone, you need to change your computer's system time to the desired number of hours.

Here is the article that may be helpful to you: How to add or subtract hours to time in Excel

I have a cell in the time format hh:mm:ss (eg. 0:10:00). I want it to just show the minute i.e. 10 minutes for the example. To follow up if it is 0 minutes, can it display 0 instead of 00?

Hello!

In time format, either hours or seconds must be shown along with minutes.

You can use the "hh:mm" or "mm:ss" format.

The "mm" format will show not minutes, but months.

I would like to convert start and stop times to total minutes, ex. 17:46 to 20:00 is 2:14, but I would to display as 134 minutes. Help is appreciated.

Hi,

To express the time in minutes, multiply by 1440.

=A1*1440

Hope this is what you need.

I need to take this Timestamp and turn it into the proper date and time, hour, min, sec.

1614828584677 = ?

Hello!

Your question is not entirely clear, please specify.

What result do you want to get from your number?

HI Team,

I am in need of Time calculation of my working hours on a specific task, My working hours is from MONDAY to FRIDAY ( 8 AM to 5 PM ), Eg: I got a task on Friday opened date of task 30/04/2021 17:18:00 and closed date of task is 03/05/2021 08:17:00 , As per human calculation as per my working hours it should be 17 minutes, But could any one guide me with Formula for this calculation

Hello,

I would like excel to calculate non numeric cells in increments of 10 and when it reaches 60 return 1 hour.

I am creating a break list that goes in 10 minute increments with 20 or 30 minute breaks. I need excel to count each cell as 10 minutes and when it reaches 60 minutes count 1 hour, if there are breaks longer than 30 minutes, I want them deducted from the total working time. I do not know how to convert the result to hours. =((COUNTA(D2:CI2)*0.1)-(COUNTIF(D2:CI2,"R")*0.1)) - this is the formula I have used, I need to know how to convert the result of this formula to time in accumulating hours, or if there's a better formula I ca use for my calculation.

Hello!

If you want to count the number of hours as a decimal number try this formula:

=((COUNTA(D2:CI2)*10/60)-(COUNTIF(D2:CI2,"R")*10/60))

I hope it’ll be helpful.

Good day Alexander,

Thanks a million, the formula worked beautifully, doing exactly what I need.

8:23:27 PM

9:00:29 PM

7:18:52 PM

11:02:54 PM

7:08:30 PM

5:22:12 PM

8:00:54 PM

9:03:58 PM

7:27:46 PM

7:54:33 PM

4:00:27 PM

3:30:37 PM

9:33:02 PM

4:20:26 PM

how to change the time min only

Hi!

I’m sorry but your task is not entirely clear to me. What result do you want to get exactly?

Hello, I have C1 as minutes and E1 as a total count with F1 as =SUM(C1/E1) to give me count per minute. That count per minute is in decimal form. How can i have F1 as a decimal and have it equal G1 in time. For Example

C1-1

E1-2

F1-.5

G1 .30

Hello!

If I understand your task correctly, to convert decimal minutes to seconds, multiply by 60. If you want to write seconds as a decimal fraction, divide by 100.

0.5*60=30 30/100=0.30

Thanks Alexander. The issue im running into is if you flip it to

C1-3

E1-2

then F1(C1/E1)= 1.5 in excel

but i need a formula in a seperate cell for that 1.5(decimal) to show 1.3(time)

Hello!

You can use this formula:

=TIME(0,C1,0)/E1

Use custom time format "m.ss"

I want a formula that will get minus 8 hours to my current time

Example a1 my time is 00:40, i want in b1 it will show what it will be if i subtract 8 hours from a1

Hi!

I recommend reading this guide: How to calculate time in Excel - time difference, adding / subtracting times.

Pay attention to paragraph — How to add or subtract hours to time in Excel.

=IF(A1-TIME(8,0,0)<0,A1-TIME(8,0,0)+1,A1-TIME(8,0,0))