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
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.
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:
Copy that formula across as far as you need.Code:B1: =A1+TIME(0,IF(MOD(--TEXT(A1,"[M]"),60)=11,60-11,1),0)
Note: you must format those values as Time.
Is that something you can work with?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks