I’m using the DATEIF function to determine difference between to dates: Table 1:Start Date and dates in Table 2. However, I need the displayed format for years, months and days in 2-digits (03 years, 01 months, 02 days). Is this possible? Thank you
MacBook Pro 15″, macOS 11.7
You can do something like this:
=REPT(0,2−LEN(DATEDIF(Start Date::$A$1,A2,"Y")))&DATEDIF(Start Date::$A$1,A2,"Y")& " years, " &
REPT(0,2−LEN(DATEDIF(Start Date::$A$1,A2,"YM")))& DATEDIF(Start Date::$A$1,A2,"YM")&" months, " &
REPT(0,2−LEN(DATEDIF(Start Date::$A$1,A2,"MD")))& DATEDIF(Start Date::$A$1,A2,"MD")& " days"
More on REPT here (or at Help > Numbers Help in your menu:
Use ; instead of , in the formula if your region uses , as the decimal separator.
SG
Thank you SG. I would have never figured that out. I was not familiar with the REPT function. I tried a combination of TEXTAFTER and DATEVALUE, but I could never get things correct. Again, thank you for your help.
Is it possible change the display format between two dates as 2 digit years, months and days