SOLVED
Home

Convert string to number of days

%3CLINGO-SUB%20id%3D%22lingo-sub-1196880%22%20slang%3D%22en-US%22%3EConvert%20string%20to%20number%20of%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1196880%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20share%20excel%20formula%20to%20convert-%20%22%3C%2FP%3E%3CTABLE%20width%3D%22313px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22312px%22%3E1%20years%200%20months%2022%20days%20%3D%20%22387%22%20days%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1196880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197015%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20string%20to%20number%20of%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568837%22%20target%3D%22_blank%22%3E%40arjsharm%3C%2FA%3E%26nbsp%3BI%20guess%20it%20depends%20which%20year%20you%20are%20talking%20about.%20Some%20years%20have%20364%20days%2C%20some%20have%20365%20days.%20So%2C%20it%20will%20be%20either%20365%20%2B%2022%20or%20364%20%2B%2022.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Please share excel formula to convert- "

1 years 0 months 22 days = "387" days 

 

5 Replies
Highlighted

@arjsharm I guess it depends which year you are talking about. Some years have 364 days, some have 365 days. So, it will be either 365 + 22 or 364 + 22.

Highlighted
Solution

Hello @arjsharm,

 

If "years", "months", and "days" are in the same cell such as "1 years 0 months 22 days" then that could be:

=SUM(365*LEFT(A1,FIND(" y",A1)-1),365/12*MID(A1,FIND("rs",A1)+3,FIND(" m",A1)-FIND("rs",A1)-3),--MID(A1,FIND("hs",A1)+3,FIND(" d",A1)-FIND("hs",A1)-3))&" days"

 If "years", "months", and "days" are in the separate cells such as

YearsMonths

Days

1022

Then that could be:

=A1*365+B1*365/12+C1&" days"
Highlighted

@Riny_van_Eekelen in my case the year is 365 days irrespective of leap year days 

Highlighted

@PReagan Thanks for sharing the formula ! It worked!

 

Highlighted

@arjsharm 

 

My pleasure!

Related Conversations