Converting a text time period into a number

[MisTo]

Hi

I've got a list of people's tenure which I need to be in a number format in terms of years. The current format is:

0 years, 2 months, 24 days


I want to express this in terms of years so it this particular example would be:

0.232276

Is there a formula that would allow me to convert between the two without working them all out manually? I've got a list of c.400 to get through...

Thanks in advance!



[TABLE="width: 176"]
<TBODY>[TR]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[sdsurzh]

Hi,

Suppose your date on A1 please apply the following formula.

=DATEDIF(A1,TODAY(),"d")/365

and change the number format to 2 digit.

Thanks,
SK

 

[Robert Mika]

Hi

I've got a list of people's tenure which I need to be in a number format in terms of years. The current format is:

0 years, 2 months, 24 days


I want to express this in terms of years so it this particular example would be:

0.232276

Is there a formula that would allow me to convert between the two without working them all out manually? I've got a list of c.400 to get through...

Thanks in advance!



[TABLE="width: 176"]
<TBODY>[TR]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Hello and welcome
Is this format consistent along all your data?

 

[PaddyD]

Hi - welcome to the board.

Date calcs are often more complex than they should be. In this instance, e.g. if you're calculating decimal fractions of a year, how many days are you using as the devisor? If the 2 months were jan & feb, not same number of days as if they were march & april etc.

 

[sdsurzh]

HI,

You have to enter the join date (14/05/2013) of particular employee in A1 cell. it will work from joining date to today.

Thanks,
SK

 

[MisTo]

Hello and welcome
Is this format consistent along all your data?

Yes it is.

calcs are often more complex than they should be. In this instance, e.g. if you're calculating decimal fractions of a year, how many days are you using as the devisor? If the 2 months were jan & feb, not same number of days as if they were march & april etc.

Basing the calculation on a 365.25 day year - I take your point about months being different lengths...it looks like I will probably need to figure out the start date. If the end date is today is there an easy way to calculate with the data I have?

Thanks

 

[Robert Mika]

Yes it is.



Basing the calculation on a 365.25 day year - I take your point about months being different lengths...it looks like I will probably need to figure out the start date. If the end date is today is there an easy way to calculate with the data I have?

Thanks

Possible but looon winding...
Try this with the dat you have and taking 30 for a month:
=(TRIM(--LEFT(A1,2))*365.25+TRIM(MID(A1,FIND(",",A1)+2,2))*30+TRIM(--MID(SUBSTITUTE(A1,",","@",2),FIND("@",SUBSTITUTE(A1,",","@",2))+2,2)))/365.25