હિમાંશુ પરભુભાઇ મિસ્ત્રી | Himanshu Mistry

July 30, 2008

Date Functions in MS Excel

Filed under: Useful Links — Himanshubhai Mistry @ 9:11 pm

I found good information about Date related functions available in Microsoft Excel, from www.officearticles.com  website

Here it goes:

Today’s date and time:
=NOW()
Today’s date only:
=TODAY()

Day of the month for a specific date:
=DAY(TODAY())

Day of the week for a specific date (set the cell format to custom format “dddd”):
=WEEKDAY(TODAY())
=TEXT(WEEKDAY(TODAY()),”dddd”)

Month of the year for a specific date:
=MONTH(TODAY())

Year for a specific date:
=YEAR(TODAY())

Date for a specific year, month and day (Example returns 6/12/2005):
=DATE(2005,6,12)

Add days to a date (one week from today):
=TODAY()+7

Subtract days from a date (one week ago today):
=TODAY()-7

Last day of the this month:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
or
=EOMONTH(TODAY(),0)

Last workday of the current month:
=IF(WEEKDAY(EOMONTH(TODAY(),0))=6,EOMONTH(TODAY(),0)-1,IF(WEEKDAY(EOMONTH(TODAY(),0))=1,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))
or
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1,Holidays)

First workday of the following month:
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1,Holidays)

Number of workdays in the current month:
=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Holidays)

Last workday in range of days (A1 is start date, B1 is number of days in the period):
=WORKDAYS(A1,B1,Holidays)

DATEDIF() Syntax & Examples
The DATEDIF() function uses the following syntax:

=DATEDIF(start_date,end_date,”code”)

The start date must be less than the end date, or the function returns an error.

The following are the codes for the DATEDIF() function:

“y” Years
“m” Months
“d” Days
“md” Difference between days in a period; no month and years
“ym” Difference between the months in a period, no days and years
“yd” Difference between the days in a period, no years
Examples
To calculate the number of years between two dates:
=DATEDIF(A1,TODAY(),”y”) & ” Years”

To calculate the number of years and months between two dates:
=DATEDIF(A1,TODAY(),”y”) & ” Years, ” & DATEDIF(A1,TODAY(),”ym”) & ” Months”

To calculate the number of years, months, and days between two dates:
=DATEDIF(A1,TODAY(),”y”) & ” Years, ” & DATEDIF(A1,TODAY(),”ym”) & ” Months, ” & DATEDIF(A1,TODAY(),”md”) & ” Days”

Using the TEXT() function to calculate dates.
You can use the TEXT() to get the number of days or weeks between two dates:

Examples
Number of days:
=VALUE(TEXT((NOW()-$A$1)/24,”[h]”))

Number of weeks:
=VALUE(TEXT((NOW()-$A$1)/168,”[h]”))

source : http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_excel.htm

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: