Date Formulas and Functions in Microsoft Excel

Excel stores dates in sequential serial numbers beginning with January 1st, 1900 as 1.  January 1st, 2006 is 38718 because it’s 38,718 days after January 1st, 1900.  Excel on MAC works the same, except the dates start on January 1st, 1904. Don’t ask me why.

Note: There are several different ways to do everything in Microsoft Office applications. Date formulas and functions are no different, so this list isn’t comprehensive, but should get you by until you become an expert.

Note: Some of these formulas and functions require use of the Analysis Toolpak Addin. Just go to ToolsàAddins, and put a check next to Analysis Toolpak if you get a #NAME? error.

Note: Some of these formulas and functions use a named range called “Holidays”. To create it, make a list of dates in a column. Assign the named range to the cells.

Tip: Volatile formulas are formulas that recalculate every time the workbook calculates. The =NOW formula (among others) is a Volatile formula. If you open a workbook that contains a volatile formula, you make no changes, and close it, don’t be surprised if you’re asked to save the workbook. That’s because the value that the recalculation created did, in fact, change in your workbook.

Date Formulas and Functions

• 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]“))

=INT((MAX(A1:A10)-MIN(A1:A10))/7)

or if you want the odd days as well:

=INT((MAX(A1:A10)-MIN(A1:A10))/7)&” Weeks & ”
&MOD((MAX(A1:A10)-MIN(A1:A10)),7)&” Days”

Courtesy of ExcelIsFun’s Channel

http://flightline.highline.edu/mgirvin/excelisfun.htm

http://www.youtube.com/user/ExcelIsFun#p/search/25/uoED6-YF7dE

COURSE TEXT In this example, a text file has been imported into Excel. First names and last names are in two separate columns. To combine the information into one cell, you would use a formula that is unlike what you’ve seen before. It uses the ampersand (&) operator instead of a function. You would type this formula in cell C2: =A2&” “&B2 The ampersand tells Excel to combine the text from cell A2, and a space (identified by the quotation marks around it), and the text from cell B2. To combine the other names into other cells in column C, you would copy the formula down the column. Note    You can do the same operation by using the CONCATENATE function: =CONCATENATE(A2,” “,B2), but it’s faster to just type the ampersand. “Concatenate” means to combine, by the way, or had you guessed?