Billable Hours Attempt - WeekdaySub counting Saturday as weekday

• 0
• Question
• Updated 6 months 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.

• 1,222 Points

Posted 7 months ago

• 0
• 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 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

• 1,222 Points
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)
• 72,448 Points
Here is another formula to try.

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,