The number of "Work Hours" between two DateTime Values:
Formula #1 calculates the number of working days between two dates (excluding weekends and holidays). This formula takes it one step further by allowing you to calculate the number of business hours between two DateTime values. This code is an improved version of the code shown on this page before 2021. It automatically handles start and end times that fall outside the business day. It allows you to assign business hours on any day of the week and vary the hours on each day of the week. And it is all done in this one formula.In the first two lines you put in your fields for BeginDateTime and EndDateTime. Below that you put in the start time and end time for each day of the week. Each day of the week can have a different start and end time. Below that you can add a list of holiday dates. These dates are entered for each year, and the list can include holiday dates for multiple years into the future. The code below holiday list should not be changed.
The result of this formula is elapsed hours in decimal format. If you want to convert this to an elapsed time string format (HH:MM) you can use formula #9. Formula #9 needs a value in seconds and this formula is in hours, so you should multiply this formula by 3600 when you use it in formula #9. You can do this on the second line of formula #9.
Local DateTimeVar BeginDate := {Your.BeginDT}; //Your Begin DateTime
Local DateTimeVar EndDate := {Your.EndDT}; //Your End DateTime
//Specify your start and end times for each day.
//Set both to Time(0,0,0) for non-working days.
TimeVar Range Array BusTimes;
Redim BusTimes[7];
BusTimes [1] := Time(0,0,0) to Time( 0,0,0); //Sunday
BusTimes [2] := Time(7,0,0) to Time(20,0,0); //Monday
BusTimes [3] := Time(7,0,0) to Time(20,0,0); //Tuesday
BusTimes [4] := Time(7,0,0) to Time(20,0,0); //Wenesday
BusTimes [5] := Time(7,0,0) to Time(20,0,0); //Thursday
BusTimes [6] := Time(7,0,0) to Time(20,0,0); //Friday
BusTimes [7] := Time(0,0,0) to Time( 0,0,0); //Saturday
DateVar Array Holidays := [
Date (2020,01,1), //add additional holidays between these two
Date (2019,12,25)
];
//////////////////////// Nothing user configurable below.
Local NumberVar MinBeginDT :=(
Local TimeVar BusDayBeg := Minimum( BusTimes[DayOfWeek (BeginDate)]);
Local TimeVar BusDayEnd := Maximum( BusTimes[DayOfWeek (BeginDate)]);
Local TimeVar B := Maximum ([Time (BeginDate) , BusDayBeg]);
Local TimeVar E :=
if Date(BeginDate) <> Date(EndDate) then BusDayEnd else
if Time(EndDate) > BusDayEnd then BusDayEnd else
if Time(EndDate) < BusDayBeg then BusDayBeg else
Time (EndDate);
if Minimum( BusTimes[DayOfWeek (BeginDate)]) = Time(0,0,0) //A non working day
or Date(BeginDate) in Holidays // A Holiday
or B > BusDayEnd //An event that start after the Business Day Ends
then 0
else (E - B) /(60*60); );
Local NumberVar MinEndDT :=(
Local TimeVar BusDayBeg := Minimum( BusTimes[DayOfWeek (EndDate)]);
Local TimeVar BusDayEnd := Maximum( BusTimes[DayOfWeek (EndDate)]);
Local TimeVar B := BusDayBeg;
Local TimeVar E :=
if Time(EndDate) > BusDayEnd then BusDayEnd else
Time (EndDate);
if Minimum( BusTimes[DayOfWeek (EndDate)]) = Time(0,0,0) //A non working day
or Date(EndDate) in Holidays // A Holiday
or Date(EndDate) = Date(BeginDate) // Single Day Events are handled above
or E < BusDayBeg //An event that ends before the Business Day Starts
then 0
else (E - B) /(60*60););
Local NumberVar MinWhole :=(
Local DateVar Begin := Date (BeginDate);
Local DateVar End := Date (EndDate);
if End < Begin then End := Begin;
Local numberVar i;
Local NumberVar BusMinutes;
Local NumberVar RawDays := End - Begin;
If RawDays > 1 then
For i := 1 to RawDays-1
do( if DayofWeek (Begin +i) in 2 to 6 and not (Begin +i in Holidays)
then BusMinutes := BusMinutes
+(Maximum(BusTimes[DayofWeek(Begin+i)]) - Minimum(BusTimes[DayofWeek(Begin+i)]))
);
BusMinutes/60/60 );
(MinEndDT + MinBeginDT + MinWhole)