Forum Discussion
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.
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.
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:
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
- My apologies, you are correct. I was looking at another row in the spreadsheet.
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.
