Hello I am trying to divide against a value if a specific drop-down field value is selected but can't seem to get the syntax right.
=DAYS360(Beginning_Date, Return_Date),DIV((IF(Type_of_Leave_Request.Half-Day)), 2)
This will remove weekends from you Date Calculations.
=IF(Type_of_Leave_Request="Half-Day",DIV(NETWORKDAYS(Beginning_Date,Return_Date),2),NETWORKDAYS(Beginning_Date,Return_Date))
In your form, you would change the Returning_Date to End_Date (Last day requested off) as the Returning Date is counted as a day, or you would have to subtract a day from the Returning_Date to account for this. For example, if you were taking just the 22nd off, Your Start Day would be the 22nd, your returning date would be the 23rd.
You would only count the 22nd as the day off
NETWORKDAYS can also account for Holidays when that information is available in an array but I've not played with that yet.
Hi Armondo,
I would assign a value to your day_type entries, check which it is set to using those values and divide accordingly (by 1 or 2).
DIV needs to come first since it expects two variables.
=DIV(DAYS360(start,return), IF(day_type = 1, 1,2));
Hi Armando
Another way to handle this. Set the Date Fields as Default to Current Date, and the Day Type default to Half-Day or All-Day or you will see a calculation error then one of the fields is blank.
The formula below is actually one line but the reply box broke it into 2 when it saw the hyphen in Half-Day
=IF(Type_of_Leave_Request="Half-Day",DIV(DATEDIF(Beginning_Date,Return_Date,"D"),2),DATEDIF(Beginning_Date,Return_Date,"D"))
My problem with this approach is that you will need to select the returning date as by default it will show as 0 days.
However what you did post did work.
Ok so the best way I find would be to just hide the calculation field if any of the variables are blank this keeps the errors at bay.
Thanks guys.
So my main problem now is that it calculates weekends as well.
Is there any syntax on OpenFormula we can use to exclude weekends?
If not I guess its a Javascript job.
This will remove weekends from you Date Calculations.
=IF(Type_of_Leave_Request="Half-Day",DIV(NETWORKDAYS(Beginning_Date,Return_Date),2),NETWORKDAYS(Beginning_Date,Return_Date))
In your form, you would change the Returning_Date to End_Date (Last day requested off) as the Returning Date is counted as a day, or you would have to subtract a day from the Returning_Date to account for this. For example, if you were taking just the 22nd off, Your Start Day would be the 22nd, your returning date would be the 23rd.
You would only count the 22nd as the day off
NETWORKDAYS can also account for Holidays when that information is available in an array but I've not played with that yet.
This is very good information Steve,
I tried the noted calculation and while it does neglect the weekends it is not showing the division by 2 with the half days.
Sorted it out the variable name for day_type was different.
This is great, I wonder if we can find more information on setting up the Holiday Array somewhere.