Spreadsheet: 745024d1629687775-formula-to-search-for-absent-and-present-absent-students
Sheet: Sheet1

 | Seeptember 2021
 | 2021-09-01 00:00:00 | 2021-09-01 00:00:00 | 2021-09-01 00:00:00 | 2021-09-01 00:00:00 | 2021-09-02 00:00:00 | 2021-09-02 00:00:00 | 2021-09-02 00:00:00 | 2021-09-02 00:00:00 | 2021-09-03 00:00:00 | 2021-09-03 00:00:00 | 2021-09-03 00:00:00 | 2021-09-03 00:00:00 | 2021-09-04 00:00:00 | 2021-09-04 00:00:00 | 2021-09-04 00:00:00 | 2021-09-04 00:00:00 | 2021-09-05 00:00:00 | 2021-09-05 00:00:00 | 2021-09-05 00:00:00 | 2021-09-05 00:00:00 | … | …
Name | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | … | … | … | … | … | …
[NAME] | A | A | A | A | P | A | P | P | A | A | P | P | A | A | P | P | … | … | … | … | … | …
George | P | P | P | P | P | P | A | A | A | P | P | P | P | P | A | A | … | … | … | … | … | …
[NAME] | A | A | A | A | A | p | p | p | p | A | A | A | p | p | p | p | … | … | … | … | … | …
[NAME] | A | p | p | p | A | p | p | A | A | p | p | A | A | A | p | p | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
 | 2021-10-01 00:00:00
 | 2021-10-01 00:00:00 | 2021-10-01 00:00:00 | 2021-10-01 00:00:00 | 2021-10-01 00:00:00 | 2021-10-02 00:00:00 | 2021-10-02 00:00:00 | 2021-10-02 00:00:00 | 2021-10-02 00:00:00 | 2021-10-03 00:00:00 | 2021-10-03 00:00:00 | 2021-10-03 00:00:00 | 2021-10-03 00:00:00 | 2021-10-04 00:00:00 | 2021-10-04 00:00:00 | 2021-10-04 00:00:00 | 2021-10-04 00:00:00 | 2021-10-05 00:00:00 | 2021-10-05 00:00:00 | 2021-10-05 00:00:00 | 2021-10-05 00:00:00 | … | …
Name | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | … | … | … | … | … | …
[NAME] | A | P | P | A | A | A | P | A | A | A | A | P | A | A | P | P | … | … | … | … | … | …
George | A | A | P | P | P | P | A | A | A | A | A | A | P | P | A | A | … | … | … | … | … | …
[NAME] | p | p | A | A | A | p | p | p | p | A | A | p | p | p | p | p | … | … | … | … | … | …
[NAME] | A | A | A | A | A | p | p | A | A | p | p | A | A | A | A | A | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
 | 2021-11-01 00:00:00
 | 2021-11-01 00:00:00 | 2021-11-01 00:00:00 | 2021-11-01 00:00:00 | 2021-11-01 00:00:00 | 2021-11-02 00:00:00 | 2021-11-02 00:00:00 | 2021-11-02 00:00:00 | 2021-11-02 00:00:00 | 2021-11-03 00:00:00 | 2021-11-03 00:00:00 | 2021-11-03 00:00:00 | 2021-11-03 00:00:00 | 2021-11-04 00:00:00 | 2021-11-04 00:00:00 | 2021-11-04 00:00:00 | 2021-11-04 00:00:00 | 2021-11-05 00:00:00 | 2021-11-05 00:00:00 | 2021-11-05 00:00:00 | 2021-11-05 00:00:00 | Ext… | …
Name | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | 1st hour | 2nd hour | 3rd hour | 4th hour | … | … | … | … | … | …
[NAME] | A | A | A | A | P | A | P | A | A | A | P | P | A | p | P | P | … | … | … | … | … | …
George | P | A | A | P | P | P | A | A | A | P | P | P | P | P | A | A | … | … | … | … | … | …
[NAME] | p | A | A | A | p | p | p | A | A | A | A | A | p | p | p | p | … | … | … | … | … | …
[NAME] | p | p | p | p | p | p | p | A | A | p | p | p | A | A | p | A | … | … | … | … | … | …
Ext… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
… | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | …
Example 1
Date | 2021-09-02 00:00:00 |  | I need formula when I choose date in B38  excel will search for letter A under this date in row 3 and 15 and 27 , if it find at least one letter A , it should return the name of the student and the four hour beside the name , if all cells under that date are letter P , ignore the name
Name | 1st hour | 2nd hour | 3rd hour | 4th hour
[NAME] | P | A | P | P
George | P | P | A | A
[NAME] | A | p | p | A
Example 2
first date | 2021-11-01 00:00:00 |  | second date | 2021-11-03 00:00:00 |  | Name | [NAME] |  | Letter | A |  | Result | I need formula to search in Row dates ( Row 3 , 15 and 27 ) how many letter A (in K49) from date in B49 till date in E49 for the Name in H49
 |  |  |  |  |  |  |  |  |  |  |  | Result = | 8