| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Month - 1
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
=DATE(2006,MONTH(TODAY())-1,1)
FormatCellsNumber tab, choose category Custom. In the Type box, enter mmm -- Kind regards, Niek Otten Microsoft MVP - Excel "HearSay" wrote in message ... |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. | | | |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
"HearSay" wrote in message
... 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. =TODAY()-DAY(TODAY()) and format as mmm -- David Biddulph |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
It seems that you could also just use:
=TEXT(A1,"mmm") "HearSay" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
Oops. I missed the minus 1.
=IF(MONTH(A1)=1,"Dec",TEXT(MONTH(A1)-1&"/1/2006","mmm")) "PCLIVE" wrote in message ... It seems that you could also just use: =TEXT(A1,"mmm") "HearSay" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
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 "HearSay" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
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 Epinn wrote: 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 "HearSay" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
From what I read, xl2007 does what you want!
Pete_UK wrote: 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 Epinn wrote: 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 "HearSay" wrote in message ... 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. -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
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 Dave Peterson wrote: From what I read, xl2007 does what you want! Pete_UK wrote: 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 Epinn wrote: 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 "HearSay" wrote in message ... 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. -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
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/...20/483205.aspx Pete_UK wrote: 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 Dave Peterson wrote: From what I read, xl2007 does what you want! Pete_UK wrote: 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 Epinn wrote: 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 "HearSay" wrote in message ... 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. -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display Month - 1
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 a.. "Dave Peterson" wrote in message ... 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/...20/483205.aspx Pete_UK wrote: 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 Dave Peterson wrote: From what I read, xl2007 does what you want! Pete_UK wrote: 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 Epinn wrote: 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 "HearSay" wrote in message ... 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. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| holiday dates | Excel Worksheet Functions | |||
| Find and input value based on month | Excel Discussion (Misc queries) | |||
| Help!!! Vlookup!! | Excel Worksheet Functions | |||
| HELP with this function | Excel Worksheet Functions | |||
| Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Linear Mode
