Discussions

Back to discussions
Expand all | Collapse all

Calculate Years of Employee between a date/time field and today

  • 1.  Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    I am trying to develop a field that automatically calculates the years of employment (including months & days) using a date/time field (Date of Hire) and whatever day it is currently.  

    Thank you in advance!

    ------------------------------
    Laura Diak
    ------------------------------


  • 2.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    Laura,

    This is a two part projcet.

    You need a Formula Duration field.   

    • Today()-[Date of Hire]

    That gives you your first answer and can be used in reporting for seniority, total employment time and so on.

    Then you need a Formula Text field to convert the Duration to each component of Years, Months and Days.  This you will want to put on Forms and specialty reports where no further calculations are to be performed on the data.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------

    Original Message


  • 3.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    Thank you, Don.

    I was able to get the first part working.  How would you suggest I set up the formula for the second part?

    ------------------------------
    Laura Diak
    ------------------------------

    Original Message


  • 4.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    Edited by Don Larson 17 days ago
    Laura,

    You need two tools, conversions and whole set of QB Formulas called Rounding and Truncating


    You can use these to break up the Duration into smaller pieces that can then be displayed as Years, Months and Days.  However we should make two assumptions

    • All years are 365 days long
    • All months are 31 days long
    This is not really true because of leap years and even and odd months.  If you cannot live with these assumptions then this gets much more complicated.

    The easiest part is how many total days someone has been there.

    • ToDays([Duration])
    That gives you a number which can be from zero to really big.  Once this goes over 31 then you have your first month.
    When Total Days goes over 365 we have our first year.  I would do this going backwards from year to month to day

    var number TotalNumDays = ToDays([Duration]);
    var number TotalNumYears = $TotalNumDays/365; // Quick Base does not have a function ToYears() which is a bummer, so we made one

    //Calculate Years to Display
    var number DisplayYears = (Floor($TotalNumYears));  

    //Calculate Months to Display
    var number DisplayMonths= Floor(($TotalNumYears-$DisplayYears)*12);

    // Calculate Days to Display
    var number DisplayDays= $TotalNumDays-$DisplayYears*365 - $DisplayMonths*31;


    //Display
    ToText($DisplayYears) & " Years " & "<br>" & ToText($DisplayMonths) & " Months" & "<br>" & ToText($DisplayDays) & " Days"


    Play with it and you will get some text output.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------

    Original Message


  • 5.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    It worked!  Thank you so very much!

    Happy New Year!

    ------------------------------
    Laura Diak
    ------------------------------

    Original Message


  • 6.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    Unfortunately, there isn't a numeric field type that would produce Years, Months, and Days. You would probably need to either do 3 individual numeric formulas OR a single Text formula.


    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------

    Original Message


  • 7.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    This formula will do years and months. But I or someone would need to get inspired to get the days working too.

    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 RawResult=
    $Years & "yr" & If ($years <> "1","s") & " " &   $Months & "mth" & If($Months<>"1","s");

    If($years<>"" and $Months<>"", $RawResult)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------

    Original Message


  • 8.  RE: Calculate Years of Employee between a date/time field and today

    Posted 17 days ago
    Thank you.

    ------------------------------
    Laura Diak
    ------------------------------

    Original Message