Question about Microsoft Office Excel 2007

Transforming birthdates into year codes

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

Posted by on

• pnina797 Aug 02, 2009

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!

• pnina797 Aug 02, 2009

Thank you very much! Your answer was clear and easy to follow , is great and helps me a lot. Thanks again
Pnina

• pnina797 Aug 02, 2009

Dear Gemini Yhanks for your help

• pnina797 Aug 02, 2009

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

• pnina797 Aug 02, 2009

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?

• pnina797 Aug 02, 2009

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 :)

• pnina797 Aug 02, 2009

Thank you again :)

• pnina797 Aug 04, 2009

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

×

• Level 2:

An expert who has achieved level 2 by getting 100 points

MVP:

An expert that got 5 achievements.

Governor:

An expert whose answer got voted for 20 times.

Scholar:

An expert who has written 20 answers of more than 400 characters.

• Expert

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

• Level 2:

An expert who has achieved level 2 by getting 100 points

MVP:

An expert that got 5 achievements.

Governor:

An expert whose answer got voted for 20 times.

Scholar:

An expert who has written 20 answers of more than 400 characters.

• Expert

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

• Level 2:

An expert who has achieved level 2 by getting 100 points

MVP:

An expert that got 5 achievements.

Sergeant:

An expert that has over 500 points.

Governor:

An expert whose answer got voted for 20 times.

• Expert

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).

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

• 1 more comment
• Robert Bivins Aug 02, 2009

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.

• Robert Bivins Aug 02, 2009

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.

• Robert Bivins Aug 02, 2009

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)
Goodluck!

Posted on Jan 02, 2017

×

my-video-file.mp4

×

Related Questions:

You all wont accept my password which is my birthday so i wont forget. carolboasso@aol.com

PIck a different, and more "complicated', password, e.g., the first character of each word of a phrase, such as:

I
Was
Born
On
The
4th
Of
July

After all, how many people know your birthdate,
and how many of them would try to use your birthdate

Sep 26, 2010 | Facebook Computers & Internet

How do I explain to a 6 year old child why she is younger with a birthdate of 2/1/2004 than someone with a birthdate of 2/27/1988? She feels she shoudl be older because her birthday is 2/1 and the other...

why correct her? that just cute she will learn it eventually...

make her count backwards starting with the current year,
a new born baby born today is how old?
what if that baby was born last year?
the year before?

it will click

Feb 26, 2010 | Mathsoft StudyWorks! Middle School Deluxe...

I want an formula in excel to find out number of days present per month an perticular worker

Hello this is Baris,
Can you tell me more about this employee. Are you creating an excel spreadsheet to keep record of his days. Are you just getting the numbers from an outside source like a schedule.

To be able to give you an answer I will assume that you have the info already in the same spreadsheet.

Lets say Column A is the days of the month.
Column B is the information column like the hours that the employee worked. If the month has 31 days this is what you need to do.
Click on the cell B32 and type the formula
=count(B1:B31) and press enter. This will count the number off cells which have a value in it in that month.

If you provide me more info we may come up with a better solution. Hope this helps :)

Sep 24, 2009 | Microsoft Excel for PC

Change the format of dates to simple numbers

Highlight the column you'd like format as a number and copy it. Choose another column and hit Paste Special and choose Value. The date is then represented as a number.

Aug 03, 2009 | Microsoft Windows XP Professional SP3 Full...

Don't do this in Excel. Buy Visual Foxpro.
And make proper application.

Apr 11, 2009 | Microsoft Excel for PC

EXCEL FORMULA PC

The solution I've used in similar situations is to create a 3rd column C with the items in column A and column B concatenated.

C2 = A2 & B2
C3 = A3 & B3
C4 = A4 & B4
etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

May 27, 2008 | Microsoft Excel for PC

Should I use countif or if or what ??

hi this my id :dadu_mf@rediff.com plz send excel material

Mar 25, 2008 | Microsoft Excel for PC

Counting

If it isn't too big of a spreadsheet, you can sort it on the column which has the numbers in it, which will put all the 1's in the top of the stack and you can then select just the cells with 1's in them and Excel will count the number of cells that you currently have selected. The number will show up in the Name Box (above cell A1) as: R2 X C2 (for two rows and one column currently selected). Rows are horizontal and columns are vertical.

Jan 10, 2008 | Microsoft Excel for PC

Multiple letters in each cell, want to add the letters and tally how many of each

Use the =COUNTIF function

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")
or
@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in
column a from row 1 to row 100.

Mike

Oct 31, 2007 | Microsoft Excel for PC

Open Questions:

Related Topics:

144 people viewed this question

Level 3 Expert

Level 3 Expert