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

     

     

DelPanosDN's avatar
DelPanosDN
Copper Contributor
Mar 13, 2024
Thank you, it worked ! this time it calculated all the fields without that previous message - your function is a success !
DelPanosDN's avatar
DelPanosDN
Copper Contributor
Mar 15, 2024
Hello Patrick, is there a possibility to share the whole formula, in case I need to modify the service window in the future ? Again appreciate your help in this !
  • chetc3's avatar
    chetc3
    Copper Contributor
    May 05, 2024

    Patrick2788 

    Thank you! I knew I was over complicating it! I greatly appreciate your help!

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 05, 2024

    chetc3 

    For totals minutes without restrictions the formula can be simplified.

     

    =(stop - start) * 1440
  • chetc3's avatar
    chetc3
    Copper Contributor
    May 05, 2024

    Patrick2788 

     

    I don't want to exclude anything. But when I try and remove the time frame you have defined, it goes funky. I know it is me, but thought I would try and reach out to the expert. Thanks!

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 05, 2024
    What exactly are the hours you're looking to exclude?
  • chetc3's avatar
    chetc3
    Copper Contributor
    May 04, 2024

    Patrick2788 

    I am trying to your example to capture 24x7 downtime and I can't figure what I am doing wrong. It keeps erroring out or causes memory issues in excel. I would like to think my use case should be simple and I am making a mountain out of a mole hill. Can you help me out?

Resources