Forum Discussion

jbtm's avatar
jbtm
Copper Contributor
Nov 15, 2024

Date and countifs formulas not working

Hi! 

I am trying to use two formulas, and neither are returning the values they should. 

FORMULA 1

The first formula should be inputting a value based on a date range. I want the cell to say CO16 if the date in column K falls between 01/01/2024 and 06/30/2024 and CO17 if the date in column K falls between 07/01/2024 and 12/31/2024, otherwise leave blank. Here is the formula I am using, and it is not working:

=IF(AND(K3>=DATE(2024,1,1),K3<=DATE(2024,6,30)),"CO16",IF(AND(K3>=DATE(2024,7,1),K3<=DATE(2024,12,31)),"CO17",""))

I also tried: =IF(AND(K6>=DATEVALUE("1/1/2024"),K6>=DATEVALUE("6/30/2024")),"CO16","CO17"), which is the only formula so far that returns the correct value 75% of the time, even though it is wrong.

I confirmed column K is a short date data type.

FORMULA 2

The second formula needs to count how many cases fall into the date ranges for CO16 that also contain the word in a cell. The easiest way would be if I could countifs and rely on the formula above which is not working. So, I tried this and it is also not working:

=COUNTIFS('DC All'!K:K,">=01/01/2024",'DC All'!K:K,"<=06/30/2024",'DC All'!S:S, "Harris")

If there is an easier way to include the words in say, cell A1 instead of writing Harris (the word in cell A1) then I would love that. 

 

Thank you for any help!!

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 15, 2024

    Formula 1:

    =IF(AND(K3>=DATE(2024,1,1),K3<=DATE(2024,6,30)),"CO16",IF(OR(K3>=DATE(2024,7,1),K3<=DATE(2024,12,31)),"CO17",""))

    or shorter

    =IF(AND(K3>=DATE(2024,1,1),K3<=DATE(2024,6,30)),"CO16","CO17")

    Formula 2:

    =COUNTIFS('DC All'!K:K,">="&DATE(2024,1,1),'DC All'!K:K,"<="&DATE(2024,6,30),'DC All'!S:S, A1)

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 16, 2024

    More variants for Formula 1, assuming you could have dates out of year 2024 (or blank cells)

    =IF( K3>DATE(2024,12,31), "",
  IF( K3>DATE(2024,6,30), "CO17",
  IF( K3>DATE(2023,12,31), "CO16", "")
) )

    or

    = SWITCH( TRUE,
    K3 > DATE(2024,12,31), "",
    K3 > DATE(2024,6,30), "CO17",
    K3 > DATE(2023,12,31), "CO16",
    ""
)

    or

    =IFS(
  K3 > DATE(2024,12,31), "",
  K3 > DATE(2024,6,30), "CO17",
  K3 > DATE(2023,12,31), "CO16",
  TRUE, ""
)

Resources