Trying to Accomplish: I am trying to create a system for tracking total hours logged between 2 Date/Time fields and then subtract any weekend hours from that calculation to arrive at Total Billable Hours.
Approach: I have a field for calculating total number of days between the dates, as well as the total number of weekdays. I plan to subtract the number of weekdays from the total number of days to get the total number of weekend days. I then plan to do (Number of Weekend Days * 24) to get it in hours, and then subtract that from the total hours logged between the 2 Date/Time fields.
Problem: When I place the Start Date on Wed. and the End Date on Sat., I am getting a total number of weekdays as 4 days instead of 3. My formula for Number of Weekdays is "WeekdaySub( [End Date], [Start Date]) +1"
Also, I thought maybe removing the '+1' from the formula might fix my issue, but if I remove the +1 then I get 0 days for a 1 day duration and also it calculates Saturday correctly but instead fails to see Monday as a weekday.
Approach: I have a field for calculating total number of days between the dates, as well as the total number of weekdays. I plan to subtract the number of weekdays from the total number of days to get the total number of weekend days. I then plan to do (Number of Weekend Days * 24) to get it in hours, and then subtract that from the total hours logged between the 2 Date/Time fields.
Problem: When I place the Start Date on Wed. and the End Date on Sat., I am getting a total number of weekdays as 4 days instead of 3. My formula for Number of Weekdays is "WeekdaySub( [End Date], [Start Date]) +1"
Also, I thought maybe removing the '+1' from the formula might fix my issue, but if I remove the +1 then I get 0 days for a 1 day duration and also it calculates Saturday correctly but instead fails to see Monday as a weekday.
 1,222 Points
Posted 7 months ago
 1,406 Points
i encountered a similar issue. Only 16 hours were worked on a day and only a max of 5 hours were worked on Saturdays if at all worked. Saturdays are extremely rare and I haven't got Saturday fully worked out if the time starts or ends on Saturday. We aren't billing from this, just capturing time that equipment is on a bench for configuration. I am going to post the formula but be warned it is ugly but it could help you get on the right path to a solution.
var date startdate = ToDate([Start]);
var date enddate = ToDate([End]);
var number completeduration = ToHours([End][Start]);
var number startdayofweek = DayOfWeek($startdate);
var number enddayofweek = DayOfWeek($enddate);
var TimeOfDay alldaysstart = ToTimeOfDay("07:00");
var TimeofDay weekdaystop = ToTimeOfDay("23:00");
var TimeofDay saturdaystop = ToTimeOfDay("12:00");
// was worked logged on Saturday
var Bool worksaturday = If([Work Saturday]=true,true,false);
// time from noon Saturday to 7am Monday plus 11pm Friday to 7am Saturday 51  now like below minus the 5 from Saturday
var number weekendhours = 43;
// 11pm Friday to 7am Monday 56 midnight saturday start to midnight Monday 48
var number noweekendhours = 48;
//weekend hours to subtract
var number unworkedweekendhours= If($worksaturday=true,$weekendhours,$noweekendhours);
// Length of a workday
var number workhours=16;
// length of nonworkday
var number nonworkhours=8;
// did we start on Saturday?
var Bool startonweekend = If($startdayofweek=6,true,false);
// did we complete work on Saturday
var Bool endonweekend = If($enddayofweek=6,true,false);
var Bool startorendonweekend = If($startonweekend=true,true,If($endonweekend=true,true,false));
// did work happen on sameday?
var Bool sameday = If($startdate=$enddate,true,false);
// number of full days of work **adjusted
var number fulldaysofwork =ToDays($enddate$startdate)1;
// total days of work
var number alldaysofwork =ToDays($enddate$startdate)+1;
var number alldaystotalhours = $alldaysofwork*24;
// hours not worked on start date
var number hoursonstart = 24ToHours(ToTimestamp($startdate,$weekdaystop)[Start]);
// hours not worked on start date saturday
var number saturdayhoursonstart = 24ToHours(ToTimestamp($startdate,$saturdaystop)[Start]);
// hours not worked on end date
var number hoursonend = 24ToHours([End]ToTimestamp($enddate,$alldaysstart));
// combined hours of start and end date hours not worked if not on weekend
var number workdayhoursnotworked = $hoursonstart+$hoursonend;
// combined hours of start and end date hours not worked if on saturday
var number workdayhoursnotworkedsat = If($startorendonweekend=true,$saturdayhoursonstart+$hoursonend,0);
//start and end dates
// Start what day of week
var Bool mstart = If($startdayofweek=1,true,false);
var Bool tustart = If($startdayofweek=2,true,false);
var Bool wstart = If($startdayofweek=3,true,false);
var Bool thstart =If($startdayofweek=4,true,false);
var Bool fstart =If($startdayofweek=5,true,false);
// End what day of the week
var Bool mend = If($enddayofweek=1,true,false);
var Bool tuend = If($enddayofweek=2,true,false);
var Bool wend = If($enddayofweek=3,true,false);
var Bool thend =If($enddayofweek=4,true,false);
var Bool fend =If($enddayofweek=5,true,false);
// not a full week but must remove weekend hours
var Bool meekone = If($mend=true and $tustart=true,true,false);
var Bool meektwo = If($mend=true and $wstart=true,true,false);
var Bool meekthree = If($mend=true and $thstart=true,true,false);
var Bool meekfour = If($mend=true and $fstart=true,true,false);
var Bool meek = If($meekone=true or $meektwo=true or $meekthree=true or $meekfour=true, true, false);
var Bool tueekone = If($tuend=true and $wstart=true,true,false);
var Bool tueektwo = If($tuend=true and $thstart=true,true,false);
var Bool tueekthree = If($tuend=true and $fstart=true,true,false);
var Bool tueek = If($tueekone=true or $tueektwo=true or $tueekthree=true, true, false);
var Bool weekone = If($wend=true and $thstart=true,true,false);
var Bool weektwo = If($wend=true and $fstart=true,true,false);
var Bool week = If($weekone=true or $weektwo=true, true, false);
var Bool theek = If($thend=true and $fstart=true,true,false);
var Bool shortweekremoveweekend = If($meek=true or $tueek=true or $week=true or $theek=true, true,false);
// Calulation to determine the number of weekends
var number fullweeks =
If($alldaysofwork>41,6,
If($alldaysofwork>34,5,
If($alldaysofwork>27,4,
If($alldaysofwork>20,3,
If($alldaysofwork>13,2,
If($alldaysofwork>6,1,0))))));
var number weekenddays= $fullweeks*2;
var Number removeshortweekhours = If($shortweekremoveweekend = true and $worksaturday=false and $fullweeks=0 ,32,0);
var Number startsatremovehours = If ($worksaturday=true and $startonweekend=true, ($weekendhours$saturdayhoursonstart),0);
var Number endsatremovehours = If($worksaturday=true and $endonweekend=true, ($weekendhours$hoursonend),0);
var Number removenonworkedweekendhours = If($startorendonweekend=true, ($fullweeks1)*$weekendhours,$fullweeks*$weekendhours);
If($sameday=true,$completeduration,
If($worksaturday=false, $alldaystotalhours  ($fullweeks*$unworkedweekendhours)  (($fulldaysofwork$weekenddays)*$nonworkhours)$workdayhoursnotworked  $removeshortweekhours
,$alldaystotalhours $startsatremovehours  $endsatremovehours $workdayhoursnotworkedsat (($fulldaysofwork$weekenddays)*$nonworkhours)$removenonworkedweekendhours$workdayhoursnotworked
))
I said it was ugly but there are notes throughout the entire formula
var date startdate = ToDate([Start]);
var date enddate = ToDate([End]);
var number completeduration = ToHours([End][Start]);
var number startdayofweek = DayOfWeek($startdate);
var number enddayofweek = DayOfWeek($enddate);
var TimeOfDay alldaysstart = ToTimeOfDay("07:00");
var TimeofDay weekdaystop = ToTimeOfDay("23:00");
var TimeofDay saturdaystop = ToTimeOfDay("12:00");
// was worked logged on Saturday
var Bool worksaturday = If([Work Saturday]=true,true,false);
// time from noon Saturday to 7am Monday plus 11pm Friday to 7am Saturday 51  now like below minus the 5 from Saturday
var number weekendhours = 43;
// 11pm Friday to 7am Monday 56 midnight saturday start to midnight Monday 48
var number noweekendhours = 48;
//weekend hours to subtract
var number unworkedweekendhours= If($worksaturday=true,$weekendhours,$noweekendhours);
// Length of a workday
var number workhours=16;
// length of nonworkday
var number nonworkhours=8;
// did we start on Saturday?
var Bool startonweekend = If($startdayofweek=6,true,false);
// did we complete work on Saturday
var Bool endonweekend = If($enddayofweek=6,true,false);
var Bool startorendonweekend = If($startonweekend=true,true,If($endonweekend=true,true,false));
// did work happen on sameday?
var Bool sameday = If($startdate=$enddate,true,false);
// number of full days of work **adjusted
var number fulldaysofwork =ToDays($enddate$startdate)1;
// total days of work
var number alldaysofwork =ToDays($enddate$startdate)+1;
var number alldaystotalhours = $alldaysofwork*24;
// hours not worked on start date
var number hoursonstart = 24ToHours(ToTimestamp($startdate,$weekdaystop)[Start]);
// hours not worked on start date saturday
var number saturdayhoursonstart = 24ToHours(ToTimestamp($startdate,$saturdaystop)[Start]);
// hours not worked on end date
var number hoursonend = 24ToHours([End]ToTimestamp($enddate,$alldaysstart));
// combined hours of start and end date hours not worked if not on weekend
var number workdayhoursnotworked = $hoursonstart+$hoursonend;
// combined hours of start and end date hours not worked if on saturday
var number workdayhoursnotworkedsat = If($startorendonweekend=true,$saturdayhoursonstart+$hoursonend,0);
//start and end dates
// Start what day of week
var Bool mstart = If($startdayofweek=1,true,false);
var Bool tustart = If($startdayofweek=2,true,false);
var Bool wstart = If($startdayofweek=3,true,false);
var Bool thstart =If($startdayofweek=4,true,false);
var Bool fstart =If($startdayofweek=5,true,false);
// End what day of the week
var Bool mend = If($enddayofweek=1,true,false);
var Bool tuend = If($enddayofweek=2,true,false);
var Bool wend = If($enddayofweek=3,true,false);
var Bool thend =If($enddayofweek=4,true,false);
var Bool fend =If($enddayofweek=5,true,false);
// not a full week but must remove weekend hours
var Bool meekone = If($mend=true and $tustart=true,true,false);
var Bool meektwo = If($mend=true and $wstart=true,true,false);
var Bool meekthree = If($mend=true and $thstart=true,true,false);
var Bool meekfour = If($mend=true and $fstart=true,true,false);
var Bool meek = If($meekone=true or $meektwo=true or $meekthree=true or $meekfour=true, true, false);
var Bool tueekone = If($tuend=true and $wstart=true,true,false);
var Bool tueektwo = If($tuend=true and $thstart=true,true,false);
var Bool tueekthree = If($tuend=true and $fstart=true,true,false);
var Bool tueek = If($tueekone=true or $tueektwo=true or $tueekthree=true, true, false);
var Bool weekone = If($wend=true and $thstart=true,true,false);
var Bool weektwo = If($wend=true and $fstart=true,true,false);
var Bool week = If($weekone=true or $weektwo=true, true, false);
var Bool theek = If($thend=true and $fstart=true,true,false);
var Bool shortweekremoveweekend = If($meek=true or $tueek=true or $week=true or $theek=true, true,false);
// Calulation to determine the number of weekends
var number fullweeks =
If($alldaysofwork>41,6,
If($alldaysofwork>34,5,
If($alldaysofwork>27,4,
If($alldaysofwork>20,3,
If($alldaysofwork>13,2,
If($alldaysofwork>6,1,0))))));
var number weekenddays= $fullweeks*2;
var Number removeshortweekhours = If($shortweekremoveweekend = true and $worksaturday=false and $fullweeks=0 ,32,0);
var Number startsatremovehours = If ($worksaturday=true and $startonweekend=true, ($weekendhours$saturdayhoursonstart),0);
var Number endsatremovehours = If($worksaturday=true and $endonweekend=true, ($weekendhours$hoursonend),0);
var Number removenonworkedweekendhours = If($startorendonweekend=true, ($fullweeks1)*$weekendhours,$fullweeks*$weekendhours);
If($sameday=true,$completeduration,
If($worksaturday=false, $alldaystotalhours  ($fullweeks*$unworkedweekendhours)  (($fulldaysofwork$weekenddays)*$nonworkhours)$workdayhoursnotworked  $removeshortweekhours
,$alldaystotalhours $startsatremovehours  $endsatremovehours $workdayhoursnotworkedsat (($fulldaysofwork$weekenddays)*$nonworkhours)$removenonworkedweekendhours$workdayhoursnotworked
))
I said it was ugly but there are notes throughout the entire formula
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,448 Points
Here is another formula to try.
// input your own business hours and datetime fields which are your start and end.
var timeofday DayStartTime = ToTimeOfDay("6:00 am");
var timeofday DayEndTime = ToTimeOfDay("6:00 pm");
var datetime StartClock = [Date Created];
var datetime EndClock = [Closed Date];
var DateTime StartDateTime = Max($StartClock, ToTimestamp(ToDate($StartClock), $DayStartTime));
var DateTime EndDateTimeTesting = If(IsNull($EndClock), Now() ,$EndClock);
var DateTime EndDateTime = Min($EndDateTimeTesting, ToTimestamp(ToDate($EndDateTimeTesting), $DayEndTime));
var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime), ToDate($StartDateTime)) + 1; //(we count each day as a full workday)
var number HoursBeforeStartEndAdjustment = $WeekDayDays * ToHours($DayEndTime  $DayStartTime);
var datetime StartDateTimeBounded =
If(ToTimeOfDay($StartDateTime) < $DayStartTime, ToTimestamp(ToDate($StartDateTime), $DayStartTime), $StartDateTime);
var datetime EndDateTimeBounded =
If(ToTimeOfDay($EndDateTime) > $DayEndTime, ToTimestamp(ToDate($EndDateTime), $DayEndTime), $EndDateTime);
Max(0,
Round($HoursBeforeStartEndAdjustment
 Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded)  $DayStartTime))
 Max(0,ToHours($DayEndTime  ToTimeOfDay($EndDateTimeBounded))),0.1))
// input your own business hours and datetime fields which are your start and end.
var timeofday DayStartTime = ToTimeOfDay("6:00 am");
var timeofday DayEndTime = ToTimeOfDay("6:00 pm");
var datetime StartClock = [Date Created];
var datetime EndClock = [Closed Date];
var DateTime StartDateTime = Max($StartClock, ToTimestamp(ToDate($StartClock), $DayStartTime));
var DateTime EndDateTimeTesting = If(IsNull($EndClock), Now() ,$EndClock);
var DateTime EndDateTime = Min($EndDateTimeTesting, ToTimestamp(ToDate($EndDateTimeTesting), $DayEndTime));
var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime), ToDate($StartDateTime)) + 1; //(we count each day as a full workday)
var number HoursBeforeStartEndAdjustment = $WeekDayDays * ToHours($DayEndTime  $DayStartTime);
var datetime StartDateTimeBounded =
If(ToTimeOfDay($StartDateTime) < $DayStartTime, ToTimestamp(ToDate($StartDateTime), $DayStartTime), $StartDateTime);
var datetime EndDateTimeBounded =
If(ToTimeOfDay($EndDateTime) > $DayEndTime, ToTimestamp(ToDate($EndDateTime), $DayEndTime), $EndDateTime);
Max(0,
Round($HoursBeforeStartEndAdjustment
 Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded)  $DayStartTime))
 Max(0,ToHours($DayEndTime  ToTimeOfDay($EndDateTimeBounded))),0.1))
Related Categories

Formulas & functions
 3026 Conversations
 80 Followers
David Brogdon