Formula Numeric Field

Question

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

donlarson · Answer

Mark,Before you go further, what are the business rules about rolling over PTO?&nbsp; 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?&nbsp; There are places you can have earned 60 days PTO if you are saving them.This would change how you architect your solution.------------------------------Don LarsonPaasporterWestlake OH------------------------------

markcomish · Answer

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

------Original Message------

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

markcomish · Answer

Wanted to see if you had a solution after I provided the additional info.&nbsp; Thank you in advance!Mark------------------------------Mark Comish------------------------------

tommusto · Answer

Try this:&nbsp;If([start date]&lt;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------------------------------

donlarson · Answer

Mark,I am assuming&nbsp;[Start Date] is a Date Field[Days Used] is a Duration Fieldthen my formula numeric is

//&nbsp;Source&nbsp;Control

//&nbsp;&nbsp;Variables

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

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

//&nbsp;UI
$DaysPTORemain
------------------------------Don LarsonPaasporterWestlake OH------------------------------

Forum Discussion

Formula Numeric Field

Related Content

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

Color Code Date Field based on Formula

IF statement changing numeric to text

Percent Formula Help

Formula Url Buttons