1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Function to subtract minutes from hours

Discussion in 'Ask an Excel Question' started by rsomist, Apr 20, 2017.

  1. rsomist

    rsomist New Member

    Messages:
    20
    I have a workbook that has 2 worksheets. The first is YTDTotal and on that worksheet, I track system outages. These outages are tracked by Affected CI (which is column C) and column D gives me the total Impacted Business Hours Duration in hours (which is calculated by subtracting column E from F). The second worksheet lists my targets for total outage time in minutes (column L) by category. I need to add a column into the YTD-Total worksheet that will keep a RUNNING TOTAL of how much time is left before we go over our total outage time target per affected CI (each category). There are some affected CI's that don't have target times, so those don't need a running a total - what I currently have is sufficient. I believe I need to subtract column D (Impacted Business Hours Duration) from the YTD-Total worksheet from column L (Total min) in the Uptime Targets worksheet, but I'm not sure how to convert the time difference (one calculating in minutes and the other in hours) and how to keep each affected CI category together between worksheets. Outages are entered by date, so I might have one Affected CI entered that is not in a group with the same category, but I still need it to keep the running total.

    I'll attach the worksheet. Any help would be GREATLY appreciated.

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,263
    rsomist - as You wrote Any help would be GREATLY appreciated.
    You wrote a lot of text and so on,
    but ...
    was the main idea to get some values out with minutes?
    I did one sample.
    I used 'YTDTotal' columns A, C, E & F-values
    and
    You can see those sum value in minutes 'automatic'.

    Attached Files:

  3. rsomist

    rsomist New Member

    Messages:
    20
    Not really - I need the hours in column D from the YTDTotal worksheet subtracted from the minutes in column K in the Targets worksheet by category that keep a running total as I add more outages.
  4. vletm

    vletm Well-Known Member

    Messages:
    2,263
    rsomist
    Now, both with hrs and minutes

    Attached Files:

  5. rsomist

    rsomist New Member

    Messages:
    20
    Thank you! I was still hoping for a running total time subtracted from the target times on the 2nd worksheet - not just the total outage times like what you have on the PivotTable. I need those hours to be subtracted from the corresponding target times on the other worksheet and keep a running time of that value.

    For example - I have an allotted outage time of 1314.873 minutes per year in the category of email (cell K16 on the Targets worksheet) and year to date, I have experienced 6 hours and 20 minutes of outage time (as shown on the YTDTotals worksheet). So for the year, I have 934.873 minutes left of outage before I go outside my target time. That 934.873 is the number I need a running total of, not the accumulated hours by affected CI.
  6. vletm

    vletm Well-Known Member

    Messages:
    2,263
    rsomist - hope is good word
    1) You should use same unique 'Affected CI' all the time!
    2) Email <> Email / Outlook (as well if there are some extra characters before CI as with 'F5-VPN LB')
    3) I lookup to 'X' column Your target values
    4) 'Y'-column (help column if want to see monthly values too) has total used from You data
    5) 'O..'V' are 'spare columns for those missing months'.
    6) 'Z'-column shows difference of target/used. If over then 'hh:mm over'
    7) 'AA'-column shows minutes left as You hoped. If all used then only 'over'
    8) 'AB'-column shows times how much would use per day the rest of year.
    >> any better now?

    Attached Files:

  7. vletm

    vletm Well-Known Member

    Messages:
    2,263
    rsomist
    With heavy flu I noticed more and some mistakes too ...
    1) I fixed YTDTotal-formulas ... many missing 'Service/Platforms'
    2) I did new formulas to 'Targets' ... still many 'no connection of those'?
    >> 'Targets'-sheet would be better (cause Pivot)
    > Ideas ... Questions?

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page