IF month and year?
Thanks all!
Last edited by worecx; 10-06-2011 at 07:52 AM.
Hello,
Assuming you want to do this with a formula copied down the column of dates, the following should do it. There are lots of different ways. This is just one of many.
=IF(J21="";0;(IF(text(j21;"MM-YYYY")="10-2003";1;0))
If you can post a spreadsheet, it will be easier to put in the formula. Your regional settings use the semicolon, mine use the comma. An Excel workbook would make this difference transparent and help concentrate on the formula and the data instead of the punctuation.
cheers,
Hi Worecx,
What you need is to use the AND function and MONTH function.
Similar to your YEAR you can do something like
(=IF(J21="";0;(IF(AND(YEAR(J21)=2003;MONTH(J21)=10);1;0)))
Wow, thanks teylyn and quekbc.
That really helped me out and it works perfectly. Yesterday i was busy trying formulas for 4 hours and gave up.
Thanks again! Problem solved
My Excelsheet is getting up to 190mb now. Is there a way to just count all cells which have for example "10-2003"?
Thanks again!
In a different cell, just type in =countif(A:A,1). Since the formula from quekbc returns 1 or 0, your criteria in the countif function will be 1.
I'm looking for a new formula which will count the cells which contain (for example) 10-2003
The formula has to look in the 65.000 records where month 10 and year 2003 appears (for example)
This result has to be put in 1 cell.
Hopefully you understand what i mean.
You mean to say, you don't want to use the If & IF(And( functions given above but want to directly use the count or countif to arrive at the count of "10-2003"?
You can do so if you use this function first
and then use the count in an independent cell. You can hide the column that contains the above formula if you do not want to display it.(=IF(J21="";0;(IF(AND(YEAR(J21)=2003;MONTH(J21)=10);1;0)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks