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

"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.

On Monday, August 27, 2012 12:36:51 AM UTC-7, Excel Dumbo wrote:
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









--

Excel

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

Hi,

The reason you get an incorrect result is Excel stores dates as whole 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 in your workbook.

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.

It's because the formula I gave you returns 1st July 2012 formatted as Jul-12 and you need 31st July 2012.

Which version of Excel are you using?
If 2003, do you have the analysis toolpak installed??

Try this in place of the formula I gave you earlier.

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


If this doesn't work, it's because you're using 2003 or earlier and do not have the analysis toolpak installed.

Let me know if it works. If not I will sort out a workaround for you.

Of course, if that does work you could just use =EOMONTH(A1,12) for the same thing. Again, swap A1 in the formula for your relevant cell.

S.

Hi Spencer,

Thanks a lot for your reply. I did try both the formula and it is giving me a #NAME? error. I am using excel 2003 and do not have analysis tool pack


To give u a bigger picture of the problem

I have two data validation lists one for 2011 (Jan-11, Feb-11, Mar-12, Apr-11)
and second data validation list 2012 (Jan-12, Feb-12, Mar-12, Apr-12)

I do not intend to use both data validation lists . I prefer to use only one data validation list, that is, 2011 . Corresponding month for 2012 should automatically come up when i select the month feom 2011 data validation drop down.

Hope this will further define the problem.

Thanks Spencer. Awaiting ur reply.

Without the analysis toolpak you can use this version of the formula:

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

But as you can see from =EOMONTH(A1,12) things are far easier with it, so if you can install it I would highly recommend it.

Hello Spencer,

had a further look at the problem and managed to find out what went wrong. THe formula u mentioned worked well. I had to align the date format on the data validation list and the date format on the look up range which solved the problem. Jan-12 in the validation list was coming up as - 1/12/2012, Feb-12 as 2/12/2012 and in the look up range, Jan 12- was coming up a 1/1/2012 and Feb-12 as 1/2/2012. If i use the former format , it will give me values from Jan-Jul , and if i use the later format it gives me values from Jan- Jun.

Thanks a lot for your help. This site is wonderful

Glad to be of help. :)

On Mon, 27 Aug 2012 09:17:23 +0000, Excel Dumbo wrote:


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)