difference between 2 dates in office hours

[NLPTA]

Hi all,

i know there are alot of topics already but i can not get it to work, not even with the help of my friend copilot...

The formula will only show the difference in hours if both dates are in office time/day/
What should i change to have it count 1 of them? please help.
Example Creation_date is 05/01/2026 14:59 and Response_date is 05/01/2026 15:18with a perfect result of 00:19 response time.
Now creation_date 13/11/2025 15:46 and response_date 14/11/2025 7:29 is a result - and should be 01:14 becasue of office times 09:00-17:00

My formula:

Interval(
Avg(
If(
Hour(Timestamp(responseDate+1/24)) >= 9 AND Hour(Timestamp(responseDate+1/24)) < 17 AND
Hour(Timestamp(TIME_Creation+1/24)) >= 9 AND Hour(Timestamp(TIME_Creation+1/24)) < 17 AND
Match(WeekDay(Timestamp(TIME_Creation+1/24)), 'Mon','Tue','Wed','Thu','Fri') AND
Match(WeekDay(Timestamp(responseDate+1/24)), 'Mon','Tue','Wed','Thu','Fri'),
(Timestamp(responseDate+1/24) - Timestamp(TIME_Creation+1/24))
)
),
'hh:mm'
)
 

[Daniel_Castella]

Hi @NLPTA 

Try this formula, in my end seems to work fine:

Interval(

If(Hour(responseDate) - 9 >0, responseDate, Floor(TIME_Creation) + MakeTime(17,00))

-

If(17 - Hour(TIME_Creation) > 0, TIME_Creation, Floor(responseDate) + MakeTime(9,00))
+

NetWorkDays(TIME_Creation,responseDate)-2
, 'hh:mm')

Kind Regards

Daniel

[NLPTA]

Thank you Daniel for helping out. 
The results are showing negatives also now and not there yet.

[marcus_sommer]

I wouldn't go with such approach else using appropriate prepared table-data within a mapping. Such table might be created manually in Excel but there are also generic possibilities, for example:

load *, Weekday & '|' & Time as Key, rowno() as Value;
load *, maketime(Hour, Minute) as Time;
load *, -1 + iterno() as Minute while -1 + iterno() <= 59;
load *, 8 + iterno() as Hour while 8 + iterno() < 17;
load pick(recno(), 'Mon', 'Tue', 'Wed', Thu', 'Fri') as Weekday autogenerate 5;

whereby only Key + Value are in the mapping and then a call like:

time(1/24/60 *
   applymap('mapTab', Weekday & '|' & EndTime, null()) -
   applymap('mapTab', Weekday & '|' & StartTime, null())) as Difference

returns the differences.

It's a very simplified example to show the general approach to calculate the differences between timestamps  and may need some further adjustments, for example to possible times before or after the office-hours which might be fetched with rangemin/rangemax-queries.

The real benefits of such logic comes when there are different office-times for departments, employees, times and/or holidays and/or breaks needed to be considered or similar stuff. Extending for it the table-data is not mandatory trivial but compared with the nightmare to get a solution with n nested if-loops it would be quite easy.

[Daniel_Castella]

Hi @NLPTA 

Yes, true. My bad. I needed to adjust a couple of things in my formula. Check the following one, I set the if to take into account the 0 too, and applied a correction factor to the Net Work Days, since they were counted as 24 hours instead of 8 hours.

Interval(

If(Hour(responseDate) - 9 >=0, responseDate, Floor(TIME_Creation) + MakeTime(17,00))

-

If(17 - Hour(TIME_Creation) > 0, TIME_Creation, Floor(responseDate) + MakeTime(9,00))
+

((NetWorkDays(TIME_Creation,responseDate)-2) * 8/24)
, 'hh:mm')

Let me know if now it works.

Kind Regards

Daniel

[NLPTA]

results for the adjusted formula:

[Daniel_Castella · Accepted solution]

Hi @NLPTA 

ok, I have changed the formula concept. I tested most of the points of your table (before I only did a couple) and now it seems to work.

Interval(
If(Hour(responseDate) - 9 < 0, Rangemax(MakeTime(Hour(responseDate), Minute(responseDate)), MakeTime(9,00)), Rangemin(MakeTime(Hour(responseDate), Minute(responseDate)), MakeTime(17,00)))
-
If(Hour(TIME_Creation) - 9 < 0, Rangemax(MakeTime(Hour(TIME_Creation), Minute(TIME_Creation)), MakeTime(9,00)), Rangemin(MakeTime(Hour(TIME_Creation), Minute(TIME_Creation)), MakeTime(17,00)))
+ (Rangemax((NetWorkDays(TIME_Creation,responseDate)-1),0) * 8/24)
,'hh:mm')

Kind Regards

Daniel