MS Excel: DATE() Function

Adjusting the date in Microsoft Excel is quite easily done and the DATE() function has a number of hooks that can be utilized to your advantage.

The time() function works by all parts the same

Understanding the way Excel uses DATE()

The formula for referencing the date set in A1 is as follows

DATE(YEAR(A1),MONTH(A1),DAY(A1))
[/cc]
Putting the above formula in cell B1 will give you the same date as set in A1.

Excel treats the date as 3 separate pieces of data these being Year, Month and day.

You can set a particular element of date to whatever value you'd like. For example, If you have a date of 04/04/11 and only want the year of the date in A1 you would use
DATE(YEAR(A1),1,1)

This is telling Excel to get the year from A1, but set the Month and Day to 1 resulting in 1/1/11

NOTE: setting MONTH and DAY to 0 will give you the day before the last day of the month, and the month before the last day of the year, in turn, reducing the year by 1 as well IE: 30-11-2010.

Doesn’t make sense? I know right…

Furthermore,

DATE(YEAR(A1),10,10)

will result in 10:10:11

Using Arithmetic in =DATE()

Knowing how the DATE() function works, you can now perform arithmetic on the date.

Adding 3 Years to the time in A1 is simple by adding a +3 to the YEAR reference

DATE(YEAR(A1)+3, MONTH(A1), DAY(A1))

Subtracting 3 Years to the time in A1 is simple by adding a -3 to the YEAR reference

DATE(YEAR(A1)-3, MONTH(A1), DAY(A1))

Adding 3 Years to the date in A1 and rounding down to the Year is just by assigning 1 to the Month and day reference.

(We use 1 simply because there is no Day or Month 0)

DATE(YEAR(A1)+3, 1, 1)

Getting Fancy

Rounding the date off to the nearest Year: Using a simple ‘if else statement’ we can refer to the time in A1 and see if the Month and day are greater than or equal to 6/0 and then round up or round down as required.

IF((DATE(YEAR(A5), MONTH(A5),DAY(A5))

Here we could either the comparison DATE(YEAR(A5),6,30)) or DATE(YEAR(A5),7,0)). Both would work just as effectively as the other with the slight advantage going to July 0 as there will never be a date to match it, therefore it will always be either > or < A1.

Leave a Reply