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

     

     

18 Replies

  • javier04's avatar
    javier04
    Copper Contributor
    Jul 23, 2026

    Great solution from PatrickZ788! The LAMBDA approach handles the Monday-Friday exception cleanly.

    One thing worth adding — before building out the full formula, it helps to manually verify a few of your downtime calculations first using a simple Calculadora Alicia to confirm your expected minute differences are correct. That way you can validate the formula output against known values during testing saves a lot of debugging time when working with time-based calculations in Excel.

  • 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
      Tin 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")
)