more than two number frmts [=x]"style"

[jberg123]

In A1 I might have 3,6,9 or 12.

I want number format to display "31 March" for 3, "30 June" for 6, "30 Sept" for 9 and "31 Dec" for 12. How can I do this?

Number format [=3]"31 March";[=6... only works for 2 conditions it seems.

Many thanks guys

 

[CWatts]

=LOOKUP(A1,{3,6,9,12},{"31 March","30 June","30 Sept","31 Dec"})

Change things around if your needs change.

 

[jberg123]

Thanks, that's pretty funky, will have use of it another time, but it doesn't quite work for this because I need the value of the cell still to be 3,6,9 or 12 but I want it to be displayed as specified earlier (the months).

Any ideas?

If worst comes to worst I'll have to get around it with a dummy cell somewhere and use your lookup idea...

 

[CWatts]

Oh, that's my bad, I totall spaced on what you were looking to do. I"m not that good at number formats.

I guess my only question would be why does it have to change it, is it only for display purposes or does it serve a function?

If it is just for display purposes, you can make a text box, and set the contents of that text box to the formula I provided and put it on top of your cell so the cell value itself will retain its numerical content, but the display will be the dates you're looking for.

Would that work?

 

[jberg123]

Oh, that's my bad, I totall spaced on what you were looking to do. I"m not that good at number formats.

I guess my only question would be why does it have to change it, is it only for display purposes or does it serve a function?

If it is just for display purposes, you can make a text box, and set the contents of that text box to the formula I provided and put it on top of your cell so the cell value itself will retain its numerical content, but the display will be the dates you're looking for.

Would that work?

Ah, that would in fact work. IT's for display purposes (trying to make a rather complicated benchmarking file be as easy as possible for next generation analysts to work in. It would help if the financial year end was displayed as 31 March instead of "3").

But, also trying to keep the file as slim as possible and not sure if text boxes is optimal in that regard. But it's definitely a solution that should work. Thanks!

 

[CWatts]

Ah ha! Solved!

Use conditional formatting. Set the formula to =$A$1=3 and then set the number format to "March 30".

Repeat for the other condiitions you want.

If you have Excel 2003 or earlier, you'll have to maunally set the number format to "March 30" and then add conditions for the other 3 months which would override the original format since you're limited to 3.

Excel 2007 you can easily have multiple formats.

 

[jberg123]

Excel 2007 you can easily have multiple formats.

tell me about it... don't know how many hours I've spent on macros sorting conditinoal formatting with more than three (four) conditions...


will try that! thanks a lot!