Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Team,
I want to get Days, Months, Years from one of my date. Admission Date field value will be this format 20201001.
But months is not calculating correctly. Instead of 4 months it will give 8 months. Please help me on this.
=IF
(Today() <= AddYears(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'),1),'0 Years ',
Age(Today(),Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) & ' Years ')
&
IF(MONTH(Today()) = MONTH(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')),'0 Months ',
IF(DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) < DAY(Today()), fabs(MONTH(Today())-Month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))) & ' Months ',
((MONTH(Today()))-Month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')))-1 & ' Months '
))
&
IF(MONTH(Today()) = MONTH(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')),DAY(Today()) - DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) & ' Days',
IF(DAY(Today()) > DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')),DAY(Today()) - DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) & ' Days',
DAY(DATE(Interval(Today() - Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))))+1 & ' Days'
))
Thanks in advance.
Thanks
Try this in your expression-
=div(year(Today())*12+month(Today()) - (year(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))*12
+month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))),12) & ' Years'
&' '&
mod((year(Today())*12)+month(Today())-(year(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))*12+month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))),12) & ' Months'
&' '&
(Today()-AddMonths(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'),((year(Today())*12)+month(Today())) - (year(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))*12
+month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))))) & ' Days'