Change date format (years, days, hours) to days

[Fjura]

Hello,

May I ask if someone knows how I could change this kind of format (years, days, hours) for example: "220 y 344 d 12 h" to a number of days?



So "220 y 344 d 12 h" would be "80644,50"

thanks!

 

[oldbrewer]

would it always be 220 y and not 220y and not 220y344d12h

 

[Fjura]

would it always be 220 y and not 220y and not 220y344d12h

yes, there is a space betwean the numbers and characters all the time.

 

[etaf]

are you assuming 365 days for every year and not including leap years ?

what happens if there are no days or no years
would that be

220 y 0 d 0 h

 

[Fjura]

are you assuming 365 days for every year and not including leap years ?

what happens if there are no days or no years
would that be

220 y 0 d 0 h

365 for all, yea in that case it would be as you wrote.

 

[Mathias Pedersen Heinze]

It seems to me that the biggest challenge you face is to parse the string ans extract the actual numbers from it. If you want a pure cell function solution, you could go with a very complicated string search function, like this (assuming your YDH-string is in A1):

=VALUE(LEFT(A1,FIND("y",A1)-1))*365+VALUE(MID(A1,FIND("y",A1)+2,FIND("d",A1)-FIND("y",A1)-2))+VALUE(MID(A1,FIND("d",A1)+2,LEN(A1)-FIND("d",A1)-3))/24

However, a simpler solution might be to split your column of YDH-strings using the 'Text to Columns' method. If you split it by white space you'll end up with years, days, and hours in three distinct columns which makes the calculations much easier.

 

[etaf]

EDIT - I see exactly the same type of formula has already been suggested


try

=((LEFT(A1,FIND("y",A1,1)-2)*1 )*365)+(MID(A1,FIND("y",A1,1)+1,(FIND("d",A1,1)-1)-(FIND("y",A1,1)+1))*1) + ((MID(A1,(FIND("d",A1,1)+1),LEN(A1)-(FIND("d",A1,1)+1))*1)/24)

Where A1 is the Cell with the value in

 

[Fjura]

thanks guys, those formulas look great but for some reason, I always get this error:
https://support.content.office.net/en-us/media/38b1b648-6844-4b27-80db-638e4b8f225c.png

 

[Prish]

I have slightly different solution if want to try:

If all your data is in Column A
I will highlight column A, select "Text to Columns" from the data Tab
Leave on "Delimited width" and select next
Tick only the "space" delimiter and click next once again
Choose the columns that's have "y", "d" and "h" and select the format "Do not import column (skip)
Set Destination as =$B$1
Click Finish.
You now have years, days and hours in separate columns.
A simple formula in E1 will give you the result you want: =(B1*365)+C1+(D1/24)

 

[Prish]

I have slightly different solution if want to try:

If all your data is in Column A
I will highlight column A, select "Text to Columns" from the data Tab
Select "Delimited" and select next
Tick only the "space" delimiter and click next once again
Choose the columns that's have "y", "d" and "h" and select the format "Do not import column (skip)
Set Destination as =$B$1
Click Finish.
You now have years, days and hours in separate columns.
A simple formula in E1 will give you the result you want: =(B1*365)+C1+(D1/24)