Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default year on year tracking

many thanks to Luke for a quick and informative answer. It gives me the basis
of what i want. What i should have said is that the ages of the children need
to be in years and months (or simply months) so that a more exact measure can
be provided between age and assessed age. I could simply type in the months
as a figure after working it out separately but i have been trying the year
and month functions to do it for me and not quite getting it right. if i
could and then fit it into lukes solution i think i may have it. EG. Child
born on say 01/01/2000 takes a test on 03/03/2009 and gets an assessed
reading age of 8 years 2 months (actual age 9 years 3 months). Next test is
on 04/03/2010 and he/she gets 10 years 2 months assessed age ( actual age 10
years 3 months) so the child has improved and the measure of the improvement
is the change in the difference between actual and assessed year on year Can
you help further
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default year on year tracking

If you are looking at finding difference in months between 2 dates use
DATEDIF() function

with start date in A1 and end date in B1
=DATEDIF(A1,B1,"m")

OR
Put DOB in A1 and
In B1 enter the below formula
=IF(A1+0=0,"",DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&"
months " & DATEDIF(A1,B1,"md")&" days")


'Age upto today with DOB in A1
=IF(A1+0=0,"",DATEDIF(A1,TODAY(),"y")&" years "&DATEDIF(A1,TODAY(),"ym")&"
months " & DATEDIF(A1,TODAY(),"md")&" days")

--
If this post helps click Yes
---------------
Jacob Skaria


"Tony Chapman" wrote:

many thanks to Luke for a quick and informative answer. It gives me the basis
of what i want. What i should have said is that the ages of the children need
to be in years and months (or simply months) so that a more exact measure can
be provided between age and assessed age. I could simply type in the months
as a figure after working it out separately but i have been trying the year
and month functions to do it for me and not quite getting it right. if i
could and then fit it into lukes solution i think i may have it. EG. Child
born on say 01/01/2000 takes a test on 03/03/2009 and gets an assessed
reading age of 8 years 2 months (actual age 9 years 3 months). Next test is
on 04/03/2010 and he/she gets 10 years 2 months assessed age ( actual age 10
years 3 months) so the child has improved and the measure of the improvement
is the change in the difference between actual and assessed year on year Can
you help further

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default year on year tracking

Hi
Try
=DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months
"&DATEDIF(D5,E5,"md")&" days"

Change D and E to fit your needs

"Tony Chapman" wrote:

many thanks to Luke for a quick and informative answer. It gives me the basis
of what i want. What i should have said is that the ages of the children need
to be in years and months (or simply months) so that a more exact measure can
be provided between age and assessed age. I could simply type in the months
as a figure after working it out separately but i have been trying the year
and month functions to do it for me and not quite getting it right. if i
could and then fit it into lukes solution i think i may have it. EG. Child
born on say 01/01/2000 takes a test on 03/03/2009 and gets an assessed
reading age of 8 years 2 months (actual age 9 years 3 months). Next test is
on 04/03/2010 and he/she gets 10 years 2 months assessed age ( actual age 10
years 3 months) so the child has improved and the measure of the improvement
is the change in the difference between actual and assessed year on year Can
you help further

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default year on year tracking

brilliant. thank you very much. calculations of ages are spot on - though i
didn't need the days bit.

"Jacob Skaria" wrote:

If you are looking at finding difference in months between 2 dates use
DATEDIF() function

with start date in A1 and end date in B1
=DATEDIF(A1,B1,"m")

OR
Put DOB in A1 and
In B1 enter the below formula
=IF(A1+0=0,"",DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&"
months " & DATEDIF(A1,B1,"md")&" days")


'Age upto today with DOB in A1
=IF(A1+0=0,"",DATEDIF(A1,TODAY(),"y")&" years "&DATEDIF(A1,TODAY(),"ym")&"
months " & DATEDIF(A1,TODAY(),"md")&" days")

--
If this post helps click Yes
---------------
Jacob Skaria


"Tony Chapman" wrote:

many thanks to Luke for a quick and informative answer. It gives me the basis
of what i want. What i should have said is that the ages of the children need
to be in years and months (or simply months) so that a more exact measure can
be provided between age and assessed age. I could simply type in the months
as a figure after working it out separately but i have been trying the year
and month functions to do it for me and not quite getting it right. if i
could and then fit it into lukes solution i think i may have it. EG. Child
born on say 01/01/2000 takes a test on 03/03/2009 and gets an assessed
reading age of 8 years 2 months (actual age 9 years 3 months). Next test is
on 04/03/2010 and he/she gets 10 years 2 months assessed age ( actual age 10
years 3 months) so the child has improved and the measure of the improvement
is the change in the difference between actual and assessed year on year Can
you help further

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default year on year tracking

many thanks it works out the ages brilliantly

"Eduardo" wrote:

Hi
Try
=DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months
"&DATEDIF(D5,E5,"md")&" days"

Change D and E to fit your needs

"Tony Chapman" wrote:

many thanks to Luke for a quick and informative answer. It gives me the basis
of what i want. What i should have said is that the ages of the children need
to be in years and months (or simply months) so that a more exact measure can
be provided between age and assessed age. I could simply type in the months
as a figure after working it out separately but i have been trying the year
and month functions to do it for me and not quite getting it right. if i
could and then fit it into lukes solution i think i may have it. EG. Child
born on say 01/01/2000 takes a test on 03/03/2009 and gets an assessed
reading age of 8 years 2 months (actual age 9 years 3 months). Next test is
on 04/03/2010 and he/she gets 10 years 2 months assessed age ( actual age 10
years 3 months) so the child has improved and the measure of the improvement
is the change in the difference between actual and assessed year on year Can
you help further

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
year on year tracking Tony Chapman Excel Discussion (Misc queries) 1 June 23rd 09 02:02 PM
Combination Graph with current year and prior year sales JanW Charts and Charting in Excel 2 April 5th 07 09:20 PM
how to calculate $1000/year at 5% for 25 years showing each year HELP! Setting up and Configuration of Excel 0 July 25th 06 12:58 AM
Need to create attendance tracking based on % and 2080 hours/year Choateb Excel Discussion (Misc queries) 0 July 10th 06 08:03 PM
How to compare current year to prior year in bar chart? substring Charts and Charting in Excel 4 May 12th 05 07:04 PM


All times are GMT +1. The time now is 04:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"