JaimeStrawderma
5 years agoQrew Cadet
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
------------------------------
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
------------------------------