Cells A1:A20 of Sheet1 is a date and time field. As shown below and in the attached image, Cells A1:F5 of Sheet2 is a grid with time in Row 1 and Dates in Column A. Specifically, cells B1:F1 = 8, 9, 19, 11, 12 and cells A2:A5 = 1-Apr, 2-Apr, 3-Apr and 4-Apr.
I would like to enter formulas in cells B2:F5 of Sheet2 so that, for example, cell B2 calculates the number of cells in Column A of Sheet1 that fall between the time range of 8 AM to 9 AM on 1-Apr. Similarly, cell F5 on Sheet2 calculates the number of cells in Column A of Sheet1 that are between 11-12 AM on 4-Apr.
Many thanks for any guidance on how to best do this.
Sheet1 Cells A1:A20
Sheet2 Cells A1:F5
I would like to enter formulas in cells B2:F5 of Sheet2 so that, for example, cell B2 calculates the number of cells in Column A of Sheet1 that fall between the time range of 8 AM to 9 AM on 1-Apr. Similarly, cell F5 on Sheet2 calculates the number of cells in Column A of Sheet1 that are between 11-12 AM on 4-Apr.
Many thanks for any guidance on how to best do this.
Sheet1 Cells A1:A20
4/1/23 8:21 AM |
4/1/23 8:46 AM |
4/1/23 9:01 AM |
4/1/23 10:44 AM |
4/2/23 8:55 AM |
4/2/23 9:03 AM |
4/2/23 11:33 AM |
4/2/23 11:37 AM |
4/2/23 11:44 AM |
4/3/23 8:05 AM |
4/3/23 8:09 AM |
4/3/23 9:21 AM |
4/3/23 10:22 AM |
4/3/23 11:04 AM |
4/4/23 8:59 AM |
4/4/23 9:11 AM |
4/4/23 9:21 AM |
4/4/23 11:08 AM |
4/4/23 11:10 AM |
4/4/23 11:55 AM |
Sheet2 Cells A1:F5
8 | 9 | 10 | 11 | 12 | |
1-Apr | |||||
2-Apr | |||||
3-Apr | |||||
4-Apr |