Get date in year month days format

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

@Niraj

You rock dude
Thanks a lot

also Sateesh thanks a lot for your help

[sateesh_kumar1 · Accepted solution]

HI Akshat ,

It's better to split the whole formula into multiple variables for better processing and some tweak at days logic.

test it out for multiple cases .

Good logic Niraj

[Former Member · Accepted solution]

Thanks Sateesh for suggesting best practice in above solution.

I have tested the logic with random multiple dates and seems to be working fine till now but suggestions are always welcome.

Regards

Niraj

[Former Member · Accepted solution]

Yup that's true
i created variables with the same formulas and it's working

Regards,

Akshat

[Former Member · Accepted solution]

HI Niraj,

its very Good logic its working charm. but it would be very helpful if you could explain formula how you achieve this thing

=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"

Y you use mod functions and which calculations you used if you could explain brief it would be helpful to people and they can understand in better way

Thanks,

Varun

[Former Member · Accepted solution]

Though it's not the right group to ask but it's a high priority issue so any help would be appreciated

Having some issue with nofilter

I have a block having filter say "revenue is nulll" and i want another row just below the block which gets data for "revenue is not null"

in order to do , i added table footer to the block and created a metric using nofilter to get data

nofilter(([Profit]) where [revenue] is not null)

I am getting values but realised that they are incorect

Now if i add a new block with the filters and try to run a metric then it displays blank value (maybe metric is dependent on some dimension, i know the dim name too)

i used foreach for the same but no luck

any idea on how to do it

my condition in header and footer is exactly opposite

[Former Member · Accepted solution]

resolved the issue

[sateesh_kumar1]

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.

[Former Member]

Hi,

in which format you want to display it ?

Regards,

Rogerio

[Former Member]

in format

1 year 1month 2 days