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 weekend...
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:
