Excel Convert Days to Years Months Weeks Days

This is a post about the Microsoft Excel formula to convert a number like 570 to text string like 1 year 6 months 3 weeks 2 days.

Introduction

In my various excel sheets being in use for financial, statistical or such, often times I have a cell with a number of days; i.e. how many days remaining till payment or maturity or expiry etc; & if it says like 156, 327, 198; I wanted it to be in better human readable format like in years, months, weeks, days.

Formula

Assuming your number is in cell A1, Formula is =TRIM(IF(DATEDIF(0,A1,"Y")=0, "", DATEDIF(0,A1,"Y")&"y ")&IF(DATEDIF(0,A1,"YM")=0, "", DATEDIF(0,A1,"YM")&"m ")&IF(INT((DATEDIF(0,A1,"MD")/7))=0, "", " "&INT((DATEDIF(0,A1,"MD")/7))&"w ")&IF(DATEDIF(0,(A1-(INT((DATEDIF(0,A1,"MD")/7)))*7),"MD")=0, "", " "&DATEDIF(0,(A1-(INT((DATEDIF(0,A1,"MD")/7)))*7),"MD")&"d "))

Pretty Print:

=TRIM(
	IF(DATEDIF(0,A1,"Y")=0,
		 "",
		 DATEDIF(0,A1,"Y")&"y "
	 )
	&IF(DATEDIF(0,A1,"YM")=0,
		 "",
		 DATEDIF(0,A1,"YM")&"m "
	 )
	&IF(INT((DATEDIF(0,A1,"MD")/7))=0,
		"", 
		" "&INT((DATEDIF(0,A1,"MD")/7))&"w "
	)
	&IF(DATEDIF(0,(A1-(INT((DATEDIF(0,A1,"MD")/7)))*7),"MD")=0,
		"",
		" "&DATEDIF(0,(A1-(INT((DATEDIF(0,A1,"MD")/7)))*7),"MD")&"d "
	)
)

Requirements

Formula should be fed a single positive number, & it should give out a string as 1y 4m 2w 8d whereas y is year, m is months, 2 is weeks, 8 is days. If any unit is not there, i.e. 65 outputs as 2m 3d; formula should not give 0y 0w. Any unit with 0 should be ignored.

Formulas Used

I have used the formula DATEDIF, which takes a start date, end date & unit type. Either you can really supply the dates like 2021/01/25 & 2022/08/24 as start & end dates; & formula will subtract both & get the number of days; or you can simply pass on a number like 150 as start, 0 as end; & it will be same result.

Unit types are Y, M, D, MD, YM, YD. Y gives out the years of 365 days each & ignores the remainder of days. 370 & 440 both will give 1 as output. M will give months of 31 days each, ignoring the remainder of days. 35 & 40 both will give output as 1. D will give Days.

MD will give the remainder of days after ignoring Months & Years from the number. 65 will output as 3 (65%31). YM will give months ignoring years & days, YD will give days after ignoring years only.

Explanation:

To get Years

IF(DATEDIF(0,A1,"Y")=0,"",DATEDIF(0,A1,"Y")&"y ") checks if A1 has output unit Y is zero, if yes, it simply outputs a empty string as "" otherwise it gives out the number of years in number & adds a y & a space to it, like 2y.

To get Months

&IF(DATEDIF(0,A1,"YM")=0, "", DATEDIF(0,A1,"YM")&"m ") The & symbol adds the following to the existing string from before, empty or 5y, & checks if output of number after ignoring years & days is Zero, if true, adds the output as 4m . String now is 2y 4m . Month can never be bigger than 11, because then it will be a year.

To get Weeks

DATEDIF has no inbuilt unit of weeks, but number of weeks can be found by dividing Days by 7, ignoring anything after decimal.

&IF(INT((DATEDIF(0,A1,"MD")/7))=0, "", " "&INT((DATEDIF(0,A1,"MD")/7))&"w ") Again, & adds the output of this one to existing one from before. INT converts anything like 2.5 to 2; gets the integer part. IF checks the output of DATEDIF with MD divided by 7.

MD ignores all Months & Years, we don’t need those because we already processed the Months & Years in last two steps. If the Integer value is Zero, it adds nothing, just an empty string; otherwise it adds that number 3 & w & a space; the string now is 2y 4m 3w . Week can never be bigger than 4 because then it will be a month.

To get Days

&IF(DATEDIF(0, (A1-(INT((DATEDIF(0, A1, "MD")/7)))*7), "MD")=0, "", " "&DATEDIF(0,(A1-INT((DATEDIF(0, A1, "MD")/7)))*7), "MD")&"d ") So now we processed the years, months & weeks. MD will again give us number of remainder days after ignoring Years & Months; but it will still include the days we processed as weeks. So, now we need to find those weeks again, by finding the days, dividing them by 7, getting the integer part, & then multiplying it by 7 & subtracting that number from A1. Then check that if thats Zero, if yes ignore, otherwise add d & a space to it.

Now, every output string has a space suffixed to it; & we don’t want space at the very end of output string; so we TRIM() the string, which removes any spaces from very ends of any string.