Can we define time 1

Format numbers as date or time values

When you type a date or time in a cell, it appears in a standard date and time format. This default format is based on the regional date and time settings specified in Control Panel and changes when you adjust these settings in Control Panel. You can display numbers in several other date and time formats, most of which are unaffected by Control Panel settings.

Content of this article

Display numbers as dates or times

You can format dates and times as you type. If you z. B. 2/2 in a cell, Excel automatically interprets this as a date and displays the 2. February in the cell. If you don't want this, e.g. B. if you prefer the February 2, 2009 or the 02.02.2009 display in the cell, you can choose a different date format in the Format Cells dialog box, as explained in the following procedure. If you 9:30 a or 9:30 p Enter in a cell, Excel interprets this as the time and shows 09:30 or 9:30 p.m. Again, you can display the time in the dialog box Cells customize format.

  1. Click the tab begin in the group number on the dialog box launcher next to number.

    You can also Press CTRL + 1, about that Cells dialog box format to open.

  2. click in the List Category date or Time.

  3. click in the Type list to the date or time format that you want to use.

    Note: Date and time formats that begin with an asterisk (*) respond to changes in the regional date and time settings specified in the Control Panel. Control Panel settings have no effect on formats without asterisks.

  4. To view dates and times in other language formats, click in the box Locale (position) the language you want to set.

    The number in the selection's active cell on the worksheet appears in the Sample box so that you can preview the number formatting options you selected.

Top of page

Create a custom date or time format

  1. Click the tab begin the dialog box launcher (next to number).

    You can also Press CTRL + 1, about that Cells dialog box format to open.

  2. click in the Category field date or Time,and then choose the number format that is closest to the format you want to create. (When creating custom number formats, it's easier to start with an existing format than to start from scratch.)

  3. Click in the field category on Custom. in The Field Type you should see the format code to match the date or time format you selected in step 3. The built-in date or time format cannot be changed or deleted, so you shouldn't worry about overwriting it.

  4. Take in the Make the required changes to the format in the Type field. You can use any of the codes in the following tables:

    Days, months and years

display

code

Months as 1-12

M.

Months as 01-12

MM

Months as Jan-Dec

MMM

Months as January-December

MMMM

Months with the first letter of the month

MMMMM

Days as 1-31

T

Days as 01-31

TT

Days as Sun-Sat

TTT

Days as Sunday-Saturday

TTTT

Years as 00-99

JJ

Years as 1900 - 9999

YYYY

If you use "m" immediately after the "h" or "hh" code, or immediately before the "ss" code, Excel displays minutes instead of the month.

Hours, minutes and seconds

display

code

Hours as 0-23

H

Hours as 00-23

hh

Minutes as 0–59

M.

Minutes as 00-59

MM

Seconds as 0–59

s

Seconds as 00–59

ss

Hours until 4:00 a.m.

h AM / PM

Time: 16:36

h: mm AM / PM

Time: 4:36:03 p

h: mm: ss A / P

Elapsed time in hours; Example: 25.02

[h]: mm

Elapsed time in minutes; Example: 63:46

[mm]: ss

Elapsed time in seconds

[ss]

Fractions of a second

h: mm: ss.00

AM and PM If the format includes an AM or PM, the hour is based on the 12-hour clock, with "AM" or "A" indicating times from midnight to noon and "PM" or "P" indicating times from noon to midnight. Otherwise, the hour is based on the 24-hour format. The code "m" or "mm" must appear immediately after the code "h" or "hh" or immediately before the code "ss". otherwise, Excel displays months instead of minutes.

Creating custom number formats can be tricky if you haven't already. For more information on creating custom number formats, see Create or Delete a Custom Number Format.

Top of page

Tips for viewing dates or times

  • To quickly use the standard date or time format, click the cell that contains the date or time, and then press CTRL + SHIFT + # or CTRL + SHIFT + @.

  • If a cell ##### after you've applied date or time formatting to it, the cell is likely not wide enough to display the data. To expand the column width, double-click the right boundary of the column that contains the cells. This will automatically adjust the size of the column to the number. You can also drag the right edge until the column is the size you want.

  • If you try to undo a date or time format by using in If you select General in the Category list, Excel displays a numeric code. When you re-enter a date or time, Excel displays the default date or time format. If you want to enter a specific date or time format, e.g. B. January 2010,you can format it as text by typing text in the list category choose.

  • If you want to quickly type the current date into your worksheet, highlight any blank cell, then press Ctrl +; (Semicolon) and press ENTER if necessary. To insert a date that updates to the current date every time you reopen a worksheet or recalculate a formula, type = TODAY () in a blank cell, and then press ENTER.

Do you need more help?

You can always ask an expert in the Excel Tech Community, get support from the Answers Community, or suggest a new feature or improvement on Excel User Voice.