How to customize Date and Time format in Excel?

image_pdf

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,439 total views, 3 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>