Date Formulas in Excel
 11 Comment
Some of the more common formulas I use on a regular basis are date manuputation formulas. Whether it using the Date() formula or the Month() formula or many others, there are several ways to show data that have dates involved.
Let’s start by taking a look at the Date Formula itself:
Date(Year, Month, Day)
where Year, Month and Day are numeric value or cell references to numeric values. The result returns a serial number for the result; if cell was formatted to general before the formula was entered it will be automatically reformatted in date format.
Example 1: Date(2007, 1, 5) will return 1/5/2007.
Example 2: Date(2007, 14, 5) will return 2/5/2008.
(Note: if the number for the month is greater than 12 year will be incremented accordingly and the months mod 12 will be returned for the resulting months. The same is true if the days entered are greater than the number of days for that particular month)
[ad#inpostad]
Example 3: Date(2007,1,32) will return 2/1/2007
Excel stores dates as sequential serial numbers so they can be used in calculations. For example if 1/1/2007 is formatted as a number, you will see 39083. If you add 2 to that (as in 2 days) the result will be 39085 or 1/3/2007 if formatted as a date again.
Date Formula using Cell References
Most of the time you will using the Date Formula, you will be using it together with cell references. Below are a few examples of how to do this.
Example 1: Date(C2,D2,E2) will return 1/16/2007
Example 2: Date(C4,D4,E4) will return 5/3/2007
Example 3: Date(C4,D4+2,E4+10) will return 7/13/2007
Tricks Using the Date Formula
There are also formulas that will break apart a date, such as 2/1/2007. Using the Day(), Month(), and Year() Formulas, you can create 3 different columns to break apart the date if you like to have summaries of that data by month for example.
Example 1: =Year(“1/1/2007″) returns 2007, which is the same as
Example 2: =Year(Date(2007,1,1)) returns 2007
Example 3: =Year(1/1/2007) returns 1900, date must be entered as a string if inside year formula
Trick #1: Use the Date formula to find the number of days in a month. If, for example, you are trying to find the number days in February 2007, enter the formula as =Day(Date(2007,3,1)1), the result will be 28.
Trick #2: Use the Date formula to find the last date in a month. If, for example, you are trying to find the last day in March 2002, enter the formula as =Date(2002,4,1)1, the result will be 3/31/2002.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
11 Comments on this post
Trackbacks

James said:
Excelent site (get it!). Great Excel tips – thanks alot!
April 20th, 2009 at 12:39 pm 
john said:
Nice one James. Thanks for visiting!
April 20th, 2009 at 1:31 pm 
flapep2 said:
I am new to Excel. I have been using Lotus 1 2 3. How do I make an “@today” date in Excel? Thanks all.
July 19th, 2009 at 9:50 am 
john said:
Using the formula “=Today()” will give you today’s date in that particular cell… Is that what you were looking for?
July 20th, 2009 at 12:07 am 
Asif said:
I just visited this site and seems it could solve my problems in excel, i will save this weblink to my opera web browser.
pls see below table which i need to be solved.
Visitor Date of Visit Number of Visit in a month WarningMsg
Asif 21Aug09 4 Last
Sameer 21Aug09 2 Valid
Anthony 21Aug09 5 Not ValidPlease help me to fill the formla in column 3 & 4.
Changing the date each time the number in column3 should be increased as per visit, column 4 also should be updated.
Note: but i dont want to repeat the visitor name in column again only date should change every visit.
Need your help, thanks in advannce.
Asif
August 21st, 2009 at 11:48 am 
Asif said:
Visitor____ Date of Visit___ Number of Visit in a month___ WarningMsg
Asif ______21Aug09_____ 4_____________________ Last
Sameer___21Aug09_____ 2_____________________ Valid
Anthony__ 21Aug09_____ 5_____________________ Not ValidAugust 21st, 2009 at 11:52 am 
john said:
You’ll probably need to write a macro to handle this situation. Possibly using a cell event prodedure… have you written a macro before?
August 21st, 2009 at 12:53 pm 
sdk said:
how do i get dateCell appended with single quote. i am trying to build a SQL query and want to do something like ‘datecelldata’
so the formula would be =”‘”&A2″‘” in this case if the A2 is date formatted cell the it give a numberical value while evaluating the formula cell.thank you,.
January 19th, 2012 at 3:43 pm 
abc said:
i have a prob that i start 01.04.2012 then how i calculate 1530days (1st),105120days (2nd), 205210days(3rd), 295300days(4th) till the starting date
April 1st, 2012 at 4:48 am 
Ankit Lamba said:
Hi,
Excel problem
can u pl. let me know to calculate the difference b/w two dates but format shud be in (.) format…diffence shud be in no. of days…
ex: 02.01.2011 & 04.04.2012
December 12th, 2012 at 3:19 am
[...] this calculation are both date functions. The TODAY function returns the current date. And the DATE function builds a date from the year, month and day (which looks like this: DATE(yyyy, [...]