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

     

     

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")
)
      • DelPanosDN's avatar
        DelPanosDN
        Copper Contributor
        Mar 13, 2024

        Patrick2788  thank you ! for some reason the formula often produces the message "Please check date/times entered" I am not sure why this is happening as the timeframes impacted seem to be within the time window I want to calculate 😞 

         


  • tomvermaas's avatar
    tomvermaas
    Copper Contributor
    Sep 03, 2023
    Thanks!!!!! This helps me a lot!!!
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Sep 03, 2023
      You're welcome. Good to see you have Excel 365!

Resources