LEFT function not working with dates
"Excel Dumbo" wrote:
I am trying to build a forumula to get the result
"Jul-12" from the given value "Jul-11"
This is what i tried and I was getting a result "407-12"
not sure if this is a formating issue
Date Jul-11
=LEFT(A2,3),"-","12"
(That is not the formula that you used. It is invalid syntax. In the
future, copy-and-paste formulas from the Formula Bar, especially when you
have a syntax question.)
It appears that dates are stored as Excel "serial numbers", which is normal.
It appears as Jul-11 due to the cell format.
And apparently, Jul-11 represents a date in July 2011 -- serial numbers
40725 to 40755.
If A1 displays Jul-11 and you want the same date a year later (Jul-12), you
can write:
=EDATE(A1,12)
in a cell, and format the cell as Custom mmm-yy, the same format as A1.
If that formula produces a #NAME error, see the help page for EDATE for
instructions for enabling the ATP.
Alternatively, you can use the following formula:
=DATE(1+YEAR(A1),MONTH(A1),DAY(A1))
Do __not__ use the formula =A1+365 or =A1+365.25 . Neither works reliably
around leap years.
In fact, even the DATE formula might not work as intended if A1 is Feb 29.
|