Alternate Options for ADDWEEKDAYS Formula to Incorporate Half Days

[VictoriaLinn]

Hello! I have a scenario where we want to take the out-of-the-box handoff date for a task and create a calculated field that adds the task's planned duration to it. I’ve written the formula below, but I’m running into an issue: since we are using time in the planned duration (doing increments, like 4.5 days), the formula doesn’t seem to account for that properly. Does anyone have suggestions for an alternative way to make this work?

Adjusted Start Date is the Handoff Date and Adjusted End Date is this calculation: ADDWEEKDAYS({handoffDate},({durationMinutes}/480))

[Sven-iX]

Hi @VictoriaLinn 
From the docs:
ADDWEEKDAYS adds the number of weekdays to the date. This expression only adds whole integer values to the date, rounding down. (I know - seems inconsistent because ADDDAYS handles partial days fine)

Maybe ADDHOURS would do the trick? 

[skyehansen · Accepted solution]

I agree with Sven. The ADDWEEKDAYS is working as designed, with whole weekdays rounded down. I did look at the calculated data expressions page, and haven't really seen anything that might be a better option for you -- but you can keep looking, and please share back if we have missed anything.

My initial thought is that you'd have to come up with a mathematical formula to detect the half day (is it only ever half days?) and then separately add those four hours, based on the time of day and what day it is. Taking a completely random example, let's say you have a duration of 4.5 days.

1) figuring out whether there's a half day involved would involve a calculation like: 

is {durationMinutes}/480 = CEIL({durationMinutes}/480). CEIL rounds up, so you're asking if 4.5 = 5 -- if it isn't equal, then you assume a half day.

2) did your "addweekdays" calculation land you on a friday?

is DAYOFWEEK(your calculation) = 6?

3) is it past 1? (assuming 9-5 operations here -- obviously pick a time that is four hours away from your end of day)

is HOUR(your calculation) > 13? If it's equal to 13, is MINUTE(your calculation)>0?

4) is the following day a holiday?

The result of the four fact-finding questions above, would then lead you to do one of the following:

* if it's not past 1, add your half day (ADDHOURS would work) and move on with your life 

* if it's past 1, add your half day and 16 additional hours. 

* if there's a holiday involved, add the half day and 16+24 hours for each holiday.

* if there's a weekend involved, add the half day and 16+48 hours.

For lesser beings, I will say that at this point, if I were forced to make this, I would probably make the initial four questions their own set of calculated fields, so I could just use the one field to say "if DE:1 = true, do XYZ, if DE:2=true do ABC", and so on. Just makes it easier to keep everything tracked without getting confused and overly complex.

[Sven-iX]

<mind blown>

I've always used a duration of full-days, because of the associated headaches. 
What's the reason to use partial days in duration?