If the WEEKDAY function in Excel isn’t working as expected, it could be due to several factors related to the format and recognition of dates in your worksheet. The issues can range from unrecognized date formats to incorrect cell formatting.
Table of contents
YouTube: Excel WEEKDAY function not working
Unrecognized Dates
A common issue occurs when Excel does not recognize the values in your cells as dates. Even if the data looks like a date, Excel may not interpret it as such, leading to a #VALUE! error.
You can verify this by highlighting the cells, right-clicking and selecting Format Cells or use the shortcut Ctrl + 1. In the below dialog box that appears, choose different date formats to see if the format in the Sample box changes. If it doesn’t change then Excel doesn’t recognize the dates highlighted as valid dates.
Wrong Output Due to Incorrect Formatting
If your WEEKDAY function returns a date instead of the expected weekday numbers (1 to 7), the problem likely lies in the cell formatting.
To correct this, highlight the cells, right click and select Format Cells and then choose Number as shown below.
When formatted as numbers, the WEEKDAY function will return the correct value. Below the date output is converted to weekday numbers.
Year Formatting Issues
Another potential issue arises from how Excel interprets two-digit years. In the example below, 01/12/09 may be interpreted as December 1, 2009, or December 1, 1909, depending on how Excel reads the year.
To avoid this, always input your dates with a four-digit year. To adjust the format, highlight the cells, right click, choose Format Cells, and select a date format that uses four digits for the year. The image below illustrates the difference between a two-digit and four-digit year format.
#NUM! Errors with Large Numbers
A #NUM! error in the WEEKDAY function can occur when Excel attempts to interpret a large number as a date. If the number is too large for Excel to convert, it will trigger the error. To fix this, ensure that the number is broken down into valid year, month, and day components. In the image below, we demonstrate how breaking up large numbers into valid date components resolves the issue.
Tips and Troubleshooting.
- Be Consistent with Date Formats: Ensure consistency in your date formats across your spreadsheet to avoid errors when using date-related functions like WEEKDAY.
- Check Regional Settings: Excel’s date interpretation can depend on your regional settings. Check that your system’s date format aligns with how your data is entered, especially when working with international date formats.