Help a poor SEN teacher out! Year:Month calculations :/

**heisenburg** · 05-24-2013, 02:43 PM

I'm putting together what should be a very simple tracking sheet for monitoring progress of my pupils who are undergoing litercy interventions.

I want to be able to input a baseline data reading age and then at the end of the intervention a re-assessed reading age. I would like the tracker to automatically calculate the difference (progress a pupil has made).

Reading ages are measured in Years:Months (using the colon). For example 06:09 would be Six years and nine months.

Colons are used as time in excel and that's where I'm hitting problems - I don't know how to get around this. If I have a start RA of 10:03 and a second age of 11:04 then I get progress of 01:01 which would represent an increase of 1 year and 1 month. However, if I input a start age of 10:03 and then a second age of 0:58. The calculation currently used is a very simple =sum(A2-A1) i.e 11:04 - 10:03 = 01.01

Ok, I'm sure there's a very simple solution to my problem but I just can't put my finger on it! If you have a solution or a suggestion I'd be super grateful!!!

I've attached what I have in the hope that some super-excel guru can help!

Thank you

**ChemistB** · 05-24-2013, 03:04 PM

Not so easy.

First you're working with a base 12 since there are 12 months in a year. Your second example should give a result of 00:10 and not 00:07 which is what you would assume from 11:01 - 10:03

So, I'm being optimistic here and assuming at worse, it stays the same and never goes down. We could modify if that's not the case.
First make your columns I and L formatted as Text so Excel doesn't see them as Time.

Next, we make a helper column in M. We can hide that later.
In M5, to take care of the base 12 issue;
=IF(RIGHT(L5,2)+0< RIGHT(I5,2)+0, LEFT(L5,2)-1&":"&RIGHT(L5,2)+12,L5) copied down

Then in F5,
=TEXT(LEFT(M5,2)-LEFT(I5,2),"00")&":"&TEXT(RIGHT(M5,2)-RIGHT(I5,2),"00")
See attachment
Is that what you needed?

**shg** · 05-24-2013, 03:10 PM

If you ...

1. Entered all of those values as whole numbers, e.g., 11:04 as 1104

2. Formattted all of those cells as 0\:00

... then in F5, =100*DOLLARFR(DOLLARDE(L5/100, 12) - DOLLARDE(I5/100, 12), 12)

**popipipo** · 05-24-2013, 03:15 PM

Why do you use the colon to seperate years and month?
Excel don't likes that, he thinks that it is a time.
Use a regular notation for dates DD-MM-YYYY or MM-DD-YYYY
Then use this formula

Please Login or Register  to view this content.

**heisenburg** · 05-24-2013, 03:26 PM

Wonderful! I used shg's solution in the end - I can see why you're a Forum Guru!

Thank you both sooooooo much!

Help a poor SEN teacher out! Year:Month calculations :/

LinkBack

Thread Tools

Rate This Thread

Display

Help a poor SEN teacher out! Year:Month calculations :/

Re: Help a poor SEN teacher out! Year:Month calculations :/

Re: Help a poor SEN teacher out! Year:Month calculations :/

Re: Help a poor SEN teacher out! Year:Month calculations :/

Re: Help a poor SEN teacher out! Year:Month calculations :/

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions