Forum Discussion

DavidBrogdon's avatar
DavidBrogdon
Qrew Assistant Captain
7 years ago

Billable Hours Attempt - WeekdaySub counting Saturday as weekday

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. 

Formulas and functions
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    7 years ago
    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

    • DavidBrogdon's avatar
      DavidBrogdon
      Qrew Assistant Captain
      7 years ago
      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!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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))