Dates in Excel

Handling Date methods in Excel is one of the very interesting matters. The Best part of it is you can perform calculation on it.

As Default Excel stores the dates in simple numbers and it is then displayed in Systems date format.

Default system’s format is MM/dd/yyyy but in excel you can change this format as per your convenience.

Likewise Enter any date in any cell

Right click – Format Cells

From Numbers Tab Click on Date and from given several options select any format of date as previewed.

Press OK then Cell’s format of date will be changed

Or you can define your own format. For doing this.

Instead of selecting date from Numbers tab Select Cutom

And enter

“dd/MM/yyyy” or “dd-MM-yyyy” (without Quotes) and see the cell format exactly same.

Now for understanding how to deal with format see the followings:

Date is comprises of three parts (Days, Months and Year) and additional to it a separator is used (by default these separators are “/” and “-“).

d: Represents Day in date
d: day will be displayed in single digit and if day is greater then 9 then only it will be display in tow digits (Like: 2,12)
dd: day will be displayed in two digits and if day is less then 9 then a leading Zero will be added (Like: 02,12)
ddd: days will be showed in weekday format (sun, mon, tue)
dddd: days will be showed in Complete weekday Name format (sunday, monday, tuesday)

m: Denotes Month
m: Month will be displayed in single digit and if Month is greater then 9 then only it will be display in tow digits (Like: 2,12)
mm: Month will be displayed in two digits and if Month is less then 9 then a leading Zero will be added (Like: 02,12)
mmm: Month will be showed in Short Month Name format (Jan, Feb, Mar)
mmmm: Month will be showed in Complete Month Name format (January, February, March)

and y finally denotes year
y: Year will be displayed in single digit and if Year is greater then 9 then only it will be display in tow digits (Like: 2,12)
yy: year will be displayed in two digits and if year is less then 9 then a leading Zero will be added (Like: 02,12)
yyy: Year will be showed in year with century format Like 2009, 2012, 1999

Normally System’s setting allows the default century format like if you type 11/11/25 then date will be 11/11/2025 and if you type 11/11/99 the date will be 11/11/1999, That is because of regional settings. Whenever any two digit year is entered system interprets it based on the system’s setting

To change the Date format and Two Digit year behavior you can go to Control Panel – Regional and Language Option – and Select date.

Change the behavior as per your requirements, But still be careful because if system’s default date format is changed then may some software not work or may behave unscientifically.

Calculation on Dates

As I already stated that calculation on any date is possible

For example Cell A1 has stored date 8th December – 2009 then

Use following formulas

=A1 + 1: 9th December - 2009

=A1 - 1: 7th December - 2009

Suppose you have some date in A2 (15/12/2009)

=A2 –A1 = 07/01/1900 Change it to Number format and you can find the differences of two dates i.e. 7.

You can divide, multiply, add or subtracts date from one another and after changing the cell format to numbers you can get the results.

You can also sue Date.Diff formula to obtain deference of date.

For Additional Details about Excel Date related function Visit:
http://xlxpart.blogspot.com/2009/12/date-functions.html