Skip to Content
avatar image
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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 07, 2016 at 05:07 AM

    Please use below

    =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

    Add comment
    10|10000 characters needed characters exceeded

  • 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. 😊

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 06, 2016 at 01:10 PM

    Hi,

    in which format you want to display it ?

    Regards,

    Rogerio

    Add comment
    10|10000 characters needed characters exceeded