Discussions

Expand all | Collapse all

Formula Numeric Field

  • 1.  Formula Numeric Field

    Posted 12-18-2019 13:49
    Looking for some help with a formula for a Formula Numeric field.

    So we give full-time employees 14 paid days off per year.  So if they work the full year it is easy to calculate how many days off they get 14 but if they we hired 9-1-19 they would only get 5 (we only do full days or half days so we round up to the nearest half day if not a whole number)

    So I have a formula numeric field tat is simply:   14-[Days Used] to calculate the remaining days.

    What I need is if their [start date] is on or before Jan 1 of the current year then 14-[Days Used], If [start date] is after Jan 1 of current year then [start date] divided by number of months remaining in the current year then rounded up to the half day (if it is not a whole number) -[days used]

    Hope this makes sense.

    ------------------------------
    Mark Comish
    ------------------------------


  • 2.  RE: Formula Numeric Field

    Posted 12-18-2019 14:31
    Mark,

    Before you go further, what are the business rules about rolling over PTO?  If an employee goes all year and [Days Used]=0, what is the correct answer on January 1st the next year?

    Is it 14?
    Does it drop to zero?
    Is there a cap on total amount you can accrue?  There are places you can have earned 60 days PTO if you are saving them.

    This would change how you architect your solution.

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



  • 3.  RE: Formula Numeric Field

    Posted 12-18-2019 14:47

    There is no rolling over, saving, accruing of PTO (Flat 14 days per year, if you were employed the entire year).  So each Jan 1 everyone with a start date prior to that day would be reset to 14.

     

     

    Regards,

     

    Mark D. Comish

    Director

    mark@focus1associates.com  

    Direct: (541) 200-7005

     






  • 4.  RE: Formula Numeric Field

    Posted 25 days ago
    Wanted to see if you had a solution after I provided the additional info.  Thank you in advance!

    Mark

    ------------------------------
    Mark Comish
    ------------------------------



  • 5.  RE: Formula Numeric Field

    Posted 24 days ago
    Try this: 

    If([start date]<FirstDayOfYear(Today()),14-[Days Used],Round((If(IsLeapYear([start date]),366,365)-DayOfYear([start date])-1)/If(IsLeapYear([start date]),366,365)*14,0.5)-[Days Used])

    I briefly tested it and it looks like it's working correctly.

    ------------------------------
    -Tom
    ------------------------------



  • 6.  RE: Formula Numeric Field

    Posted 21 days ago
    Thank you very much Tom!

    ------------------------------
    Mark Comish
    ------------------------------



  • 7.  RE: Formula Numeric Field

    Posted 24 days ago
    Mark,

    I am assuming 

    [Start Date] is a Date Field
    [Days Used] is a Duration Field


    then my formula numeric is


    // Source Control

    //  Variables

    var number CalendarDays = (365 - DayOfYear([Start Date])); 
    var number DaysThisYear = if ( [Start Date]FirstDayOfYear(Today()), 14, 
                Round$CalendarDays/365*14,.5)  );

    var number DaysPTORemain = $DaysThisYear-ToNumber(ToDays([Days Used]));

    // UI
    $DaysPTORemain


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



  • 8.  RE: Formula Numeric Field

    Posted 21 days ago
    Thank you Don!

    ------------------------------
    Mark Comish
    ------------------------------