Forum Discussion

tomvermaas's avatar
tomvermaas
Copper Contributor
Sep 02, 2023
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 exception is that the machine produces on working days from Monday to Friday from 6am to 11pm.

 

so if the machine breaks down on Friday 10:50 PM and the fault is resolved on Monday 6:10 AM, 20 minutes of downtime must be calculated.

I have tried everything but I cannot find the right combination of formulas.

 

does anyone know if this is possible?


example:

Start: 9/1/2023 6:00:00 PM
Stop: 9/1/2023 8:13:00 PM
Result: 133

 

Start: 9/1/2023 9:15:00 PM
Stop: 9/2/2023 8:13:00 PM
Result: 105

 

Start: 9/3/2023 9:00:00 AM
Stop: 9/3/2023 8:30:00 PM
Result: 0

 

Start: 9/1/2023 10:15:00 PM
Stop: 9/4/2023 8:00:00 AM
Result: 165

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Sep 02, 2023

    tomvermaas 

    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)
)

     

     

17 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 02, 2023

    tomvermaas 

    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)
)

     

     

    • DelPanosDN's avatar
      DelPanosDN
      Copper Contributor
      Mar 13, 2024
      Hello, I wanted to ask how could this formula be changed, if we wanted to use it for another timeframe to calculate store downtime, how that exception could be amended to cover the below store hours ?

      Mon - Sat. from 06:00am to 21:00pm and also Sunday 07:00am to 19:00pm
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Mar 13, 2024

        DelPanosDN 

        This will do it:

        =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),
            SixAM, 6 / 24,
            NinePM, 21 / 24,
            SevenAM, 7 / 24,
            SevenPM, 19 / 24,
            include, (WEEKDAY(TotalTime, 2) < 7) * (hr >= SixAM) * (hr <= NinePM) +
                ((WEEKDAY(TotalTime, 2) = 7) * (hr >= SevenAM) * (hr <= SevenPM)),
            Actual, FILTER(TotalTime, include, 0),
            SUM(SIGN(Actual))
        )
    ),
    IFERROR(MAP(start, stop, TotalMinutes), "Please check date/times entered")
)

Resources