Display Month - 1

H

HearSay

I have a cell that I would like to display the three letter month minus a
month based on the current day
So today is 09/29/2006, I would like the cell to contain "Aug"

Is this possible.
 
N

Niek Otten

=DATE(2006,MONTH(TODAY())-1,1)

Format>Cells>Number tab, choose category Custom. In the Type box, enter mmm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a cell that I would like to display the three letter month minus a
| month based on the current day
| So today is 09/29/2006, I would like the cell to contain "Aug"
|
| Is this possible.
|
|
|
 
D

David Biddulph

HearSay said:
I have a cell that I would like to display the three letter month minus a
month based on the current day
So today is 09/29/2006, I would like the cell to contain "Aug"

Is this possible.
Click to expand...

=TODAY()-DAY(TODAY())
and format as mmm
 
P

PCLIVE

Oops. I missed the minus 1.

=IF(MONTH(A1)=1,"Dec",TEXT(MONTH(A1)-1&"/1/2006","mmm"))
 
E

Epinn

How about

=TEXT(EDATE(TODAY(),-1),"mmm")

If EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

If you want to go back 3 months, just change "-1" to "-3"

If you want to advance 4 months, just change "-1" to "4"

I like using EDATE to manipulate (plus or minus) months. Does anyone see a problem?

Epinn

I have a cell that I would like to display the three letter month minus a
month based on the current day
So today is 09/29/2006, I would like the cell to contain "Aug"

Is this possible.
 
P

Pete_UK

One problem, as you state, is that it needs the Analysis ToolPak
add-in. Not everyone will have this installed (why didn't Microsoft
include it within the standard Excel?)

Pete
 
D

Dave Peterson

From what I read, xl2007 does what you want!

Pete_UK said:
One problem, as you state, is that it needs the Analysis ToolPak
add-in. Not everyone will have this installed (why didn't Microsoft
include it within the standard Excel?)

Pete
Click to expand...
 
P

Pete_UK

No, Dave, I've managed without installing it and have found other ways
of doing what the functions in there do, so that my clients don't have
to install it if they don't want to. For the same reason, I haven't
installed ASAP Utilities or Morefunc etc, as I couldn't guarantee that
my users would have them so I don't want to become reliant on
"non-standard" features, even though they could probably improve my
productivity.

It's always struck me as strange, though, that the Analysis ToolPak
comes as an add-in on the same disc as Excel - surely, even if it was
originally an after-thought, after XL97 or thereabouts it could have
been incorporated in later versions?

Pete
 
E

Epinn

Dave,

Thanks for the info. So, EDATE( ) is readily available with V. 2007.

I spotted the following from the link:-

*************************************************************

(1) Since the old ATP functions are going to become built-in functions .........

(2) ...... we've added five commonly requested functions to the Excel function library:
a.. IFERROR
b.. AVERAGEIF
c.. AVERAGEIFS
d.. SUMIFS
e.. COUNTIFS
*************************************************************

More functions to learn ......

Epinn

I meant that xl2007 has moved all the functions from the analysis toolpak and
put them into excel proper.

http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx
 
Top