Skip to main content
Question

Formula Numeric Field

  • December 18, 2019
  • 0 replies
  • 52 views
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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+15
  • DonLarson
  • Registered
  • December 18, 2019
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
------------------------------
Forum|alt.badge.img+15
  • DonLarson
  • Registered
  • December 27, 2019
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
------------------------------
Terms & ConditionsAccessibility statement