manoharvankired
Qrew Trainee
2 years ago

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

I have two fields date/time I want to calculate extract hours the between those dates. Can someone guide me here

------------------------------
manohar vankireddy
------------------------------

### 3 Replies

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

• If 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,
- 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)

)

------------------------------