Log In
Register
Home
New Member
November 5, 2019
Offline
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
November 8, 2013
Offline
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.
Active Member
June 16, 2013
Offline
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.
1 Guest(s)
