Counting the number of calendar days in a range with conditions

[djmsenior]

I'm trying to find the number of calendar days in a range with conditions.

The list is has 2 columns

Column A has the names
Column B has the date & time - the data isn't being exported correctly so I'm having to use DATEVALUE to convert it

I thought I could use:
=countifs(DATEVALUE(B2:B15),">="&C3,DATEVALUE(B2:B15),"<="&D3,A2:A15,D2)

But this isn't working, so any help gratefully received.

A	B	C	D
Agent	Start	June	Ernest Rookwood
Elton John	2023-06-01 08:51:39	01/06/2023​	30/06/2023​
Elton John	2023-06-02 07:56:56
Elton John	2023-06-05 08:47:58
Elton John	2023-06-07 13:46:24
Freddie Mercury	2023-06-01 08:51:39	=countifs(DATEVALUE(B2:B15),">="&C3,DATEVALUE(B2:B15),"<="&D3,A2:A15,D2)
Freddie Mercury	2023-06-02 07:56:56
Freddie Mercury	2023-06-05 08:47:58
Freddie Mercury	2023-06-07 13:46:24
Freddie Mercury	2023-06-08 13:34:37
Freddie Mercury	2023-06-09 13:46:21
Freddie Mercury	2023-06-12 07:59:57
Freddie Mercury	2023-06-13 13:05:49
Freddie Mercury	2023-06-14 11:01:36
Freddie Mercury	2023-06-15 10:53:17

 

[Tetra201]

See if the following formulas work for you:

=SUMPRODUCT(--(DATEVALUE(B2:B15)>=C3),--(DATEVALUE(B2:B15)<D3+1),--(A2:A15=D2))
=SUMPRODUCT(--(--B2:B15>=C3),--(--B2:B15<D3+1),--(A2:A15=D2))

 

[djmsenior]

See if the following formulas work for you:

=SUMPRODUCT(--(DATEVALUE(B2:B15)>=C3),--(DATEVALUE(B2:B15)<D3+1),--(A2:A15=D2))
=SUMPRODUCT(--(--B2:B15>=C3),--(--B2:B15<D3+1),--(A2:A15=D2))

Hey Tetra201 - many thanks for responding so quickly!

Your formula works but it's highlighted the fact that I haven't asked the right question!!

With the following dataset:

Elton John	2023-06-01 08:51:39
Elton John	2023-06-01 16:14:11
Elton John	2023-06-01 16:46:34
Elton John	2023-06-02 08:15:53
Freddie Mercury	2023-06-09 13:46:16
Freddie Mercury	2023-06-09 13:46:17
Freddie Mercury	2023-06-09 13:46:18
Freddie Mercury	2023-06-09 13:46:19
Freddie Mercury	2023-06-09 13:46:20
Freddie Mercury	2023-06-09 13:46:21
Freddie Mercury	2023-06-12 07:59:57
Freddie Mercury	2023-06-13 13:05:49
Freddie Mercury	2023-06-14 11:01:36
Freddie Mercury	2023-06-15 10:53:17

I want to workout that:
Elton John has worked on 2 separate days
Freddie Mercury has worked on 5 separate days

 

[Tetra201]

The following formula returns your expected results:

=COUNT(UNIQUE(FILTER(INT(B2:B15),(--B2:B15>=C3)*(--B2:B15<D3+1)*(A2:A15=D2))))

 

[djmsenior]

The following formula returns your expected results:

=COUNT(UNIQUE(FILTER(INT(B2:B15),(--B2:B15>=C3)*(--B2:B15<D3+1)*(A2:A15=D2))))

Thank you so much. I think it may be time for dinner.