Excel Weeknum函数出现#VALUE!错误，求周数显示及空白设置方案

Hi there! I see you're hitting a #VALUE! error with Excel's WEEKNUM function when dealing with empty or non-date cells, and you want to only display week numbers for cells with valid dates while leaving others blank. Let's sort this out step by step.

Why the #VALUE! Error Occurs #

The #VALUE! error pops up because WEEKNUM requires a valid date as its input. When you run it on an empty cell or a cell that doesn’t hold a proper date value (like plain text or a blank), Excel can’t compute the week number and throws this error—exactly what’s happening in your screenshot with those empty rows.

Solution: Use IF + ISDATE to Validate Inputs #

You can wrap your WEEKNUM function in an IF statement paired with ISDATE to check if the cell contains a valid date first. If it does, calculate the week number; if not, return a blank cell.

Here’s the formula to use (replace A1 with your actual date cell reference):

=IF(ISDATE(A1), WEEKNUM(A1, 2), "")

Breakdown of the Formula:

• ISDATE(A1): Checks if cell A1 holds a valid date. Returns TRUE if it does, FALSE otherwise.
• WEEKNUM(A1, 2): Calculates the week number. The second parameter 2 sets Monday as the first day of the week (use 1 if you want Sunday as the start—adjust this based on your regional or personal preference).
• "": Returns an empty string (blank cell) if the input isn’t a valid date.

How to Apply This to Your Workbook #

1. Select the cell where you want the week number to appear.
2. Paste the formula above, updating the cell reference to match your date column.
3. Drag the fill handle down to apply the formula to all relevant rows.

This will automatically show the week number for cells with valid dates and leave empty rows blank, just as you need.

内容的提问来源于stack exchange，提问作者San