Formula to remove afterhours and weekends from number

[JD1455]

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.  

[#2]

@JD1455 

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

[#3]

My apologies, you are correct. I was looking at another row in the spreadsheet.

[#4 · Accepted solution]

@JD1455 

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.

[#5]

@JD1455 

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: