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!

 

[Alex Blakenburg]

You are not giving us any visibility of what's going on.
Which formula did you try Post #3 or #5.
Can you provide an XL2BB of some sample data ?
At a minimum make you first result in column H the activecell and give us a picture of what your spreadsheet looks like which includes the row and column references and shows the formula bar of the activecell.

Your example uses row 1, is this really the case row 1 is typically a heading row.

Just in case you have extra spaces in your text length of service fields I have added a Trim to the below.
I have also modified it to be in row 2 in case you have headings.
So copy the below into row 2.
If it doesn't work please provide the above information.

=LET(arr,TEXTSPLIT(TEXTJOIN(";",TRUE,TRIM(H2),TRIM(I2))," ",";"),
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]

Could you share the data you are working with? bb code or upload a file somewhere and share the link here?

 

[felixstraube]

How about this?:

=LET(
a;DIVIDIRTEXTO(H1;" ";;VERDADERO);y_1;ELEGIRCOLS(a;1);m_1;ELEGIRCOLS(a;3);d_1;ELEGIRCOLS(a;5);
b;DIVIDIRTEXTO(I1;" ";;VERDADERO);y_2;SI.ERROR(ELEGIRCOLS(b;1);0);m_2;SI.ERROR(ELEGIRCOLS(b;3);0);d_2;SI.ERROR(ELEGIRCOLS(b;5);0);
y; y_1+y_2+ENTERO((m_1+m_2+ENTERO((d_1+d_2)/30,4375))/12);
m;RESIDUO(m_1+m_2+ENTERO((d_1+d_2)/30,4375);12);
d;ENTERO(RESIDUO(d_1+d_2;30,4375));
t;y & " years " & m & " months " & d & " days";
t)

 

[felixstraube]

Sorry ignore last post. Formula is in spanish.
Here is the correcto one:

=LET(
    a; TEXTSPLIT(H1; " "; ; TRUE); y_1; CHOOSECOLS(a; 1); m_1; CHOOSECOLS(a; 3); d_1; CHOOSECOLS(a; 5);
    b; TEXTSPLIT(I1; " "; ; TRUE); 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
)

 

[myletterboxnp]

Hi all,

Here is the sheet.

 

[myletterboxnp]

Dave's below formula worked.

=(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

Thank you everyone!

 

[Dave Patton]

The formula calculates but it does it yield the correct answer?

It would really help if you provided an extract of your sheet showing
a) how the text you showed was calculated (3 years 2 months 13 days) and
b) what you want to do with the results for example seniority level is X if months = 84.

I show 2 possible solutions,, Review the suggestions with Excel's formula Evaluation.
N.B. I show the individual calculations for one example below the examples.

You can paste my post to a clean sheet. Click on the icon below the f(x) in the heading, move to your sheet, and paste in cell H1.

T202312a.xlsm
	H	I	J	K	L	M	N
1	Term with text information		Days	Years	Months	or YearFrac	or Months
2	3 years 2 months 13 days	3 years 4 months 29 days	2,416.13	6	7.4	6.62	79.38
3	3 years 2 months 13 days	3 years 4 months 29 days	2,416.13	6	7.4	6.62	79.38
4	10 years 11 months 13 days	3 years 10 months 18 days	5,418.44	14	10.0	14.84	178.02
5	10 years 11 months 13 days	3 years 10 months 18 days	5,418.44	14	10.0	14.84	178.02
6
7	4748.25	639.1875	31		5418.44
9a

Cell Formulas
Range	Formula
J2,J4	J2	=LET(a,TEXTSPLIT(H2," ",,1),b,TEXTSPLIT(I2," ",,1),(INDEX(a,1)+INDEX(b,1))*365.25+(INDEX(a,3)+INDEX(b,3))*30.4375+(INDEX(a,5)+INDEX(b,5)))
K2:K5	K2	=INT(J2/365.25)
L2:L5	L2	=(J2-K2*365.25)/30.4375
M2:M5	M2	=YEARFRAC(1,J2,3)
N2:N5	N2	=J2/30.4375
J3,J5	J3	=LET(r,H3:I3,SUM(--TEXTBEFORE(r," "))*365.25+SUM(--(RIGHT(TEXTBEFORE(r," ",3),2)))*30.4375+SUM(--(LEFT(TEXTAFTER(r," ",4),2))))
H7	H7	=SUM(--TEXTBEFORE(H4:I4," "))*365.25
I7	I7	=SUM(--(RIGHT(TEXTBEFORE(H4:I4," ",3),2)))*30.4375
J7	J7	=SUM(--(LEFT(TEXTAFTER(H4:I4," ",4),2)))
L7	L7	=H7+I7+J7

 

[Dave Patton]

T202312a.xlsm
	H	I	J	K	L	M	N
1	Term with text information		Days	Years	Months	or YearFrac	or Months
2	10 years 11 months 13 days	3 years 10 months 18 days	5,418.44	14	10.0	14.84	178.02
3	10 years 11 months 13 days		4,000.31	10	11.4	10.96	131.43
4	3 years 10 months 18 days		1,418.13	3	10.6	3.88	46.59
9aa

Cell Formulas
Range	Formula
J2:J4	J2	=SUM(SUM(TEXTBEFORE(H2," "),IFERROR(TEXTBEFORE(I2," "),0))*365.25,SUM(RIGHT(TEXTBEFORE(H2," ",3),2),IFERROR(RIGHT(TEXTBEFORE(I2," ",3),2),0))*30.4375,SUM(LEFT(TEXTAFTER(H2," ",4),2),IFERROR(LEFT(TEXTAFTER(I2," ",4),2),0)))
K2:K4	K2	=INT(J2/365.25)
L2:L4	L2	=(J2-K2*365.25)/30.4375
M2:M4	M2	=YEARFRAC(1,J2,3)
N2:N4	N2	=J2/30.4375

T202312a.xlsm
	H	I							P
1	Term with text information								Months
2	10 years 11 months 13 days	3 years 10 months 18 days							177
9aa

Cell Formulas
Range	Formula
P2	P2	=DATEDIF(1,SUM(SUM(TEXTBEFORE(H2," "),IFERROR(TEXTBEFORE(I2," "),0))*365.25,SUM(RIGHT(TEXTBEFORE(H2," ",3),2),IFERROR(RIGHT(TEXTBEFORE(I2," ",3),2),0))*30.4375,SUM(LEFT(TEXTAFTER(H2," ",4),2),IFERROR(LEFT(TEXTAFTER(I2," ",4),2),0))),"M")

 

[Dave Patton]

A clearly worded question can yield a quality solution.
The source information may yield the most accurate answer.
N.B. We do not know anything about your sheet except what you tell us.
- what are you trying to achieve
- how is your data set up
- is the date information etc. unambiguous
- what version of Excel are you using

Please answer our questions.

I will provide additional information; I hope that my examples are clear.
The information may help you or help others who read the posts.
N.B. I still do not know what you are trying to achieve and the nature of your data.

1. Read the second example first

TextSplit.xlsm
	A	B	C
6			Days
7	3 years 2 months 13 days	3 years 4 months 29 days	2,416.11
8	3 years 2 months 13 days	3 years 4 months 29 days	2,416.11
9	12 years 10 months 9 days		4,696.35
4a

Cell Formulas
Range	Formula
C7	C7	=(3+3)*365.25+(2+4)*30.4345+(13+29)
C8:C9	C8	=LET(y,365.25,m,30.435,a,TEXTSPLIT(A8," ",,1),b,TEXTSPLIT(B8," ",,1),INDEX(a,1)*y+INDEX(a,3)*m+INDEX(a,5)+IF(B8>"",INDEX(b,1)*y+INDEX(b,3)*m+INDEX(b,5)))

TextSplit.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Start	End	Days		Start 2	Current	Days	Total Days
3		09-Jan-17	27-Jul-20	1296		01-Sep-20	23-Dec-23	1209	2505
4		01-Jan-23	31-Dec-23	365				0	365
5
4aa

Cell Formulas
Range	Formula
H3:H4	H3	=(COUNT(F3:G3)=2)*(G3-F3+1)
I3:I4	I3	=D3+H3
D3:D4	D3	=C3-B3+1

You can put the Lambda function into your workbook. With Name Manager, name the function to your preference and put the data shown in the Value area.
If you must sum the days worked from the text that you posted; review the following.
You can also build your own function to do this. It does not require VBA and it will work throughout your workbook.

TextSplit.xlsm
	A	B	C
1			Days
2	3 years 2 months 13 days	3 years 4 months 29 days	2,416.11
3	12 years 10 months 9 days		4,696.35
4	3 years 6 months 18 days		1,296.36
4a

Cell Formulas
Range	Formula
C2	C2	=SplitToDays(A2,B2)
C3:C4	C3	=SplitToDays(A3)

Lambda Functions
Name	Formula
SplitToDays	=LAMBDA(Date1,[Date2],LET(d_1,Date1,d_2,Date2,y,365.25,m,30.435,a,TEXTSPLIT(d_1," ",,1),b,TEXTSPLIT(d_2," ",,1),INDEX(a,1)*y+INDEX(a,3)*m+INDEX(a,5)+IF(d_2>"",INDEX(b,1)*y+INDEX(b,3)*m+INDEX(b,5))))