LEFT function not working with dates

[Excel Dumbo]

Hello dear Excel friends,

I am trying to build a forumula to get the result "Jul-12" from th
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"



I know I am wrong. Please help correct me.


Thanks
Excel Dumb

 

[joeu2004]

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.

 

[lhkittle]

Hello dear Excel friends,



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"







I know I am wrong. Please help correct me.





Thanks

Excel Dumbo

If you format the Jul-11 cell to Number you will get something like 41101.00.
So the Left(A2,3) returns 411. The rest of the example formula is incorrect syntax.

Try this:

'Jul-11 in cell E1.(Note the leading "'"

In destination cell =LEFT(A2,3)&"-"&"12" returns Jul-12

HTH
Regards,
Howard

 

[Spencer101]

Hello dear Excel friends

I am trying to build a forumula to get the result "Jul-12" from th
given value "Jul-11

This is what i tried and I was getting a result "407-12
not sure if this is a formating issu

Date
Jul-11 =LEFT(A2,3),"-","12



I know I am wrong. Please help correct me


Thank
Excel Dumb

Hi

The reason you get an incorrect result is Excel stores dates as whol
numbers and the number for Jul-11 would be 40725

Try this formula to accomplish what you need

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)
Obviously swap A1 in that formula for the appropriate cell reference i
your workbook

 

[Excel Dumbo]

Hi Spencer,

Thanks a lot for your repliy. I tried your formula and it worked, i
gives me the result. But, it leads to another problem . I have explaine
this below

For Eg Example- Result "Jul-12" is in Worksheet1 cell B1

On Worksheet2 , cell DG25 is linked to the B1 of Worksheet 1

I have a formula below

=SUMPRODUCT(($H$20:$BE$20=$DG$26)*($H$21:$BE$21<=$DG$25)*($H28:$BE28))

which pulls out the cumulative values from Jan to Ju
(H$21:$BE$21<=$DG$25) When I apply your formula mentioned in the earlie
post, my formula in Worksheet 2 gives me values only till June. The bi
formula is supposed to give me values from Jan to July and not just Ja
to June.

Hope you understood the problem, as I am working on a big file, I am no
able to attach it here due to size restrictions.

Kindly advise

 

[Ron Rosenfeld]

Hi Spencer,

Thanks a lot for your repliy. I tried your formula and it worked, it
gives me the result. But, it leads to another problem . I have explained
this below

For Eg Example- Result "Jul-12" is in Worksheet1 cell B1

On Worksheet2 , cell DG25 is linked to the B1 of Worksheet 1

I have a formula below

=SUMPRODUCT(($H$20:$BE$20=$DG$26)*($H$21:$BE$21<=$DG$25)*($H28:$BE28))

which pulls out the cumulative values from Jan to Jul
(H$21:$BE$21<=$DG$25) When I apply your formula mentioned in the earlier
post, my formula in Worksheet 2 gives me values only till June. The big
formula is supposed to give me values from Jan to July and not just Jan
to June.

Hope you understood the problem, as I am working on a big file, I am not
able to attach it here due to size restrictions.

Kindly advise.

Without knowing the contents of your row 26, my guess is that your formula is returning everything up to and including 7/1/2011. If you want to include the first seven months of the year (Jan-Jul inclusive), then you must change the formula in Sheet1!B1 to compute to the end of the month:

=EOMONTH(A2,12)

or

=DATE(YEAR(A2)+1,MONTH(A2)+1,0)