adding two dates which are in text format (3 years 2 months 13 days) + (3 years 4 months 29 days)

[myletterboxnp]

Column F1 has Hire dates (01/09/2017)
Column G1 has re-hire dates (07/27/2020)
Column H1 has calculated length_of_service_Since_Hire (3 years 2 months 13 days) (text field)
Column I1 has calculated length_of_Service_Since_Re_hire (3 years 4 months 29 days) (text filed)

in Column I1, what formula can calculate the seniority date meaning : (Length_of_Service_Since_Hire+Length_of_Service_Since_Re_Hire)?

Some people don't have re_Hire_date.

Thank you!

 

[SanjayGMusafir]

Column I1 has calculated length_of_Service_Since_Re_hire (3 years 4 months 29 days) (text filed)

in Column I1, what formula can calculate the seniority date meaning : (Length_of_Service_Since_Hire+Length_of_Service_Since_Re_Hire)?

You have given same cell address in both scenarios above? Please explain

Do you want i1 to give higher of the two period? or

you want i1 to give values as they are and want to merge two information in some other cell address?

 

[Alex Blakenburg]

I am sure someone can come up with a simpler solution than this, but in the meantime try this is J1.
I have assumed 31 days in a month to convert days to months.

=LET(arr,TEXTSPLIT(TEXTJOIN(";",TRUE,H1,I1)," ",";"),
origdays,SUM(--INDEX(arr,0,5)),
origmths,SUM(--INDEX(arr,0,3)) + INT(origdays/31),
fyrs,SUM(--INDEX(arr,0,1))+ INT(origmths/12),
fdays, MOD(origdays,31),
fmths,MOD(origmths,12),
fyrs & " years " & fmths & " months " & fdays & " days  ")

 

[felixstraube]

Another option:

Senority dates.xlsx
	F	G	H	I	J	K
1	09/01/2017	27/07/2020	3 years 2 months 13 days	3 years 4 months 29 days		6 years 7 months 10 days
Hoja1

Cell Formulas
Range	Formula
K1	K1	=LET( a,TEXTSPLIT(H1," "),y_1,CHOOSECOLS(a,1),m_1,CHOOSECOLS(a,3),d_1,CHOOSECOLS(a,5), b,TEXTSPLIT(I1," "),y_2,CHOOSECOLS(b,1),m_2,CHOOSECOLS(b,3),d_2,CHOOSECOLS(b,5), t,(EDATE(F1,12*y_1+m_1)+d_1-F1) + IF(G1<>"",EDATE(G1,12*y_2+m_2)+d_2 - G1,0), y,INT(t/365.25), m,INT((t-y*365.25)/30.4375), d,INT(t-y*365.25-m*30.4375), y & " years " & m & " months " & d & " days" )

365.25 are the average days per year in a century (from 2000 to 2100).
30.4375 are the average days per month in a century.

 

[felixstraube]

Sorry ignore the previous post. The formula has some errors in it calculation.

Here is another one that I think work ok:

Senority dates.xlsx
	F	G	H	I	J	K
1	09/01/2017	27/07/2020	3 years 2 months 13 days	3 years 4 months 29 days		6 years 7 months 11 days
Hoja1

Cell Formulas
Range	Formula
K1	K1	=LET( a,TEXTSPLIT(H1," "),y_1,CHOOSECOLS(a,1),m_1,CHOOSECOLS(a,3),d_1,CHOOSECOLS(a,5), b,TEXTSPLIT(I1," "),y_2,IFERROR(CHOOSECOLS(b,1),0),m_2,IFERROR(CHOOSECOLS(b,3),0),d_2,IFERROR(CHOOSECOLS(b,5),0), y, y_1+y_2+INT((m_1+m_2+INT((d_1+d_2)/30.4375))/12), m,MOD(m_1+m_2+INT((d_1+d_2)/30.4375),12), d,INT(MOD(d_1+d_2,30.4375)), t,y & " years " & m & " months " & d & " days", t)

 

[Dave Patton]

What result do you require? Total days? Total years? Total months?

T202312a.xlsm
	F	G	H	I	J
1
2	09-Jan-17	27-Jul-20	3 years 2 months 13 days	3 years 4 months 29 days	6.45
9a

Cell Formulas
Range	Formula
J2	J2	=(DATE(INDEX(TEXTSPLIT(H2," ",,1),1),INDEX(TEXTSPLIT(H2," ",,1),3),INDEX(TEXTSPLIT(H2," ",,1),5))+DATE(INDEX(TEXTSPLIT(I2," ",,1),1),INDEX(TEXTSPLIT(I2," ",,1),3),INDEX(TEXTSPLIT(I2," ",,1),5)))/365

 

[Dave Patton]

Ignore my post above except for the line "What result do you require".

 

[myletterboxnp]

I am looking for the combined total duration eg: 6 years, 7 months and 11 days in the above example.

 

[felixstraube]

the results in post 3 and 5 are what you need then.

 

[myletterboxnp]

I have been trying and it shows #VALUE. What am I doing wrong?