+ Reply to Thread
Results 1 to 5 of 5

Calculating Hours of Downtime Every Month

  1. #1
    Registered User
    Join Date
    03-17-2019
    Location
    Manila
    MS-Off Ver
    2016
    Posts
    2

    Calculating Hours of Downtime Every Month

    Hi,
    When a service downtime started and ended on the same month I have no problem getting the number of hours as I could just get the difference between the dates. However, I am having trouble calculating the number of hours a service went down if it ended in a different month.

    For Example:
    Started Ended
    Feb X, 2019 hh:mm AM March X, 2019 hh:mm AM

    I want to know how many hours the service was down during the month of February excluding the hours from March and vice versa.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,627

    Re: Calculating Hours of Downtime Every Month

    Welcome to the forum!

    You should be able to get time differences between any dates. Attach a sample workbook that shows the problem here.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Valued Forum Contributor
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    852

    Re: Calculating Hours of Downtime Every Month

    Try this formula to get total downtime per month

    Please Login or Register  to view this content.
    the max around the whole formula is to supress negative outcomes as they can happen.
    also it only works for downtimes less then one calandermonth, so if a downtimes starts in feb and ends in april the formula can be inaccurate

    On top of the formula I also used the duration time format which also suppresses 0 values
    (because in long tables many 0:00:00 strings can make it hard to read/find actual downtimes)

    in the attachment you can see the formula in action
    Attached Files Attached Files

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    2,670

    Re: Calculating Hours of Downtime Every Month

    Try this formula
    Date in A2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You can format the result as
    dd "(days)", hh "hours", mm "minutes and " :ss,0 " seconds"

  5. #5
    Registered User
    Join Date
    03-17-2019
    Location
    Manila
    MS-Off Ver
    2016
    Posts
    2

    Re: Calculating Hours of Downtime Every Month

    Thank you so much for this Roel Jongman

    Do you have an idea how to calculate it if it goes over more than one calendar month? Pardon for asking much
    Last edited by Thienzaw; Yesterday at 07:11 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1