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.
Posted on Aug 02, 2009
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.
Posted on Aug 02, 2009
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.
Posted on Aug 02, 2009
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.
×
Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of.(from cars to computers, handyman, and even drones)
click here to download the app (for users in the US for now) and get all the help you need.
Goodluck!
Posted on Jan 02, 2017
Sep 26, 2010 | Facebook Computers & Internet
Feb 26, 2010 | Mathsoft StudyWorks! Middle School Deluxe...
Sep 24, 2009 | Microsoft Excel for PC
Aug 03, 2009 | Microsoft Windows XP Professional SP3 Full...
Apr 11, 2009 | Microsoft Excel for PC
May 27, 2008 | Microsoft Excel for PC
Mar 25, 2008 | Microsoft Excel for PC
Jan 10, 2008 | Microsoft Excel for PC
Oct 31, 2007 | Microsoft Excel for PC
144 people viewed this question
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
×