Date function not working when embedded

[Ronderbecke]

I have embedded into a formula a simple function for using sumproduct and in the middle of the function is this:
=Date(2022,12,1)
It works fine like that but when I have 2022,12,1 in cell E4 I can't just use =DATE(E4) and have tried using text function and indirect function and nothing is working. I know this is probably something stupid I am missing but how do you change out what I have in date and use data from a cell that looks literally the exact same?

 

[Fluff]

Why not enter the date as a real date, rather than text.

 

[Ronderbecke]

Why not enter the date as a real date, rather than text.

=SUMPRODUCT(IF('Copy of Calculator'!$D2:$D100=A4,IF('Copy of Calculator'!$B2:$B100-DAY('Copy of Calculator'!$B2:$B100)+1=Date(2022,12,1),'Copy of Calculator'!$F2:$F100)))

How do you mean enter a real date? That's the whole function, would changing the date format alleviate the issue?

 

[Fluff]

If you want to use E4 for the date, then you need to enter it as a proper date in whatever format your date system is.
So for the UK that would be 01/12/2022

 

[Ronderbecke]

If you want to use E4 for the date, then you need to enter it as a proper date in whatever format your date system is.
So for the UK that would be 01/12/2022

Ah, I tried that and received this error:
ErrorWrong number of arguments to DATE. Expected 3 arguments, but got 1 arguments.

 

[Fluff]

If you want the date in a cell then use

=SUMPRODUCT(IF('Copy of Calculator'!$D2:$D100=A4,IF('Copy of Calculator'!$B2:$B100-DAY('Copy of Calculator'!$B2:$B100)+1=E4,'Copy of Calculator'!$F2:$F100)))

 

[Ronderbecke]

If you want the date in a cell then use

=SUMPRODUCT(IF('Copy of Calculator'!$D2:$D100=A4,IF('Copy of Calculator'!$B2:$B100-DAY('Copy of Calculator'!$B2:$B100)+1=E4,'Copy of Calculator'!$F2:$F100)))

Ah, I see what I did wrong when I originally tried that. Like I said, something stupid. Sorry, fixed.

 

[Fluff]

You're welcome & thanks for the feedback.