Forum Discussion
DATEDIF Function for calculation years, months, and days between dates
- Anonymous1 year ago
ok, I progrees a bit ....
I added the code below to the new column in the table1
Final2 =
VAR _Years = FLOOR(YEARFRAC(today(),[MinDefinitiveJudgmentDate],4),1)
VAR _Months = FLOOR(MOD(YEARFRAC(today(),[MinDefinitiveJudgmentDate],4),1) * 12.0,1)
VAR _daysinMonth = DAY(EOMONTH(today(),0))
VAR _days = IF(DAY(today())<DAY([MinDefinitiveJudgmentDate]),DAY([MinDefinitiveJudgmentDate])-DAY(today()),DAY([MinDefinitiveJudgmentDate])+(_daysinMonth-DAY(today())))
VAR _daysFinal = SWITCH(TRUE(),DAY(EOMONTH(today(),0)) = _days || DAY(EOMONTH([MinDefinitiveJudgmentDate],0)) = _days,0,_days)RETURN SWITCH(TRUE(),
_Years>0 && _Months = 0 && _daysFinal = 0,_Years & " year(s)",
_Years>0 && _Months>0 && _daysFinal = 0, _Years & " year(s), " & _Months & " month(s) ",
_Years>0 && _Months >0 && _daysFinal >0, _Years & " year(s), " & _Months & " month(s) " & _daysFinal & " day(s)",
_Years>0 && _Months = 0 && _daysFinal >0, _Years & " year(s), " & _daysFinal & " day(s)",
_Months>0 && _daysFinal = 0, _Months & " month(s) ",
_Months>0 && _daysFinal >0, _Months & " month(s) " & _daysFinal & " day(s)",
_daysFinal & " day(s)"
)...and seems like some of them are correct and some of them are incorrect in the last column..
- Anonymous1 year ago
I sorted ther error was incorrect colulmn name but that code above is excellent.
I sorted ther error was incorrect colulmn name but that code above is excellent.