Forum Discussion
- SherryBlackburn7 years agoQrew Trainee
I changed the field type, but it is returning 0 no matter what date/time I put in the start and end fields. I don't think I need the var Number WeekDayDays. Our stations operate 365 days a year, round the clock with the exception of midnight to 4am.
This is by far the most complicated formula I've worked on, so feel free to treat me as a beginner :)
var timeofday DayStartTime = ToTimeOfDay("4:00 am");
var timeofday DayEndTime = ToTimeOfDay("12:01 am");
var datetime StartClock = [Outage Start Date/Time];
var datetime EndClock = [Outage End Date/Time];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;
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)) - QuickBaseCoachD7 years agoQrew CaptainI will give it a try in the next few days.
- QuickBaseCoachD7 years agoQrew CaptainI did a test and the formula works if you fix my error here
var timeofday DayStartTime = ToTimeOfDay("4:00 am");
var timeofday DayEndTime = ToTimeOfDay("11:59 pm");
I had those backwards.
Let know how it tests.