The issue I am facing is regarding 2 separate tables in different tabs on the same excel worksheet.
The Table 1 below consists of a tabulated computer downtime. User have to input the date and time while the hours will be auto calculated (=(Date and time end - Date and time start) * 24)
Table 1
| Computer name | Date and time start | Date and time end | Hours |
|---|---|---|---|
| A | 3/9/2022 3:06:00 am | 3/9/2022 3:09:00 am | 0.05 |
| B | 3/9/2022 11:33:00 am | 4/9/2022 9:40:00 pm | 34.117 |
| C | 4/9/2022 11:33:00 am | 4/9/2022 12:33:00 pm | 1 |
| C | 6/9/2022 11:33:00 am | 4/9/2022 12:33:00 pm | 1 |
There's another table to consolidate all downtime in a day to show the percentage of computers running. The uptime percentage is calculated as 3 computers, A to C, times 24 hours. For example, 3rd of September have computer A and B total down time 12.5 hours so the up time was calculated as
=(24 hours * 3 computers - 12.5 hours) / 24 hours * 3 computers.
Currently table 2 below is tabulated manually base on table 1 above.
Table 2
| Date | Total downtime hours | Uptime % |
|---|---|---|
| 1/9/2022 | 0 | 100% |
| 2/9/2022 | 0 | 100% |
| 3/9/2022 | 12.5 | 82.64% |
| 4/9/2022 | 22.667 | 68.52% |
| 5/9/2022 | 0 | 100% |
| 6/9/2022 | 1 | 98.61% |
| 7/9/2022 | 0 | 100% |
I'm trying to get table 2 to be auto filled based on table 1. Here are the following issues that I faced:
A. Unable to match date from table 2 and date time in Table 1. Tried partial match formula but did not work.
=(72-SUMIF(Table1[Date and time start], "*"&[@Date]&"*", Table1[Hours]))/72
The formula above failed due to no partial match found of the date from table 2 date time which resulted to show 100% uptime despite having downtime for that particular date.
B. Could not find a way to separate downtime hours when it was down until the next day Computer B was shutdown from 3rd of September until 4th of September. Total downtime is 34.117 hours. Total downtime for Computer B on 3rd of September was 12.45 hours and total downtime on 4th September was 21.667 hours. I have not found a way to split 34.117 hours into 12.45 hours and 21.667 hours respectively.