Former Member

# Get date in year month days format

Hi Team,

I have a column "Joining date" and we have to calculate employee tenure as of the date we run (currentdate).

and we have to display it in format

eg: if employee has joined on 5 jan 2015 so his tenure should display 1year 4month 2days(example)

I have calculated the daysbetween those dates and got some number

any idea on how to display it in the format?

Regards,

Akshat

10|10000 characters needed characters exceeded

Former Member
Apr 07, 2016 at 05:07 AM

=Year ([CurrDate]) - Year ([BDate]) - If ( [CurrDate] <[BDate];1 ; 0 )+" Years "+(Mod (((MonthNumberOfYear([CurrDate]) - MonthNumberOfYear([BDate])) + 12) - If ( DayNumberOfMonth([CurrDate]) < DayNumberOfMonth([BDate]) ; 1 ; 0 ); 12 ))+" Months "+(DayNumberOfYear([CurrDate]) - DayNumberOfYear( [BDate]) + If ( DayNumberOfYear([CurrDate]) >= DayNumberOfYear( [BDate]) ; 0 ; If ( DayNumberOfYear( [CurrDate]) - DayNumberOfYear([CurrDate]) < DayNumberOfYear( [BDate]) ; DayNumberOfYear( [BDate]) ; DayNumberOfYear( [CurrDate])- DayNumberOfYear( [CurrDate]) ) ))+" Days"

Replace [CurrDate] with CurrentDate() and [BDate] with [HireDate] in your case.

I have tested it against multiple dates working fine and also considering Leap Year.

Regards

Niraj

10|10000 characters needed characters exceeded
• Former Member Former Member

resolved the issue ðŸ˜Š

• Apr 06, 2016 at 04:58 PM

Hi Akshat,

Logic is

1.convert your dates to yyyyMMdd format

2.subtract them . Keep the result always in 6 digits format.

3.First 2 digits is year , next 2 digits modulus with 22 gives you month , last 2 digits modulus with 70 gives you days.

I feel this as lengthy and not fool proof .

Variables in Webi

Curentdate =ToNumber(FormatDate(CurrentDate();"yyyyMMdd"))

Joining dateDate =ToNumber(FormatDate(ToDate([Hire Date];"M/dd/yyyy");"yyyyMMdd"))

Days_Final =FormatNumber(([Curentdate]-[Joining dateDate]);"000000")

Days_Finalformat =([Curentdate]-[Joining dateDate])

Years =Substr([Days_Final];1;2) + " Years"

Months =Mod(ToNumber(Substr([Days_Final];3;2));22)+" Months"

Days =Mod(ToNumber(Substr([Days_Final];5;2));70) +" Days"

Give it a try. ðŸ˜Š

10|10000 characters needed characters exceeded
• Former Member
Apr 06, 2016 at 01:10 PM

Hi,

in which format you want to display it ?

Regards,

Rogerio