Function Problems

Excel IF Formula for Dates, Blank Months, and Leap Years

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

Create a date formula that handles blank inputs and accounts for different month lengths and leap years.

Use Excel IF Formulas for Dates, Blank Months, and Leap Years
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet stores a month, year, and day in separate cells and needs to return a date or the last day of the selected month.
Observed behavior
The formula should return a date value rather than quoted formula text, leave missing inputs blank, and account for February in leap years.
Before you start

Enter the month number in B2, the year in B3, and the day number in A35. Use a whole number from 1 to 12 for the month and a valid day for that month.

Solution 1Recommended

Return a date only when the inputs are filled

Use IF and DATE to create a date while leaving the result blank if the month, year, or day is missing.

1
Enter the formula

Select the result cell and enter =IF(OR(A35="",$B$2="",$B$3=""),"",DATE($B$3,$B$2,A35)).

2
Format the result as a date

Select the result cell, then choose Home > Number Format > Short Date or another date format.

3
Check the inputs

Enter a valid month, year, and day in B2, B3, and A35. Clear any one of those cells to confirm that the result is blank.

Return a date only when the inputs are filled
Avoid quoted formulas and invalid days: Do not put quotation marks around DATE: that would return text instead of calculating a date. DATE also rolls an out-of-range day into another month, so check that the day is valid for the selected month.
Calculate dates in WPS Spreadsheet

Create date and month-end formulas in WPS Spreadsheet

Use IF, DATE, and EOMONTH in WPS Spreadsheet to leave missing inputs blank and calculate dates across different month lengths and leap years.

  1. 1. Open your spreadsheet: Open WPS Spreadsheet and choose Open to select your workbook.
  2. 2. Enter the inputs: Enter a month number in B2, a year in B3, and a valid day number in A35.
  3. 3. Add the date formula: Select a result cell and enter =IF(OR(A35="",$B$2="",$B$3=""),"",DATE($B$3,$B$2,A35)).
  4. 4. Format and save: Use Home > Number Format to display the result as a date, then choose File > Save As to save the workbook.
Create dates from separate month, year, and day cellsCalculate month-end dates without manually checking leap yearsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and formatting controls
microsoft office alternative - wps office

Frequently Asked Questions

Why does my IF formula show DATE as text?

Quotation marks around DATE make it text. Enter DATE($B$3,$B$2,A35) without quotes inside the IF formula; use "" only for the blank result.

How can I leave the result blank when the month is missing?

Include the month cell in the IF test. For example, =IF(OR(A35="",$B$2="",$B$3=""),"",DATE($B$3,$B$2,A35)) leaves the result blank if any of the three inputs is empty.

How do I return the last day of February?

With 2 in B2 and the year in B3, enter =EOMONTH(DATE(B3,B2,1),0). Format the result as a date; Excel automatically accounts for leap years.

Can I test whether a year is a leap year?

For a year in B3, enter =IF(MOD(B3,400)=0,"Leap year",IF(MOD(B3,100)=0,"Not a leap year",IF(MOD(B3,4)=0,"Leap year","Not a leap year"))).

Why did a day number produce a date in the next month?

DATE rolls out-of-range days forward. For example, day 31 in a 30-day month becomes the first day of the next month. Check the maximum day with =DAY(EOMONTH(DATE(B3,B2,1),0)).