Forum Discussion
BlakeHarrison
5 years agoQrew Captain
To me, the easiest thing would be to break it down into smaller bits:
------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------
- Set the Units on your Duration field properties from 'Smart Units' to 'HH:MM'
- Use WeekdaySub to get the # of Weekdays between the two dates
- Calculate the number of minutes between the starting value and midnight of that day, if the Starting value is a weekday
- Calculate the number of minutes between 00:01 and the ending value, if the Ending value is a weekday
- Add 2, 3, and 4 together
------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------
NicoleVarner
5 years agoQrew Member
Can you please show me what this would look like in a formula?
------------------------------
Nicole Varner
------------------------------
------------------------------
Nicole Varner
------------------------------
- NicoleVarner5 years agoQrew MemberWhen I use a duration formula, it will not let me use the WeekdaySub and to get HH:MM you have to use a duration formula. When I try and use a numeric formula the HH:MM is not an option.
------------------------------
Nicole Varner
------------------------------- NicoleVarner5 years agoQrew MemberHere is the solution to this issue:
I created a field that would pull the day of the week from the Assignment field then created another field that would pull the day of the week from the 1st Disposition Time/Date field.
(Text formula field type:
Case(DayOfWeek((ToDate([Datefield]))),1,"Monday",2,"Tuedsay",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")
In my Assign to initial contact w/in 24 hours field this is how I created my formula: (Duration formula type)
I only want it to take the weekend out if the entry was assigned on a Friday and the person assigned didn't work the entry until Monday (first part of the formula), otherwise it should calculate as normal. The -Hours(48) removes the weekend hours.
------------------------------
Nicole Varner
------------------------------- AustinK5 years agoQrew CommanderWhat would happen on weeks where Monday was a holiday? Presidents Day, Labor Day, etc. I believe that would break the formula on those days and cause a wrong calculation. Or even if the user called out sick on Monday and they were assigned the project on Friday, this would then calculate the weekends for that task assuming they started it during that week on anything but a Monday.
I don't have the answer here for that formula but just some things I noticed and thought would be worthwhile to bring up. This takes it much deeper if you had to bring in the timecard and holidays.