Sumproduct to match dates with different formats

[jwbrouse01]

Greetings.

I am struggling to modify my sumproduct formula that will count for items that were completed on time. Column L has my completion date with date and time (01/30/2024 09:18), but Column H is always set to 01/30/2024 00:00.

If I do a direct comparison - --(Sheet1!$L:$L<=Sheet1!$H:$H) - it only gives a count of the tasks that were completed earlier than the due date. How do I alter my sumproduct to include the due date?

Any help is appreciated!

 

[Coyotex3]

Can you post a sample of your data so we can see what you are working with?

One option would be to convert the dates in Columns L and H using the text formula such as

TEXT(H1,"mm/dd/yyyy")

This should allow you to do a direct comparison. It is hard for us to come up with a robust formula without some sample data.

 

[Peter_SSs]

Is this what you are after?
I assumed that if completed at any time on the target date that counted as on time (eg row 3)
My dates are d/m/y format
(I manually coloured the ones I think you are trying to count)

24 02 01.xlsm
	F	G	H				L
1	Count on time		Target Completion				Completion
2	8		26/01/2024 00:00				12/01/2024 17:34
3			05/01/2024 00:00				05/01/2024 10:52
4			05/01/2024 00:00				30/01/2024 07:40
5			08/01/2024 00:00				15/01/2024 09:21
6			03/01/2024 00:00				13/01/2024 20:47
7			01/01/2024 00:00				30/01/2024 03:32
8			01/02/2024 00:00				27/01/2024 09:03
9			04/01/2024 00:00				20/01/2024 01:37
10			16/01/2024 00:00				30/01/2024 10:01
11			15/01/2024 00:00				04/01/2024 02:20
12			04/01/2024 00:00				27/01/2024 20:36
13			04/01/2024 00:00				02/01/2024 21:33
14			29/01/2024 00:00				11/01/2024 12:11
15			06/01/2024 00:00				02/01/2024 05:41
16			19/01/2024 00:00				12/01/2024 18:49
On time

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(--(INT(L2:L16)<=H2:H16))