Function Problems

Prevent Excel MONTH Function From Returning January for Blank Date Cells

Anum MushtaqAnum Mushtaq Nov 12, 2026 871 views

Question details

Prevent blank date cells from being returned or counted as January when using the MONTH function in Excel.

Prevent Excel MONTH Function From Returning January for Blank Date Cells
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the cell where you want to display the month result, such as the cell next to date cell Y4.

2
Enter the blank-check formula

Type =IF(Y4="","",MONTH(Y4)) and replace Y4 with the date cell used in your worksheet.

3
Apply the formula to other rows

Press Enter, then drag the fill handle down the column to apply the formula to the remaining records.

Test for blank date cells before using the MONTH function
Blank dates stay blank: When Y4 is empty, the formula returns an empty result. When Y4 contains a valid date, it returns that date's month number.
WPS Spreadsheets formula help

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. 1. Open the worksheet in WPS Spreadsheets: Open your Excel workbook in WPS Office and select the sheet containing the date column.
  2. 2. Select the month result cell: Click the cell where the MONTH result should appear next to the first date entry.
  3. 3. Enter the protected MONTH formula: Type =IF(Y4="","",MONTH(Y4)), changing Y4 to match your date cell, and press Enter.
  4. 4. Fill the formula through the column: Drag the fill handle from the formula cell down through the remaining rows.
Use =IF(Y4="","",MONTH(Y4)) to prevent blank dates from returning January.Fill formulas down quickly across waiting-time records.Open and edit Microsoft Excel .xlsx and .xls worksheets with compatible formulas.Use a familiar spreadsheet interface for date, criteria, and average calculations.
microsoft office alternative - wps office

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.