I have two fields date/time I want to calculate extract hours the between those dates. Can someone guide me here
Hey Manohar,
Easiest way to get this done would be to create a Formula - Duration field and input the formula below.
[Date Two]-[Date One]
Make sure under the "display" section you change the units dropdown to "Hours". (Will be defaulted to "Smart Units")
Hope this helps!
------------------------------
George Hambel
Project Manager
Synctivate Inc
------------------------------
Hi George,
Here my formula , Field type is numeric but I want to calculate exact hours
Note: business hours excluding it is fine
var timeofday DayStartTime = ToTimeOfDay("12:00");
var timeofday DayEndTime = ToTimeOfDay("21:00");
var datetime StartClock = [Start Date];
var datetime EndClock = [Completed Date];
var datetime OnHoldStartClock = [On Hold Start Date];
var datetime OnHoldStopClock = [On Hold Stop 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 DateTime OnHoldStarDateTimeTesting = If(IsNull($OnHoldStartClock), Now() ,$OnHoldStartClock);
var DateTime OnHoldStarDateTime = Max($OnHoldStarDateTimeTesting, ToTimestamp(ToDate($OnHoldStarDateTimeTesting), $DayStartTime));
var DateTime OnHoldStopDateTimeTesting = If(IsNull($OnHoldStopClock), Now() ,$OnHoldStopClock);
var DateTime OnHoldStopDateTime = Min($OnHoldStopDateTimeTesting, ToTimestamp(ToDate($OnHoldStopDateTimeTesting), $DayEndTime));
var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime),ToDate($StartDateTime)) - WeekdaySub(ToDate($OnHoldStopDateTime),ToDate($OnHoldStarDateTime)) + 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);
var datetime OnHoldStartDateTimeBounded =
If(ToTimeOfDay($OnHoldStarDateTime) < $DayStartTime, ToTimestamp(ToDate($OnHoldStarDateTime), $DayStartTime), $OnHoldStarDateTime);
var datetime OnHoldStopDateTimeBounded =
If(ToTimeOfDay($OnHoldStopDateTime) > $DayEndTime, ToTimestamp(ToDate($OnHoldStopDateTime), $DayEndTime), $OnHoldStopDateTime);
Max(0,
Round($HoursBeforeStartEndAdjustment
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded)))
, Max(0,ToHours(ToTimeOfDay($OnHoldStartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($OnHoldStopDateTimeBounded)))),0.1)
------------------------------
manohar vankireddy
------------------------------
Qrew LegendIf you just want this to be a formula Duration field type, then make that change and convert it to a Duration like this :
Hours(
Max(0,
Round($HoursBeforeStartEndAdjustment
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded)))
, Max(0,ToHours(ToTimeOfDay($OnHoldStartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($OnHoldStopDateTimeBounded)))),0.1)
)
// Convert Employee Working Start and End Time to Numeric Hours
var Number StartHour = Hour([Employee - Working Start Time]) + (Minute([Employee - Working Start Time]) / 60);
var Number EndHour = Hour([Employee - Working End Time]) + (Minute([Employee - Working End Time]) / 60);
// Extract Date and Time from Assigned Date
var Date StartDate = ToDate([Assigned Date]);
var Number StartTime = Hour(ToTimeOfDay([Assigned Date])) + (Minute(ToTimeOfDay([Assigned Date])) / 60);
// Extract Date and Time from Due Date
var Date EndDate = ToDate([Due Date/Time]);
var Number EndTime = Hour(ToTimeOfDay([Due Date/Time])) + (Minute(ToTimeOfDay([Due Date/Time])) / 60);
// Calculate Workdays Excluding Weekends
var Number TotalDays = WeekdaySub(ToDate([Due Date/Time]), ToDate([Assigned Date]));
// Handle Partial First Day
var Number FirstDayHours = If(
$StartTime < $StartHour, $EndHour - $StartHour, // If assigned before work hours, count full workday
$StartTime > $EndHour, 0, // If assigned after work hours, no work hours that day
$EndHour - Max($StartTime, $StartHour) // Otherwise, count only remaining hours in the workday
);
// Handle Partial Last Day
var Number LastDayHours = If(
$EndTime < $StartHour, 0, // If due before work hours, no hours counted that day
$EndTime > $EndHour, $EndHour - $StartHour, // If after work hours, count full workday
Min($EndTime, $EndHour) - $StartHour // Otherwise, count only up to due time
);
// Ensure Total Days is at least 1 if Assigned and Due Date are the same
var Number AdjustedDays = If($TotalDays = 0, 1, $TotalDays);
// Calculate Total Work Hours
Abs(( ($AdjustedDays - 1) * ($EndHour - $StartHour) ) + $FirstDayHours + $LastDayHours)[Employee - working start/end times] = timeofday field type
[Assigned Date] = date/time field type
[Due Date/Time] = date/time field type
I also have a "duplicate field" of this formula above where I replace the [due date/time] field with my [completed on] date/time field to give me the actual time that it took
*this does not account for holidays*
*Field type for formula above needs to be formula - numeric.*