Date Formula

[gymwrecker]

Need help with the following formula:

A1=2010/01/12

B1==DATE(LEFT(A1,4)+1,MID(A1,5,2),RIGHT(A1,2))

Why I'm getting 4020/02/28 as the result in B1? What I'm looking for is to add 1-Year to the date in A1. Please advise....

 

[T. Valko]

Need help with the following formula:

A1=2010/01/12

B1==DATE(LEFT(A1,4)+1,MID(A1,5,2),RIGHT(A1,2))

Why I'm getting 4020/02/28 as the result in B1? What I'm looking for is to add 1-Year to the date in A1. Please advise....

Try this...

=EDATE(A1,12)

Format as Date

Note that the EDATE function requires the Analysis ToolPak
add-in be installed if you're using a version of Excel prior to
Excel 2007. If you enter the formula and get a #NAME?
error look in Excel help for the EDATE function. It'll tell you
how to fix the problem.

 

[jmthompson]

What is really in A1? Format as General to see if it really contains the Excel numeric equivalent of the date...

 

[T. Valko]

Need help with the following formula:

A1=2010/01/12

B1==DATE(LEFT(A1,4)+1,MID(A1,5,2),RIGHT(A1,2))

Why I'm getting 4020/02/28 as the result in B1? What I'm looking for is to add 1-Year to the date in A1. Please advise....

To directly answer your question...

In Excel dates are really just numbers formatted to look like dates. These numbers are the count of days since a base date. The default base date is Jan 1 1900. The numerical value given to dates is commonly referred to as the date serial number. Jan 1 1900 has a value of 1 and each day since that date the numeric value increases by 1.

So:

1/1/1900 = date serial number 1
1/2/1900 = date serial number 2
1/3/1900 = date serial number 3
1/4/1900 = date serial number 4
1/5/1900 = date serial number 5
1/1/1975 = date serial number 27395
1/1/2000 = date serial number 36526
1/1/2011 = date serial number 40544

When you enter a date in a cell Excel recognizes the entry as a date and automatically formats the entry as a date. However, the true value of that entry is the date serial number.

If you enter the date 1/1/2011 in cell A1 the cell displays as 1/1/2011 but the true value of that cell is the date serial number 40544. You can see this by changing the cell format to General or Number.

So, when you do calculations based on the "date" you're actually doing the calculation on the true value of the cell, the date serial number.

In your case here's how it breaks out:

A1 = 2010/01/12 (y/m/d) format

The true value of the cell, the date serial number, is 40190.

Taking apart the DATE function we get:

LEFT(A1,4)+1 ... 4019 + 1 = 4020

MID(A1,5,2) ... 0

RIGHT(A1,2) ... 90

Then:

DATE(4020,0,90) = 4020/2/28

Ok, this part will be hard to understand....

There is no month 0 or day 90. The DATE function is "smart" and can adjust for these unusual values.

The 0th month actually refers to the previous month. In this case the 0th month is actually December 4019.

In effect it would look like this:

DATE(4019,12,90)

That still looks goofy, don't it?

Well, the DATE function does the same thing with the days. There is no day 90 BUT if you add 90 days to 4019/12/1 (inclusive) you end up at 4020/2/28.

So:

A1: 2010/01/12

B1: =DATE(LEFT(A1,4)+1,MID(A1,5,2),RIGHT(A1,2))

= 4020/2/28

Which is the correct result albeit not the expected result.

 

[T_Holliday]

Need help with the following formula:

A1=2010/01/12

B1==DATE(LEFT(A1,4)+1,MID(A1,5,2),RIGHT(A1,2))

Why I'm getting 4020/02/28 as the result in B1? What I'm looking for is to add 1-Year to the date in A1. Please advise....

To answer the second part of the question as follow-up to T. Valko's explanation of why you're getting that result, the simple answer for B1 is:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

 

[T. Valko]

To answer the second part of the question as follow-up to T. Valko's explanation of why you're getting that result, the simple answer for B1 is:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

It depends on how they want to handle "overflow" dates like 2/29/2008.

Since there is no February 29th in 2009 what date is one year later? Is it 2/28/2009 or 3/1/2009?

EDATE(A1,12) returns 2/28/2009

DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) returns 3/1/2009

 

[T_Holliday]

True. I'm just cautious about using those add-ins on workbooks that might get read by a person that doesn't have that add-in installed, in which case they just get a #NAME error. I'd rather make it slightly more complicated in order to avoid the compatibility issues. Since leap years are pretty much the only exception to the case, if you need the formula to handle it differently, you just add an IF to the DAY portion of the DATE statement to subtract one if MONTH=2 and DAY=29.

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-IF((MONTH(A1)=2)*(DAY(A1)=29),1,0))

But, if the workbook is for personal use only and isn't going to be shared with anyone else, then using the Analysis add-in EDATE would be fine and more elegant.