Forum Discussion
Solved
Calculate downtime
Hello all, I am looking for help. I would like to calculate the downtime of a machine in Excel. for this I need the difference in minutes between the start time and stop time. The only excep...
This is a 365 solution. I've created a function called Downtime.
Downtime(Start,Stop) =LET( Min_in_day, 1440, one_minute, 1 / Min_in_day, TotalMinutes, LAMBDA(begin, end, LET( duration, (end - begin) * Min_in_day, TotalTime, SEQUENCE(duration, , begin, one_minute), hr, TotalTime - INT(TotalTime), Actual, FILTER( TotalTime, (WEEKDAY(TotalTime, 2) < 6) * (hr >= 6 / 24) * (hr < 23 / 24), 0 ), SUM(SIGN(Actual)) ) ), MAP(start, stop, TotalMinutes) )
This is a 365 solution. I've created a function called Downtime.
Downtime(Start,Stop)
=LET(
Min_in_day, 1440,
one_minute, 1 / Min_in_day,
TotalMinutes, LAMBDA(begin, end,
LET(
duration, (end - begin) * Min_in_day,
TotalTime, SEQUENCE(duration, , begin, one_minute),
hr, TotalTime - INT(TotalTime),
Actual, FILTER(
TotalTime,
(WEEKDAY(TotalTime, 2) < 6) * (hr >= 6 / 24) * (hr < 23 / 24),
0
),
SUM(SIGN(Actual))
)
),
MAP(start, stop, TotalMinutes)
)
Thanks!!!!! This helps me a lot!!!
- You're welcome. Good to see you have Excel 365!