Hi Simone,

It makes sense, but not too much I'm afraid, mixing text with calculations is usually a bad choice.

The difference can be calculated with a formula like this:

=LEFT(B1,FIND("y",B1)-1)+SUBSTITUTE(RIGHT(B1,LEN(B1)-FIND("y",B1)),"m","")/12-(LEFT(A1,FIND("y",A1)-1)+SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("y",A1)),"m","")/12)

As you can see, it's not easy to break the text to make calculations, you should use numbers and format them to display in other formats.

39.3333 is 39+4/12, can be displayed as 39 y 4/12 m with this custom format: ## "y" #/12 "m"

The cell holds a real numeric value that you can make calculations with, much easier in next calculations.

Simone Evans said
Hi all, 

I am looking for some help with 2 formulas please, first I need to work out the difference in the XyXm format between the information in the column SA against AA.   So for this versions 39y4m - 36y4m, but I need this in in formula to be able to copy it for all pupils.  The AA is a formula I input based on the dated 20/08/2019 - 29/03/1983 and then SA  is manual input based on a test from another system that I manual transfer across.

Pupil	Date of Birth	August	20/08/2019
	dd/mm/yyyy	AA	SA	DIFF
Simone Evans	29/03/1983	36y4m	39y4m	I need help here!

This test will be conducted 4 times over the year, so my second formula I would like to add is the impact from the second time this test is conducted.  I know there AA and SA would need to be calculated, but we would also need to take into account the difference from the August test.

Pupil	Date of Birth	August	20/08/2019		Nov	15/11/2019
	dd/mm/yyyy	AA	SA	DIFF	AA	SA	I
Simone Evans	29/03/1983	36y4m	39y4m	formula 1	36y10m	39y11m	????

 

I hope this makes sense!  Please ask any questions.

 

Thanks,  Simone   

DATEDIF FUNCTION

DATE MUST IN THE FORMAT : MM/DD/YYYY

DATE1-LAST DATE

DATE2-PRESENT DATE

CASE-I

DATEDIF(DATE1,DATE2,Y)

Note:- date1 to date2 count of no of years.