[Solved] Get Total Days from Year, Month, Week, Days and Time in Excel

[Jululian]

Dear All

May I kindly request for assist in a excel formula​

How can I correct my formula for missing hours​

Any assistance is greatly appreciated​

Regards Jululian

 

[shamimarita]

Dear All

May I kindly request for assist in a excel formula​

How can I correct my formula for missing hours​

Any assistance is greatly appreciated​

Regards Jululian

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)

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))



Format:
=DATEDIF(0,C3,"y") & "yr "&DATEDIF(0,C3,"ym")&"m "&DATEDIF(0,C3,"MD")&"d "& TEXT(MOD(C3,1),"hh:mm:ss")


Download the Excel file:

 

[Jululian]

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:

I am thankful for your assistance, which has had a significant impact on my work.
many thanks, Mrs. Shamimarita

 

[shamimarita]

I am thankful for your assistance, which has had a significant impact on my work.
many thanks, Mrs. Shamimarita

You are most welcome. Thanks for your appreciation, it means a lot to us.