formatting for ages NOT dates

SadieN · #1 08-11-2008, 01:41 PM

I am trying to create a chart which displays reading ages of pupils (in years and months). All attempts have failed. Obviously the format I am using for the data is based on 10. I want a scale that goes to 12 ( eg 04:10, 04:11, 5:00 and 05:01). There must be a way! Please can someone help.
SadieN

**shg** · #2 08-11-2008, 01:50 PM

If A1 contains a birth date, then

=DOLLARFR(DATEDIF(A1,TODAY(),"m")/12, 12)

... returns x.01 to x.11, where x is the age in years.

Requires the Analysis Toolpak add-in.

**Ron Coderre** · #3 08-11-2008, 02:20 PM

If you want to build a sequential list of years and months,
but formatted like time (eg 4:10, 4:11, 5:00, 5:01...etc)
where 4:10 represents 4 years and 10 months...
Try this:

A1: (a start time......eg 4:00....translated as 4 years and 0 months)
This formula calculates the next sequential "year:month" value:

Code:

B1: =A1+TIME(0,IF(MOD(--TEXT(A1,"[M]"),60)=11,60-11,1),0)

Copy that formula across as far as you need.

Note: you must format those values as Time.

Is that something you can work with?

SadieN · #4 08-14-2008, 06:11 AM

Thank you shg and Ron C. I wasn't sure what to do with shg@s formula or the add-in but I did manage to get the ages with Ron's suggestion.

Many thanks to you both.

SadieN

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)