Cell with the Month in it -1 (minus one)

[sdoppke]

Hi everyone, coudl anyone tell me how to take a cell that has a month in it that uses a dropdown list in A1. (i.e. November) and have B1 list what ever month is showing minus 1 (october)?


Thanks in advance.

sd

 

[mikerickson]

You might be looking for the formula

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

Or
=DATEVALUE(TEXT(A1-31,"""1"" mmm yyyy"))

 

[sdoppke]

You might be looking for the formula

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

Or
=DATEVALUE(TEXT(A1-31,"""1"" mmm yyyy"))

Thanks a ton for the answer, I tried it a bunch of different way and get a #value error.

I think the second one (text) is my best option.

A1 simply reads "November" (its from a dropdown list) and need B1 to read "October". Any ideas what i might be doing wrong?

thanks in advance


sd

 

[jeffreybrown]

Change A1 which holds November to 11/1/2011 and custom format to mmmm

 

[sdoppke]

Change A1 which holds November to 11/1/2011 and custom format to mmmm

Is there a way to not do that, because A1 is a dropdown of months?

If that doesnt work, do you think a V(H)Lookup might work? Where if find the Month in A1 then goes up one row and reports that one in B2? Sounds like it would work butr cant figure out how to do it?

Thanks for the reply

sd

 

[Scott Huish]

=mod(month(datevalue(1&a1&1))-2,12)+1

 

[mikerickson]

Try
=LOOKUP(A1,{"april","august","december","february","january","july","june","march","may","november","october","september"},{"may","september","january","march","february","august","july","april","june","december","november","october"})

or (untested)
=TEXT(DATEVALUE("0 "&A1&", 2001"),"mmmm")

 

[sdoppke]

Try
=LOOKUP(A1,{"april","august","december","february","january","july","june","march","may","november","october","september"},{"may","september","january","march","february","august","july","april","june","december","november","october"})

or (untested)
=TEXT(DATEVALUE("0 "&A1&", 2001"),"mmmm")

The first one looks like it will work good, but it doesnt match the month it is supposed to (but does report a different month, so is a good start ) I just dont understand the logic to tweak it?

The second is a no go.

sd

 

[T. Valko]

Hi everyone, coudl anyone tell me how to take a cell that has a month in it that uses a dropdown list in A1. (i.e. November) and have B1 list what ever month is showing minus 1 (october)?


Thanks in advance.

sd

Try this...

=TEXT((A1&1)-1,"mmmm")

 

[sdoppke]

Try this...

=TEXT((A1&1)-1,"mmmm")

You Rock!!!!!!!!!!!!!!!!!!