Forum Discussion

jorgen1990's avatar
jorgen1990
Copper Contributor
Feb 09, 2024

Excel month function counts blank cells as 1 (january)

Hi!

 

I am trying to make a sheet that calculates the average waiting time for clients in a healthcare facility.

 

The problem is that the month function of column AA counts blank cells in column Y as 1 (january). This ruins the average waiting time for january. 

 

Is there anhone that can help me with this? 

excel
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Feb 09, 2024

    jorgen1990 Because blank cell means 0 and in case of date this means 1/1/1900. And that is reason to return 1 by MONTH() function. But AVERAGE() should avoid blank cells by default. So, you can directly use AVERAGE() or AVERAGEIFS() function or use MONTH() function with IF() like.

    =IF(Y4="","",MONTH(Y4))
    • jorgen1990's avatar
      jorgen1990
      Copper Contributor
      Feb 12, 2024
      I am not sure if I understand. Is there any thing i can do with the cells in column Y so that they dont count as 1 when they are blank? I dont need a formula for average now 🙂

Resources