I have created a Time Stamp feature that I want to attach to a task. I have a Start check box that will start the timer and a Stop check box to end the time. My question is this: If I Start at 8:30 AM on Monday and Stop at 9:00 AM on Tuesday, is there a way to stop the internal timer for the hours from 5:00 PM Monday and 8:30 AM Tuesday, time that I was not in the office and would not apply to actual work spent on the task? Thanks in advance!
-
282 Points
Posted 3 months ago
QuickBaseCoach App Dev./Training, Champion
-
60,108 Points
The formula is brutal, but here is a gift. it will be a formula numeric field type.
The formula uses fields called [Status Start] and [Status End] which are date/time field types.
var timeofday DayStartTime = ToTimeOfDay("8:30 am");
var timeofday DayEndTime = ToTimeOfDay("5:00 pm");
var DateTime StartDateTime = Max([Status Start], ToTimestamp(ToDate([Status Start]), $DayStartTime));
var DateTime EndDateTimeTesting = If(IsNull([Status End]), Now() ,[Status End]);
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))
The formula uses fields called [Status Start] and [Status End] which are date/time field types.
var timeofday DayStartTime = ToTimeOfDay("8:30 am");
var timeofday DayEndTime = ToTimeOfDay("5:00 pm");
var DateTime StartDateTime = Max([Status Start], ToTimestamp(ToDate([Status Start]), $DayStartTime));
var DateTime EndDateTimeTesting = If(IsNull([Status End]), Now() ,[Status End]);
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))
-
282 Points
Awesome, thanks. Unsure where to put formula numeric field though...This is my layout thus far

QuickBaseCoach App Dev./Training, Champion
-
60,108 Points
Just create a new field called [Duration Hours Worked] and put it on the form.
-
282 Points
Gotcha. One thing please...Max(0, of the formula has an error of "Expecting duration but finding number". Should it be Max(0,ToHours.......,0.1)) ?
QuickBaseCoach App Dev./Training, Champion
-
60,108 Points
-
282 Points
Sorry about that...This is the end of your formula
Max(0,
Round($HoursBeforeStartEndAdjustment
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded))),0.1))
Max(0, is highlighted in yellow and when I hover over it states "Expecting duration but finding number".
It does not keep me from saving the field but I wasn't sure if it would stop the formula from working properly.
Max(0,
Round($HoursBeforeStartEndAdjustment
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded))),0.1))
Max(0, is highlighted in yellow and when I hover over it states "Expecting duration but finding number".
It does not keep me from saving the field but I wasn't sure if it would stop the formula from working properly.
QuickBaseCoach App Dev./Training, Champion
-
60,108 Points
The formula editor often produces false warnings. If the formula saves then there is no error.
Related Categories
-
Formulas & functions
- 2836 Conversations
- 66 Followers