How to calculate the number of hours between date/time fields, excluding weekends

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

// 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.*