Date Functions

In Addition to Dynamic date handling Excel provides a wide range of Date related functions to enable us to manipulate dates as most extend.

• Date Function: This function used to compose date

Syntex: Date(year,month,day)
Example:
=date(2009,12,17)
composes: 17th December'2009

• you can also use DateValue function to convert String date into date

Syntex: DateValue("date")
Example:
=DateValue("2009/12/17")
Retunrs: 40164  (that is equals to 17th December'2009)

this value can be used further in any function as parameter.

Best part of this function is it flexibility that allows to enter the date in any format, As we take above example we could enter date "17/12/2009", "17 dec 09", "17-December-2009"

• Day:  This function returns the day from any date provided

Syntex: Day(date) or Day("mm/dd/yyyy")
Example: Day("12/01/2009")
Returns: 01

Note: Excel uses the System's date format by default it is 'M/d/yyyy' you can change this format using control panel - Regional Settings - Dates



Month: Like day function month returns the month from any date provided
Syntex: Month(date) or Month("mm/dd/yyyy")
Example: Month("12/01/2009")
Returns: 12

Year: Like day & Month functions year returns the year from any date provided

Syntex: Year(date) or Year("mm/dd/yyyy")
Example: Year("12/01/2009")
Returns: 2009

Weekday: Weekday function returns the Weekday that is falling on the date provided

Syntex: =Weekday(date)
Example: =Weekday("2009/12/17")
Returns 5 i.e. Thursday
begning from Sunday = 1, Monday = 2 and so on Saturday = 7

Today(): This  function returns the current date. And as the date is changed Excel automatically updates today function on next calculation to the prevailling date this way it will always returen the present date.

Value returned by this function is variable it changes as the date changes to insert constant current date  that doesn't change with change date use Keyboard Shortcut Ctrl + ;

Date.Diff(): this function calculates the differances in months & year between two dates.
Syntex: date.diff(date1,date2)
Example: =DATE.DIFF("10/10/2008",today())
Return: 1 year 2 months  (Assuming today is 17-December-2009)
This function does not calculates differances in days.

For Additional Details about Dates Visit: http://xlxpart.blogspot.com/2009/12/dates-in-excel.html