Date Formatting in EXCEL

Date Formatting in EXCEL

0 Shares
0
0
0
0
0
0
0

We can display the same date in many different ways just by changing the date format.

Usually, when we insert a date in a cell it is displayed in the format dd/mm/yyyy or mm/dd/yyyy or as per your computer Regional Setting

  • The Date is the number of days.
  • All dates are stored as integers representing the number of days since January 1, 1900, which is stored as number 1, to December 31, 9999 stored as 2958465.
  • Times are stored in Excel as decimals, between .0 and .99999, that represent a proportion of the day where .0 is 00:00:00 and .99999 is 23:59:59.

A date has 3 parameters

  • d for the day
  • m for the month
  • y for the year
Date and Days Codes
d1Without 0  |  1-31
dd01With 0       |   01-31
dddSunSun-Sat
ddddSundaySunday-Saturday
Month Codes
m1Without 0  |   1-12
mm01With 0   |  01-12
mmmMarJan-Dec
mmmmMarchJanuary-December
mmmmmMJ-D
Year Codes
yy18 00-99
yyyy20181900-9999
Hour / Minutes / Seconds Codes
h0-23Hours
hh00-23
m0-59Mintues
mm00-59
s0-59Seconds
ss00-59

To format a cell Select the cell with a date in Excel, press Ctrl+1 to open the Format Cells window and switch to the date tab.

Date Formatting in EXCEL
Different examples of the display with the same date. Date Formatting in EXCEL

Here, you have Different examples of the display with the same date.

Date FormatResult
dd/mm/yy10/03/20
d/m/y10/3/20
dd/mm/yyyy10/03/2020
dd, mmm yyyy10, Mar 2020
dd, mmmm yyyy10, March 2020
yyyy-mm-dd2020-03-10
mmm-yyMar-20
mmm d, yyyyMar 10, 2020
ddddTuesday
dddd, d mmmm yyyyTuesday, 10 March 2020
d-mmmmm-yy10-M-20

Download Sample File

BUY Office 365 and Use in 5 Devices with 1TB Onedrive space