Excel IF Formula for Dates, Blank Months, and Leap Years
Question details
Create a date formula that handles blank inputs and accounts for different month lengths 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.
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.
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.
Select the result cell and enter =IF(OR(A35="",$B$2="",$B$3=""),"",DATE($B$3,$B$2,A35)).
Select the result cell, then choose Home > Number Format > Short Date or another date format.
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.

Calculate the last day of the selected month
Use EOMONTH when you need the month-end date without manually listing month lengths.
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. Open your spreadsheet: Open WPS Spreadsheet and choose Open to select your workbook.
- 2. Enter the inputs: Enter a month number in B2, a year in B3, and a valid day number in A35.
- 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. Format and save: Use Home > Number Format to display the result as a date, then choose File > Save As to save the workbook.

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)).




