Both of those cells appear to have 01-04-2023 in them, so we would expect the result to be TRUE (I am assuming you agree with this?). The entry in cell L5 is formed with a formula:
which is unambiguous because it's syntax requires inputs of year, month, and day, in that order. So L5 represents the 1st day of April in 2023. I do not know what is intended by 01-04-2023 in cell C4, but I believe you want it to be the same date in this example (1st day of April in 2023). Your system is probably interpreting 01-04-2023 as the 4th day of January. To confirm this, edit C4 (temporarily) to read as 04-01-2023 to confirm whether the logic formula I mentioned returns a TRUE. If it does, then you will want to examine your date formats. This may be something caused by either your regional settings or local cell formatting.
Here is a slightly modified version suggested by
@awoohaw that replaces the row counting in the AGGREGATE function with simple references to a column that you already have to track the log number of the entries for each day. This means that you can copy the 7 cells in K6:K12 and paste them in each summary block for each staff member and drag right across the date range without having to update the row counting component of the formula. But the source of your problem at the moment appears to be associated with the date formatting. In the example below, you will see that I changed some dates in column C to match with the dates in the summary tables...and the formulas copy the times into the appropriate locations. Alternatively, if you are comfortable with the date representation shown in column C, then you may want to change the formula in K5, K15, etc., to ensure that the correct date range of interest is shown in the summary tables.
MrExcel_20230523.xlsx |
---|
|
---|
| A | B | C | D | | | | H | I | J | K | L | M | N |
---|
1 | | | Source | | | | | | | | | | | |
---|
2 | | | | | | | | | | | | Target | | |
---|
3 | Emp ID | First Name | Date | Time | | | | | | ID | 1 | | | |
---|
4 | 1 | Sumsaam | 4/1/2023 | 11:00 | | | | | | Name | Sumsaam | | | |
---|
5 | 1 | Sumsaam | 4/1/2023 | 11:53 | | | | First Log… | Log Entry | Emp ID \ Date | 4/1/2023 | 4/2/2023 | 4/3/2023 | 4/4/2023 |
---|
6 | 1 | Sumsaam | 4/1/2023 | 23:06 | | | | before 8:00 AM | 1 | 1 | | | | 0:48 |
---|
7 | 1 | Sumsaam | 2/4/2023 | 12:06 | | | | after 8:00 AM | 1 | 1 | 11:00 | | | 11:17 |
---|
8 | 1 | Sumsaam | 2/4/2023 | 22:43 | | | | | 2 | 1 | 11:53 | | | |
---|
9 | 1 | Sumsaam | 4/4/2023 | 0:48 | | | | | 3 | 1 | 23:06 | | | |
---|
10 | 1 | Sumsaam | 4/4/2023 | 11:17 | | | | | 4 | 1 | | | | |
---|
11 | 3 | Mustafa | 1/4/2023 | 11:23 | | | | | 5 | 1 | | | | |
---|
12 | 3 | Mustafa | 4/1/2023 | 23:06 | | | | | 6 | 1 | | | | |
---|
13 | 3 | Mustafa | 4/2/2023 | 12:48 | | | | | | | 3 | | | |
---|
14 | 3 | Mustafa | 2/4/2023 | 22:32 | | | | | | | Mustafa | | | |
---|
15 | 3 | Mustafa | 3/4/2023 | 0:59 | | | | | | | 4/1/2023 | 4/2/2023 | 4/3/2023 | 4/4/2023 |
---|
16 | 3 | Mustafa | 3/4/2023 | 16:34 | | | | before 8:00 AM | 1 | 3 | | | | |
---|
17 | 3 | Mustafa | 4/4/2023 | 12:08 | | | | after 8:00 AM | 1 | 3 | 23:06 | 12:48 | | 12:08 |
---|
18 | 3 | Mustafa | 4/4/2023 | 22:36 | | | | | 2 | 3 | | | | 22:36 |
---|
19 | | | | | | | | | 3 | 3 | | | | |
---|
20 | | | | | | | | | 4 | 3 | | | | |
---|
21 | | | | | | | | | 5 | 3 | | | | |
---|
22 | | | | | | | | | 6 | 3 | | | | |
---|
|
---|