When we type date or time in a cell, excel displays default date and time format. This default format is based on our date and time settings. (From Windows Control Panel)
Excel displays many date and time formats, most of which are not affected by Control Panel settings.
Format As you Type
Depending how you enter the Date/Time Excel will give an automatic format
|
Entry |
Show |
|---|---|
|
25/12 |
25-Dec |
|
25/12/12 |
25-12-2012 |
|
9:30 |
09:30 |
|
9:30 a |
9:30 AM |
|
9.30 p |
9:30 PM |
To understand How to use Date and Time format, read more about Date and Time format in Excel
Custom Date Format:
To customize date and time format, in format cells, click on Custom. In Type box, change the format as shown below
Result:
Let's understand what this mm/dd/yyyy means and how we can modify as per our requirement.
Here m is for month, d is for date and y is for year. Check below table to understand how this code will display date.
Days(d), month(m) and year(y)
|
Code |
Show |
|
|---|---|---|
|
m |
1,2,3 to 12 |
Month |
|
mm |
01,02,03 to 12 |
Leading Zeros |
|
mmm |
Jan |
First 3 characters |
|
mmmm |
January |
Whole month name |
|
mmmmm |
J |
First letter of the month |
|
d |
1,2,3 to 31 |
Day |
|
dd |
01,02,03 to 21 |
Leading Zeros |
|
ddd |
Sun |
First 3 characters |
|
dddd |
Sunday |
Whole day name |
|
yy |
12 |
Last 2 numbers of year |
|
yyyy |
2012 |
Whole year |
Custom Time Format:
Same way, you can setup custom format to display time as required
For example, selected cell is showing Date and Time.
You want to change format to see only time with12-hour format(use of AM/PM).
To do this, Right click and select Format cells.
In Custom format, select or type h:mm AM/PM in displayed text box.
Result: The time format has been changed and easy to read.
Tip: To quickly display date or time when both date and time is present in cell,(As in above example)
- Use shortcut CTRL+SHIFT+# to display date and
- Use Shortcut CTRL+SHIFT+@ to display time.
Check below table to understand how this code will display date.
Hours (h), minutes (m) and seconds(s)
|
Code |
Show |
“ |
|---|---|---|
|
H |
0,1,2 to 23 |
Single or double digit hours |
|
hh |
00,01,02 to 23 |
Double digit hours |
|
M |
0,1,2 to 59 |
Single or double digit minutes |
|
mm |
00,01,02 to 59 |
Double digit minutes |
|
S |
0,1,2 to 59 |
Single or double digit seconds |
|
ss |
00,01,02 to 59 |
Double digit seconds |
|
h AM/PM |
4 AM or 4 PM |
Shows hours with AM and PM notation |
|
h:mm AM/PM |
4.30 PM |
Shows hours and minutes eith AM and PM notation |
|
h:mm:ss A/P |
4.30.05 P |
Shows hours, minutes and seconds with A and P notation |
|
[h]:mm |
Elapsed time in hours;(more than 24 hrs) |
|
|
[mm]:ss |
Elapsed time in minutes; (more than 60 minutes) |
|
|
[ss] |
Elapsed time in seconds; (more than 60 seconds) |
If you use "m" immediately after the "h" (h:mm) or immediately before the "ss" (mm:ss), Excel displays minutes not month.
Tip: Use function "=Today()" to display Today's date and "=Now()" to display Current date and Time
1,567 total views, 1 views today