Date Function

[excelesha]

Hello, so i have a countifs formula, in there i am trying to find the accumulated amount from 2021 to a particular month in 2023, instead of having to change the 2023 month manually for each row, i want the 2023 month to be picked up from Col A instead, but i get an error if i replace "date(2023,8,31) with date(A1), see below formula in Col C:

=COUNTIFS(data44!$M:$M,"A",data44!$D:$D,">="&DATE(2021,1,1),data44!$D:$D,"<="&DATE(2023,8,31),data!$L:$L,"yes")

Col A	Col B	Col C
2023,8,31	Aug	44​
2023,9,31	Sept	67​
2023,10,31	Oct	69​
2023,11,31	Nov	89​

 

[Joe4]

What exactly do you have in cell A1?
Is it a valid date?
Is is it a string?

2023,8,31 does not look like a valid date to me, unless you have applied a Custom format of something like yyyy,m,d to that cell.

 

[excelesha]

What exactly do you have in cell A1?
Is it a valid date?
Is is it a string?

2023,8,31 does not look like a valid date to me, unless you have applied a Custom format of something like yyyy,m,d to that cell.

I manually entered the month end date in A1, but the goal is to capture the count till month end of the indicated month in B1.

 

[Joe4]

if i replace "date(2023,8,31) with date(A1)

If the date you entered in A1 is already a valid date, just replace it with A1, and not Date(A1).

If you have the three letter month abbreviation in cell B1, and want to get the end of the month date for that month in the current year, you can use this formula:

=EOMONTH(DATEVALUE("1-" & B1 & YEAR(TODAY())),0)

 

[excelesha]

If the date you entered in A1 is already a valid date, just replace it with A1, and not Date(A1).

If you have the three letter month abbreviation in cell B1, and want to get the end of the month date for that month in the current year, you can use this formula:

=EOMONTH(DATEVALUE("1-" & B1 & YEAR(TODAY())),0)

the count is 0 if i reference to A1 directly......

 

[Joe4]

the count is 0 if i reference to A1 directly......

OK, so then what you entered into cell A1 is a string and not an actual valid date then.
If you want to use A1, can you enter an actual valid date instead of a string?

Or did you try my other suggestion?

 

[excelesha]

i changed the format of the call A1 to date, now it is working, thank you so much!

 

[Joe4]

You are welcome.