Formula Text/ Date Wizard Needed
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() funct...
Forum Discussion
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
------------------------------
Thank you for replying Chayce. What you came up with is really impressive.
When inputting the date 01-01-1992 I get the result of "For 31 years, 10 months, 34 days".
Obviously this has to do with the leap yea you mentioned. I will try tinkering with it to see if I can figure it out. It is way further than I was able to do, so either way I am satisfied. Thank you!
------------------------------
Shane Miller
------------------------------
Try this one if you like.
var date MyDate = [My Date];
var text Years = ToText(Year(Today())-Year($MyDate) -
If( Month(Today())<Month($MyDate)
or
(Month(Today())=Month($MyDate) and Day(Today())<Day($MyDate)),1,0));
var text Months = If(
Month(Today()) > Month($MyDate), ToText(Month(Today()) - Month($MyDate)),
Month(Today()) = Month($MyDate) and Day(Today()) >= Day($MyDate), ToText(Month(Today()) - Month($MyDate)),
Month(Today()) = Month($MyDate) and Day(Today()) < Day($MyDate), "11",
ToText(12-(Month($MyDate) - Month(Today()))));
var text DaysDisplay = If ( Day(Today()) > Day($MyDate), ToText( Day(Today()) - Day($MyDate) ),
Day(Today()) = Day($MyDate), "0",
Day(Today()) < Day($MyDate), ToText( Day($MyDate)-Day(Today()) ));
var text RawResult=
$Years & " Year" & If ($Years <> "1","s","") & "<br>" &
$Months & " Month" & If($Months<>"1","s", "") & "<br>" &
$DaysDisplay & " Day" & If($DaysDisplay<>"1","s", "");
If(not IsNull($MyDate), $RawResult)
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------Perfect! Thank you Mark
------------------------------
Shane Miller
------------------------------
