Spreadsheet: 791060d1660139723-how-to-auto-calculate-available-frequency-and-non-availability-hours-test-yklanka-
Sheet: Office

No | Year | Month | Product Name | In Time | Out Time | Hrs | single hrs | 2 hrs | 3 hrs |  |  | How to auto calculate available frequency and non availability hours (excelforum.com) |  |  |  |  |  |  |  |  |  | Original values
1 | 2022 | January | SUN | 2022-01-02 09:30:00 | 2022-01-02 17:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 7:30 | 1:30 | 6:00
2 | 2022 | January | SIRIUS | 2022-01-02 11:00:00 | 2022-01-03 13:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 26:00 | 20:00
3 | 2022 | January | SPLENDOR | 2022-01-04 10:00:00 | 2022-01-05 10:00:00 |  |  |  |  |  |  |  | Available Frequency |  |  |  |  |  |  |  |  | 24:00 | 8:00 | 16:00
4 | 2022 | January | EAGLE | 2022-01-04 18:00:00 | 2022-01-05 10:00:00 |  |  |  |  |  | Year | Month | 0 Product Hrs | 1 Product | 1 Prod hrs | 2 Product | 2 Prod Hrs | 3 Product | 3 Prod Hrs |  |  | 16:00 | 0:00
5 | 2022 | January | GRANDE | 2022-01-06 01:00:00 | 2022-01-06 22:20:00 |  |  |  |  |  | 2022 | January |  |  |  |  |  |  |  |  |  | 21:20 | 5:40
6 | 2022 | January | HOEGH | 2022-01-06 06:40:00 | 2022-01-07 17:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 34:20 |  | 15:40
7 | 2022 | January | NUT | 2022-01-06 15:00:00 | 2022-01-08 09:30:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 42:30 | 16:30 | 26:00 | 7:20
8 | 2022 | January | [NAME] | 2022-01-11 01:54:00 | 2022-01-11 09:34:00 |  |  |  |  |  | Suppose month starts at 01st 00:00 hrs and ends on the last day at 23:59 , Can I calculate data in to the summary  ? |  |  |  |  |  |  |  |  |  |  | 7:40 | 7:40
9 | 2022 | January | UNITY | 2022-01-14 13:42:00 | 2022-01-15 09:40:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 19:58 | 19:58
 |  |  |  |  |  |  |  |  |  |  | Year =  identify the year/s of the selected data set
 |  |  |  |  |  |  |  |  |  |  | Month =  identify the month/s of the selected data set
 |  |  |  |  |  |  |  |  |  |  | 0 Product hrs =  Calculate how many hours in the particular year/month had no(Zero) product is available.
 |  |  |  |  |  |  |  |  |  |  | 1 Product = Calculate how many times (count )only one single product is available in the selected data set
 |  |  |  |  |  |  |  |  |  |  | 1 Prod hrs = Calculate how many hours only one product is available in the selected data set
 |  |  |  |  |  |  |  |  |  |  | 2 Product = Calculate how many times (count )only two products are available in the selected data set
 |  |  |  |  |  |  |  |  |  |  | 2 Prod hrs= Calculate how many hours only two products are available in the selected data set
 |  |  |  |  |  |  |  |  |  |  | 3 Product = Calculate how many times (count )only three products are available in the selected data set
 |  |  |  |  |  |  |  |  |  |  | 3 Prod hrs= Calculate how many hours only three products are available in the selected data set
0 product hours
from date |  | To Date |  | Total Hours
2022-01-01 00:00:00 |  | 2022-01-02 09:30:00
2022-01-03 13:00:00 |  | 2022-01-04 10:00:00
2022-01-05 10:00:00 |  | 2022-01-06 01:00:00
2022-01-08 09:30:00 |  | 2022-01-11 01:54:00
2022-01-11 09:34:00 |  | 2022-01-14 13:42:00
2022-01-15 09:40:00 |  | 2022-01-31 23:59:00