Formula Text/ Date Wizard Needed

Question

Hello, 
I have one date field called "Original Effective Date" that is referring to the first day a client began working with us. I want to have 1 formula text field that utilizes the today() function to say:[Employer ID] has worked with us for _ years, _months, and _days. 
What would be the best way to accomplish this?
------------------------------Shane Miller------------------------------

ChayceDuncan · Answer

This is a pretty down and dirty attempt at it but something like this seems to get me in the ballpark more or less. It doesn't really consider leap years so you may need to try and adjust if that's an issue. I ran some tests and more or less seems to be working.

var date test = [date test];

var number currentMonthDays = Case(Month(Today()),

1,31,

2,28,

3,31,

4,30,

5,31,

6,30,

7,31,

8,31,

9,30,

10,31,

11,30,

12,31,

0);

var number monthGapInit = Month(Today()) - Month($test) + 12;

var number monthGap = If( $monthGapInit = 12, 11, $monthGapInit);

var number yearGap = Year(Today()) - Year($test) - If( $monthGap < 12 and $monthGap != 0, 1, 0);

var date adjusted = AdjustMonth(AdjustYear($test,$yearGap),$monthGap);

var number dayGap = If( $adjusted > Today(), 365 - ToDays($adjusted - Today()), ToDays(Today() - $adjusted));

"For " & $yearGap & " years, " & If($dayGap > 31, -1 + $monthGap, $monthGap) & " months, " & If( $dayGap > 31, $currentMonthDays - Day($adjusted) + Day(Today()), $dayGap) & " days"

------------------------------
Chayce Duncan
------------------------------

Sign up

Login to the community