SOLVED

Formula to remove afterhours and weekends from number

Copper Contributor

Formula to remove afterhours and weekends from number

First, let me tell you that I am alright at Excel, but am by no means an expert.  I have a formula our users are using, where they now want to remove any time afterhours (outside 8AM-5PM) and weekends.  The formula they have is: =TEXT(A1-A2, "[h]:mm:ss")

Their example is:

A1                                A2                          A3

02-16-2024 13:05      02-21-2024 08:02          114:57:00

The time in A3 should be 3:57:00 hours, not 114:57:00 hours. (The correct time should be 21:57:00, not 3:57:00).

Any help would be greatly appreciated.

4 Replies

Re: Formula to remove afterhours and weekends from number

Why should it be 3:57:00?

Friday 02-16-2024 13:05 to 17:00 on that day is 03:55.

Saturday 02-17-2024 and Sunday 02-18-2024 are excluded.

Monday 02-19-2024 8:00 to 17:00 is 9:00.

Tuesday 02-20-2024 8:00 to 17:00 is 9:00.

Wednesday 02-21-2024 8:00 to 8:02 is 0:02.

Total is 21:57

Re: Formula to remove afterhours and weekends from number

My apologies, you are correct. I was looking at another row in the spreadsheet.
best response confirmed by JD1455 (Copper Contributor)
Solution

Re: Formula to remove afterhours and weekends from number

Here is a solution using a custom VBA function.

Press Alt+F11 to activate the Visual Basic Editor and to view the code of the function.

Re: Formula to remove afterhours and weekends from number

For this task I've created a function called ElapsedTime:

``````ElapsedTime
=LAMBDA(start,end,LET(
minutes_in_day, 1440,
one_minute, 1 / minutes_in_day,
MAP(
start,
end,
LAMBDA(s, e,
LET(
minutes_elapsed, (e - s) * minutes_in_day,
allminutes, SEQUENCE(minutes_elapsed, , s, one_minute),
eight_am, 8 / 24,
five_pm, 17 / 24,
hr, allminutes - INT(allminutes),
weekend, WEEKDAY(allminutes, 2),
crit, (hr >= eight_am) * (hr <= five_pm) * (weekend < 6),
total_minutes, COUNT(FILTER(allminutes, crit)),
(total_minutes / 60) / 24
)
)
)
))``````

The through process is to create an array of numbers from the start time to end time in a given row.  The array increments by precisely 1 minute or (1/1440). Then I filtered out times outside of the 8 to 5 workday and weekends.

At the sheet level all you have to do is feed the functions two arrays like this:

1 best response

Accepted Solutions
best response confirmed by JD1455 (Copper Contributor)
Solution

Re: Formula to remove afterhours and weekends from number

Here is a solution using a custom VBA function.

Press Alt+F11 to activate the Visual Basic Editor and to view the code of the function.