Apr 26 2007

Date Formulas in Excel

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 re-formatted 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#in-post-ad]
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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

11 Comments on this post

Trackbacks

  1. Calculate Age in Excel wrote:

    [...] 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, [...]

    August 2nd, 2011 at 11:40 pm
  1. James said:

    Excelent site (get it!). Great Excel tips – thanks alot!

    April 20th, 2009 at 12:39 pm
  2. john said:

    Nice one James. Thanks for visiting!

    April 20th, 2009 at 1:31 pm
  3. 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
  4. 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
  5. 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 21-Aug-09 4 Last
    Sameer 21-Aug-09 2 Valid
    Anthony 21-Aug-09 5 Not Valid

    Please 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
  6. Asif said:

    Visitor____ Date of Visit___ Number of Visit in a month___ WarningMsg
    Asif ______21-Aug-09_____ 4_____________________ Last
    Sameer___21-Aug-09_____ 2_____________________ Valid
    Anthony__ 21-Aug-09_____ 5_____________________ Not Valid

    August 21st, 2009 at 11:52 am
  7. 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
  8. 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 ‘datecell-data’
    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
  9. abc said:

    i have a prob that i start 01.04.2012 then how i calculate 15-30days (1st),105-120days (2nd), 205-210days(3rd), 295-300days(4th) till the starting date

    April 1st, 2012 at 4:48 am
  10. 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

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives