Forum Discussion

ShaneMiller's avatar
ShaneMiller
Qrew Trainee
11 months ago

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() 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's avatar
    ChayceDuncan
    Qrew Captain
    11 months ago

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

    • ShaneMiller's avatar
      ShaneMiller
      Qrew Trainee
      11 months ago

      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        11 months ago

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