Question about Microsoft Office Excel 2007

3 Answers

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

  • 6 more comments 
  • 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

×

Ad

3 Answers

  • 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
  • 68 Answers

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

Ad
  • 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
  • 87 Answers

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

Ad
  • 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
  • 192 Answers

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

  • 1 more comment 
  • Robert Bivins 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.


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


×

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

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

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

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
to "hack" into your FaceBook ID?

Sep 26, 2010 | Facebook Computers & Internet

1 Answer

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

1 Answer

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

1 Answer

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

1 Answer

Attendance list/ headcount spreadsheet


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

Apr 11, 2009 | Microsoft Excel for PC

3 Answers

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

2 Answers

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

2 Answers

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

1 Answer

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

Not finding what you are looking for?
Microsoft Office Excel 2007 Logo

144 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2888 Answers

Piyal Perera
Piyal Perera

Level 3 Expert

528 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18409 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...