excel formula

Russellgram

New Member
Joined
Mar 8, 2002
Messages
1
Hello Excelers,

I am trying to figure out how solve the following problem: I have to keep track of boys' ages as they change every month and have the information at my finger tips. I set up a spread sheet as below, from January to December of that year.

DOB = date of birth and 15.08 means 15 years & 8 months old - 1/1/02=the date of the month

Column Column Column etc. etc.
A B C D E
Name DOB 1/1/02 2/1/02 3/1/02
Jones, R. 3/28/86 15.10 15.11 15.12
(For some reason, when my message is posted all the information right above is crunched together to on the left side and may not make sense to anyone)

My first problem is that I want 15.12 to turn to 16.00, not 15.12 and continue 16.01, 16.02 and so on.

I have been using the following formula
=C2+0.01, =C2+0.02, etc. This works fine until it comes to converting the 15.11 into 16.00. It just changes into 15.12 and I also can't figue out how to go on to the next January.

The second problem is not knowing what to do when I get to December of the year. How do I get back or go on to January of the next year without having to reenter all the data for 200 plus boys? The list will only continual to grow.

A gentleman suggested that I try the following formula:

=if(MOD(C2,1)=.11,C2+.89,c2+.01)

When I tried it the excel program told me it was not correct and did I want them to correct the formula. I clicked yes and it changed the formula to:

=IF(MOD(C2,1)=0.11,C2+K4.89,C2+0.01

When I copied it in the next cell it changed the formula according to the column I was in, but when it got to the 15.12 column it did not change it into 16.00.

First, I would like to know what this formula is saying, and Secondly, I don't know why it is not working.

Thanks in advance for any guidance anyone can give me!

Rich
This message was edited by Russellgram on 2002-03-09 20:59
This message was edited by Russellgram on 2002-03-09 21:04
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi
Russellgram

I must admit I get a bit lost trying to read you explanation, but if you want a persons age in Years and months you would most likely be better to have the years in one column and the months in another. This would make for a better spreadsheet! Try this:

Put the formula =TODAY() in say cell A1 this will be used to reference todays date in ALL your formulas. The reason why we dont nest the function into the others is because TODAY is a volatile function and using it to liberally will cause re-calculation slow dowm.

No in the Column for the person age in years simply use:
=DATEDIF($A$2,$A$1,"y")

Now in the next Column use:

=IF(MONTH($A$1)<MONTH(A2),(12+MONTH(A1)-MONTH(A2)),MONTH(A1)-MONTH(A2))

This will calculate the months.

In both formulas the assumption is that there is a bith-date in cell A2




_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
http://www.microsoftexceltraining.com
This message was edited by Dave Hawley on 2002-03-09 23:22
 
Upvote 0
Try Datedif
Datedif is in Excel but it is only documented in Excel 2000.

in one cell =DATEDIF(A77,TODAY(),"y")
in another =DATEDIF(A77,TODAY(),"ym")

or with named ranges
=DATEDIF(DOB,cMonth,"y")
=DATEDIF(DOB,cMonth,"ym")

in one cell

=DATEDIF(DOB,cMonth,"y")& "y "&DATEDIF(DOB,cMonth,"ym")&"m"

You could also look at =YEARFRAC(DOB,cMonth)

revise references as necessary.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top