Prevent Excel MONTH Function From Returning January for Blank Date Cells
Question details
Prevent blank date cells from being returned or counted as January when using the MONTH function in Excel.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is using MONTH in a waiting-time calculation that includes date cells which may be blank.
- Observed behavior
- MONTH returns 1 for blank cells because Excel treats a blank value as zero, which can be interpreted as January 1, 1900.
Check that the date column contains true Excel dates rather than text values. Identify the cells that can legitimately remain empty so the formula can test for them before calculating the month.
Test for blank date cells before using the MONTH function
Use this formula when you need MONTH to return a blank result instead of January for an empty date cell.
Excel can evaluate a blank cell as zero. When zero is interpreted as a date serial number, MONTH returns 1, representing January. Wrapping MONTH in an IF test prevents the calculation for empty cells.
Click the cell where you want to display the month result, such as the cell next to date cell Y4.
Type =IF(Y4="","",MONTH(Y4)) and replace Y4 with the date cell used in your worksheet.
Press Enter, then drag the fill handle down the column to apply the formula to the remaining records.

Average the relevant ranges without converting blank dates to months
Use this approach when your goal is an average waiting time or date-based result and you do not need a separate MONTH value for every row.
Check whether date entries are stored as real dates
Use this check if nonblank cells also return unexpected month results or do not behave like dates.
Keep Blank Dates Out of MONTH Results in WPS Spreadsheets
WPS Spreadsheets supports the same IF and MONTH formula pattern, so you can leave empty date cells blank instead of displaying January. Enter the formula once, then fill it down to apply consistent waiting-time calculations across your worksheet.
- 1. Open the worksheet in WPS Spreadsheets: Open your Excel workbook in WPS Office and select the sheet containing the date column.
- 2. Select the month result cell: Click the cell where the MONTH result should appear next to the first date entry.
- 3. Enter the protected MONTH formula: Type =IF(Y4="","",MONTH(Y4)), changing Y4 to match your date cell, and press Enter.
- 4. Fill the formula through the column: Drag the fill handle from the formula cell down through the remaining rows.

Frequently Asked Questions
Why does MONTH return 1 when the referenced date cell is blank?
Excel can treat a blank referenced cell as zero. When that value is interpreted as a date serial number, it corresponds to January 1, 1900, so MONTH returns 1.
What formula returns a blank result when the date cell is empty?
Use =IF(Y4="","",MONTH(Y4)). The IF portion checks whether Y4 is blank before MONTH evaluates the date.
Can I use ISBLANK instead of comparing the cell to an empty string?
Yes. Use =IF(ISBLANK(Y4),"",MONTH(Y4)) for truly empty cells. The =IF(Y4="","",MONTH(Y4)) version can also handle cells whose formulas return an empty string.
Will AVERAGE ignore blank cells in a waiting-time range?
Yes, AVERAGE generally ignores blank cells in the range. Use AVERAGEIFS when you need to average values only for rows that meet specific criteria, such as a nonblank related date cell.




