Forum Discussion

JaimeStrawderma's avatar
JaimeStrawderma
Qrew Cadet
4 years ago

Time Calculation based on Field Status

I have a field (Total Hours) that calculates amount of actual work hours between start and end dates. Can I get this calculation to start based upon the status of a field? E.g., Field = Support Ticket Type: SAM Support, if SAM support is selected then the Total Hours field starts. This is the formula I have for the time calculation:

var timeofday DayStartTime = ToTimeOfDay("8:30 am");
var timeofday DayEndTime = ToTimeOfDay("5:00 pm");

var DateTime StartDateTime = Max([HCS Support Start Date], ToTimestamp(ToDate([HCS Support Start Date]), $DayStartTime));

var DateTime EndDateTimeTesting = If(IsNull([Approx Completion Date]), Now() ,[Approx Completion Date]);

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))

I think this is an Automation but I cannot get it to select Total Hours as an Action. TIA!

------------------------------
Jaime Jaime
------------------------------

2 Replies

  • I'm not certain what you're asking, but changes to formulas cannot be used to trigger an automation.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
  • AustinK's avatar
    AustinK
    Qrew Commander
    If I understand you correctly it sounds like what you would want to do is have a form rule that will trigger when fields have certain options in them. You would use this form rule to capture the date and time that option was saved to the record. Assuming the option cannot be set a second time or that you do not expect it to be. You would need to create a new field called something like "SAM Support Date Capture" and start filling it out when the right conditions on a record are met with the current date. Then have your formula only start calculating from the date and time in this field when it is not blank.