Hello
Jululian,
To get the total days from year, month, week, day, and time you can use the following formulas:
Generic Formula:
=(1*365) + (2*30) + (3*7) + 5 + (16/24) + (47/1440) + (10/86400)
View attachment 1488
Combined Formula:
=((VALUE(MID(A3, 1, FIND(" Year", A3) - 1)) * 365) +
(VALUE(MID(A3, FIND(" Year", A3) + 6, FIND(" Month", A3) - FIND(" Year", A3) - 6)) * 30) +
(VALUE(MID(A3, FIND(" Month", A3) + 7, FIND(" Week", A3) - FIND(" Month", A3) - 7)) * 7) +
(VALUE(MID(A3, FIND(" Week", A3) + 6, FIND(" Day", A3) - FIND(" Week", A3) - 6))) +
(VALUE(MID(A3, FIND(" Day", A3) + 5, FIND(" Hour", A3) - FIND(" Day", A3) - 5)) / 24) +
(VALUE(MID(A3, FIND(" Hour", A3) + 6, FIND(" Minute", A3) - FIND(" Hour", A3) - 6)) / 1440) +
(VALUE(MID(A3, FIND(" Minute", A3) + 8, FIND(" Second", A3) - FIND(" Minute", A3) - 8)) / 86400))
View attachment 1489
Format:
=DATEDIF(0,C3,"y") & "yr "&DATEDIF(0,C3,"ym")&"m "&DATEDIF(0,C3,"MD")&"d "& TEXT(MOD(C3,1),"hh:mm:ss")
View attachment 1490
Download the Excel file: