Billable Hours Attempt - WeekdaySub counting Saturday as weekday

  • 0
  • 1
  • Question
  • Updated 3 hours ago
  • (Edited)
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. 

Photo of David Brogdon

David Brogdon

  • 860 Points 500 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Jason

Jason

  • 1,192 Points 1k badge 2x thumb
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 non-workday
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 = 24-ToHours(ToTimestamp($startdate,$weekdaystop)-[Start]);
// hours not worked on start date saturday
var number saturdayhoursonstart = 24-ToHours(ToTimestamp($startdate,$saturdaystop)-[Start]);
// hours not worked on end date
var number hoursonend = 24-ToHours([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, ($fullweeks-1)*$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

Photo of David Brogdon

David Brogdon

  • 860 Points 500 badge 2x thumb
You are right that is quite a formula! Sometimes this is necessary. Kudos to putting the work in on it. At the moment I just decided to create a "Time Logs" table and limit it to a single date entry field and a start time and end time. That allows me to restrict the hours to a single day only. Then I am just summing it up to the parent table to see the total billable hours. It is slightly more cumbersome for data entry but it is the most straightforward way I could think of. If I run into any issues with it I will definitely try out this formula though. Thanks!
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,468 Points 50k badge 2x thumb
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))