Add leading zeros to Datedif function in Google Sheets
I have a simple function that shows me data with Datedif in Google Sheets. The function shows me the years and months and days remaining between 2 dates. It also shows me the total number of days between the dates. I want to keep the syntax and look of the results the same by adding leading zeros to the function (for example, showing 05 for May, instead of 5, keeps the results consistent).
Here is the function:
=DATEDIF(TODAY(),D28,"Y")&"Y "&DATEDIF(TODAY(),D28,"YM")&"M "&DATEDIF(TODAY(),D28,"MD")&"D ("&DATEDIF(TODAY(),D28,"D")&")"
The results may look like this:
1Y 1M 22D (416)
2Y 10M 11D (1045)
7Y 8M 30D (2829)
22Y 11M 7D (8377)
29Y 7M 6D (10811)
I want it to look like this:
01Y 01M 22D (416)
02Y 10M 11D (1045)
07Y 08M 30D (2829)
22Y 11M 07D (8377)
29Y 07M 06D (10811)
I am sure there is a simple way to accomplish this, but I could not figure out the combination.
You want to retain leading zeros in your calculations.
Cell formatting doesn't work because the value in the cell is text, not a number. So, convert the the value to text using TEXT and use the "format" attribute to format the the number in the format the number in the formula.
The following formula assumes that the reference date is in cell B11.
=TEXT(DATEDIF(TODAY(),$B$11,"Y"),"00")&"Y "&TEXT(DATEDIF(TODAY(),$B$11,"YM"),"00")&"M "&TEXT(DATEDIF(TODAY(),$B$11,"MD"),"00")&"D ("&DATEDIF(TODAY(),$B$11,"D")&")"
