Function to subtract minutes from hours

[rsomist]

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.

 

[vletm]

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'.

 

[rsomist]

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'.

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.

 

[vletm]

rsomist
Now, both with hrs and minutes

 

[rsomist]

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.

 

[vletm]

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?

 

[vletm]

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?