 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008wrong format for month in cell
Hi all, Using Excel 2003 on Windows XP Pro. sp2 I have a date in a cell like this 03/01/2008. let say cell B1 Under format cell; Category = Date , Type = *03/14/2008 In another cell I have a formula like this; =MONTH(B1) Under format cell; Category = Custom, Type = mmmm the result = January. what??? I want it to be March, don't understand why it thinks 01 = month? Not sure why it would not be; 03 = month, 01 = Day, 2008 = year Will on top of wanting the correct format that I mentioned for month above, I also need it be be the previous months name? so the value in the cell would = Feburary for 03/01/2008 thanks for your help gv The MONTH function assumes a DATE (or dateserial number) as it's argument. The month number of 03/14/2008 equals 3. That dateserial number (3) is the 3rd day after 31-DEC-1899...03-Jan-1900. Consequently, the formatting "mmmm" will return January. Try this in the other cell: =B1 Since B1 equal 03/14/2008, the cell will display "March". Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hi all, > [quoted text clipped - 18 lines] > > gv thanks for your help and explanation. yes that works. How do I subtract one month in that formula? thanks gv > The MONTH function assumes a DATE (or dateserial number) as it's argument. > The month number of 03/14/2008 equals 3. [quoted text clipped - 38 lines] >> >> gv There are a couple ways to subtract one month from a date... depending on whether you have the Analysis ToolPak add-in installed. If yes: =EDATE(B1,-1) If no: =DATE(YEAR(B1),MONTH(B1)-1,DAY(B1)) Here's a good resource for date functions: http://www.cpearson.com/excel/datearith.htm Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > thanks for your help and explanation. yes that works. > [quoted text clipped - 47 lines] >>> >>> gv I figured it out. =(B2)-1 thanks gv > thanks for your help and explanation. yes that works. > [quoted text clipped - 47 lines] >>> >>> gv
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.