Discussions

 View Only
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 12-26-2019 20:20
    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 12-27-2019 09:28
    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 12-30-2019 11:07
    Thank you very much Tom!

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



  • 7.  RE: Formula Numeric Field

    Posted 12-27-2019 16:05
    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 12-30-2019 11:08
    Thank you Don!

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



  • 9.  RE: Formula Numeric Field

    Posted 12-11-2020 08:40
    Hello Don,

    Can you please help me, I am trying to do a simple formula by adding # to another # and then dividing it into another (example: Total Complete + Total Exempt / Total Training Assignments) Is there something I am doing wrong in the system because the # comes out very large and it shouldn't?

    1303282+67040/1942651=.70% but the system says 130,328,203.5%

    Here is how I have it in the system:

    Thanks,
    Alan

    ------------------------------
    Alan Orbach
    ------------------------------



  • 10.  RE: Formula Numeric Field

    Posted 12-11-2020 08:47
    Alan,

    In mathematics, Excel and Quick Base, Multiplication and Division take precedence over Addition and Subtraction in the of the order of operations.

    If you want the Addition to take precedence, you will need brackets

    ([Field 1] + [Field B]) / [Field C]


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



  • 11.  RE: Formula Numeric Field

    Posted 12-12-2020 09:31
    Alan,

    Mark is correct.

    There is mnemonic for remembering how math formulas work called PEMDAS.

    Parentheses
    Exponents
    Multiply or Divide
    Add or Subtract


    If you build your formulas with this in mind, evaluating left to right.  Quick Base, Excel and other systems will give you the same answer.



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