Converting cell data with a formula

[Devilfish2006k]

If there is data in a cell that is formatted as '3yrs 6ms 23ds' and links to another cell in another workbook and I want the cell in the other workbook to display this period in days i.e. 1302, what formulae would you use? Many thanks

 

[Yongle]

what are the values in the source workbook for....

3 years 0 months 0 days
0 years 3 months 0 days
0 years 0 months 3 days
3 years 0 months 3 days
3 years 3 months 0 days

 

[Devilfish2006k]

Sorry I don't understand the question

 

[Yongle]

Q1 Is the cell value a number formatted as described or does the cell contain text?

Q2 If the cell contains TEXT then I need to know how zeros are treated in the source workbook

3 years 0 months 0 days
Is it
3yrs
or
3yrs 0 ms 0ds

same for these
0 years 3 months 0 days
0 years 0 months 3 days
3 years 0 months 3 days
3 years 3 months 0 days

If the cell value is a number then ignore Q2

 

[Devilfish2006k]

The source cell is general format and presents as 3yrs 6ms 23ds. Does that help? Many thanks

 

[Devilfish2006k]

Sorry it is a text cell formatted as 3yrs 0 ms 0ds

 

[Yongle]

Not exactly

Your reply suggests that the value in the cell is text (ie not a number and not the result of a formula)
- how are the 6 items listed in post#4 Q2 displayed?

 

[Yongle]

Thanks
(our posts crossed)

 

[Yongle]

try these formulas in source file first to see if it provides the correct result
- assumes all data has this structure
[number][yrs][space][number][ms][space][number][ds]

years to days
=LEFT(A1,FIND(" ",A1)-4)*365

months to days
=RIGHT(LEFT(A1,SEARCH("ms",A1)-1),2)*30

days
=SUBSTITUTE(RIGHT(A1,4),"ds","")*1
(yes it is necessary to multiply by 1 )

total days (add them together)
=LEFT(A1,FIND(" ",A1)-4)*365+(LEFT(A1,SEARCH("ms",A1)-1),2)*30+SUBSTITUTE(RIGHT(A1,4),"ds","")*1

(You could use 365.25 and 30.5 as multipliers and then ROUND the result to Zero decimal places to be more precise)

 

[Devilfish2006k]

Many thanks, this was how I was approaching it!