DATE formula

The DATE formula is a versatile formula is normally used to build a date by providing the individual parts of the date (year, month, day).

Syntax

DATE Formula has three parts:

DATE (year, month, day)

year

year is the year in which the date would be occurring.

month

month is the month in which the date occurs.

day

day is the portion representing the day part of the date that we are trying to construct.

Example of a DATE Formula

 

Month	Day	Year	Result	Formula
10	31	2011	10/31/2011	=DATE(C2,A2,B2)

 Using the Date formula lets me take the individual components of a date and put them together. As shown above, I write the formula as =DATE(A2,B2,C2).

The result is October 31, 2011.

Formatting a DATE in Excel

You want to ensure that anytime you have a date in your spreadsheet, that it is formatted consistently.  Some countries display a date as yyyy-mm-dd while others use yyyy-dd-mm.

This can be critical as 2011-10-11 yyyy-mm-dd is not the same as 2011-10-11yyyy-dd-mm.

To format a date, while the cursor is on the cell or range of cells that you want to format then press Ctrl + 1. Choose the "Number" tab and click on "Date". In the "Type", select the format you want.

Please note that you are not limited to the days or months or years of a calendar. If I change the month to 14 in the above example I get the following...

Month	Day	Year	Result	Formula
14	31	2011	3/2/2012	=DATE(C2,A2,B2)

So what's happening in my formula???

Well if you start calculating month 1 as January, then 14 months would be February. However there are not 31 days in February so Excel rolls this into March and adjusts the day (adjusting for leap year.

This ability allows us to add/ subtract to days, months and years to get future or even past dates.

So no you know and the next time you are asked to figure out future dates, you will be able to "WOW" your audience.