 |
 | 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 / January 2006Extracting Month from Date and doing a calculation
I have a large spreadsheet, which is all the sales in my company on a daily basis. The data contained in cell O5 is the actual date based as dd/mm/yyyy with cell Z5 containing the monetary value of that sale. I need to extract on a monthly basis all the sales per month. I was trying to use =IF(O5="april",Z5) This does not work. So how do I extract the month from a date. Or any other ideas of how I could do it ??. TIA =if(month(o5)=4,z5,"whatgoeshere") > I have a large spreadsheet, which is all the sales in my company on a > daily basis. [quoted text clipped - 12 lines] > > TIA  SignatureDave Peterson > =if(month(o5)=4,z5,"whatgoeshere") Being picky here, but won't that, (when used for Month 1) return Z5 even for blank cells? Better to use something like: =IF(AND(O5<>"",MONTH(O5)=1),Z5,"whatgoeshere") (Ok I'll get back to looking at the code you so helpfully posted for me <g>) SignatureRegards Sandy sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > =if(month(o5)=4,z5,"whatgoeshere") > [quoted text clipped - 14 lines] >> >> TIA GR8 that works fine. The only other problem I have got now , is that the date cell, sometimes contains a piece of text 'unsold' This then throws up a #VALUE! error If the formulae comes across this piece of text, then the cell should then equate to 0 So how can I incorporate this problem into the IF statement. >=if(month(o5)=4,z5,"whatgoeshere") =IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0) SignatureRegards, Peo Sjoblom > GR8 that works fine. > [quoted text clipped - 9 lines] > > >=if(month(o5)=4,z5,"whatgoeshere") Cannot get that to work. Maybe its because cell O5 is a date and not a number ??? I'm not sure. >=IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0) Either your date is not a date that excel recognizes (meaning it is text) or if it's a date then it's not April Excel dates are numbers where one day is 1 and counting from Jan 0 1900 meaning that today is 38720 put 38720 in a cell, then format the cell as a date SignatureRegards, Peo Sjoblom > Cannot get that to work. > [quoted text clipped - 3 lines] > > >=IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0) Dates are just numbers to excel. Maybe this: =IF(ISNUMBER(O5)=FALSE,0,IF(MONTH(O5)=4,Z5,0)) or =IF(NOT(ISNUMBER(O5)),0,IF(MONTH(O5)=4,Z5,0)) > Cannot get that to work. > [quoted text clipped - 3 lines] > > >=IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0) SignatureDave Peterson You might consider using the AutoFilter to filter and display all of the sales for the month of April, then using the =SUBTOTAL(9,Z:Z) formula to get the total sales for that month. This way you could also actually "see" all the April sales and could maybe decern other interesting info besides just the total sales. Vaya con Dios, Chuck, CABGx3 > I have a large spreadsheet, which is all the sales in my company on a > daily basis. [quoted text clipped - 12 lines] > > TIA |
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.