Microsoft Excel - bring in previous month based on current month in a drop dwon list

Asked By shekhar on 15-Mar-13 01:06 AM
I have a excel file......cell A1 HAS A LIST BOX FOR MONTHS........i have in c1 and D1....current month and previous month.

based on the selection in cell A1, i get the current month in cell C1....how do i get previous month in D1......I cell C1 is Jan i should have Jan in cell D1.

Harry Boughen replied to shekhar on 17-Mar-13 01:55 AM
Hello Shekhar,
I assume that the 'month' is merely a text spelling and not a date value.  In that case I would set up a lookup table with a list of the months in one column and the corresponding previous month in the second and go from there using the value in C1 as the lookup value.
Regards
Harry
shekhar replied to Harry Boughen on 17-Mar-13 09:03 AM
Well I have in A1 the drop down for months and they are in mmddyyyy format. I changed the custom format to mmm.

In cell c1 & d1 I have current month and previous month. Cell c1 I have given as = a1. Cell d1 I have given the formula eomonth(now(a1),-1) ....but when I select Jan , I should see jan in d1 . But if cell c1 is feb d1 should be jan .....so on and so forth. Hope I am clear.

Thanks for your reply.....I am looking for some easy date formula referring to cell A1.

Thanks
Shekhar
Harry Boughen replied to shekhar on 17-Mar-13 04:52 PM
Hello Shekhar,
Then try
=EOMONTH(A1,-1)

Or, if that does not work, try
=EOMONTH(DATEVALUE(A1),-1)

Regards
Harry
shekhar replied to Harry Boughen on 20-Mar-13 11:43 PM
The first one works , in fact I had used that formula.....but does not work in case of jan,

So for Jan I would want Jan in both the columns and for rest of the months it should be current month and previous month.

Second formula Is erroring out.....my date format in cell A1 is mmddyyyy and is list box. For ex. 01/31/2013 and so on...

Thanks Shekhar
Harry Boughen replied to shekhar on 21-Mar-13 12:08 AM
Hello Shekar,
Try

=IF(MONTH(A1)<>1,EOMONTH(A1,-1),EOMONTH(A1,0))

Regards
Harry
shekhar replied to Harry Boughen on 22-Mar-13 10:58 AM

Hi Harry,


This works...thanks a ton..


i was also working on the if condition but did not get the MONTH(A1)<>1.

Thanks again for your help.

regards
Shekhar