I have a spreadsheet with names and a column of birthdates. I want to count how many people were born each year. How do I give a code to each year (from january to december). Thanks Pnina
It depends a lot on how you want to use the information farther on how you would set it up. But, to show the basics, use the YEAR function to extract the year from the date. Assuming the dates are in column B starting at row 2, enter in C2 =year(B2) then do a fill down and you'll have all of the years in column C. To get the count, use the COUNTIF function. Assuming your data is in rows 2 through 100, and you want the counts of people by year below that: enter the possible birth years in B102, B103, B104 ....(as far as you need to go).
In C102 enter =countif(C$2:C$100,B102)
Modify this as you wish to put the results where you want them.
You can use a filter. highlight the date coloumn then on the toolbar select 'Data' and then 'filter' and ;auto filter'.
When you look at the first box on your date column it should have a triangle at top R/H side. Click on this and a list of all your years will be there. Click on a particular year and ONLY that year will show on the spreadsheet. Click on 'all' to return to your full spreadsheet.
Hope this helps. Feedback is always appreciated.
Here is a solution that might work for you. Please be mindful that there are several different solutions that will achieve the same thing in Excel (I am assuming Excel is your software).
1) Make sure each column in your spreadsheet has a heading.
2) Highlight (select) the column of birthdays.
3) Choose Format from the menu bar.
4) Choose Cells... from the drop-down menu.
5) For the "category", choose Custom.
6) In the "type" box, type yyyy
7) Click OK and when you return to your spreadsheet, you will see only the years displayed. However, when you try to edit a cell, you will see that the entire birth date is stored and preserved.
8) Now, highlight the entire table
9) Then, choose Data from the menu bar
10) Choose Subtotals... from the drop-down menu
11) In the "At each change in" box, choose the name of the column with the birth dates
12) In the "Use function" box, choose Count
13) Leave all other choices at their defaults
14) Click OK, and you are done.
Using this method, you can continue to enter the data as you always have. In that way, the birth date info is never actually destroyed or converted. What you are doing is simply deciding what is displayed.
I hope this helps.
I would suggest adding a decade column to your spreadsheet, then using the trunc function to divide the years into decades:
1) Add the new column and label it whatever you like. I suggest "decade."
2) In the first cell of this column input =trunc((
3) Then, click on the cell where the first of the years are present, it should be just to the left.
4) Next, finish the formula by typing -1900)/10)*10
5) Now, hit enter.
6) Finally, copy the formula to the rest of the rows.
This method also allows you to use the aforementioned subtotals function to organize your decades just like you did with the years initially.
hmmmm...make sure your formula looks like this and it should work properly:
=TRUNC((B1-1900)/10)*10
Be careful to get the number of parenthesis correct. B1 is the referenced cell, of course. In my example, this formula is in cell C1.
It's all in the wrist. ;)
Actually, it is the trunc function that does the trick. Dividing by 10 allows you to make the individual year a decimal. Once that is done, trunc chops the decimal off. Then you multiply by ten to restore the number of decades back to it original value.
I know, it seems counter-intuitive. In fact, this really isn't an Excel trick, but an Algebra trick.
×
163 views
Usually answered in minutes!
Thank you very much. I didnt know about the "year" function. however I took the advice of the first writter with the "yyyy". great to have such experts!
Thank you very much! Your answer was clear and easy to follow , is great and helps me a lot. Thanks again
Pnina
Dear Gemini Yhanks for your help
I was so excited that I forgot the next question. How can each decade a specific code. Such as: from 2000 - to 2009 = code 20, from 1990 - 1999 = code 90, from 1980 - 89, code 80 and so on. Thanis again
Pnina
But the way you suggest gives me a different two digit number for each year and not just one number for each decade . any ideas?
uuuuuuuupps you are right. It does give decades .
However, if you multiply by 10 and then divide by 10, shouldnt you get the same number?
Puzzled but relieved :)
Thank you again :)
Your solution about creatin decades works only when I use a year that has not an underlying format of month-day-year. I am looking for a way to change the basic format of the years into just numbers . I do special paste, values only, but it doesnt work. Can you help? Thanks Pnina
×