Formula Errors

How to Fix Excel WEEKDAY Formula Showing January 1900

Natalie TaylorNatalie Taylor Nov 10, 2026 868 views

Question details

The user is attempting to display a current date or weekday dynamically, but the formula outputs a date from January 1900 instead of the expected result.

How to Fix Excel WEEKDAY Formula Showing January 1900
Product
Excel
Device & OS
not provided
Scenario
Using the WEEKDAY function to display the current day or date and combining it with text in a spreadsheet cell.
Observed behavior
The cell displays a date such as January 1, 1900, or January 2, 1900, because a weekday integer (1-7) was incorrectly formatted as a date.
Before you start

Check your cell's number format. This issue typically occurs because a cell containing a simple integer from 1 to 7 is formatted as a 'Date' instead of 'General'.

Solution 1Recommended

Use TEXT and TODAY Functions to Display the Current Date

Replace the WEEKDAY function with TEXT and TODAY to correctly fetch and format the current date and day of the week.

Excel stores dates as sequential integers starting with January 1, 1900 (serial number 1). The WEEKDAY function strictly returns a number from 1 to 7. If you apply a Date format to a cell containing the number 2, Excel interprets it as the 2nd day in its system: January 2, 1900.

To properly display today's date along with the day of the week, you should use the TODAY function wrapped in a TEXT formatting formula.

1
Select the target cell

Click on the cell where you want the correct date to be displayed.

2
Clear the old formula

Press the Delete key to remove the problematic WEEKDAY formula.

3
Enter the corrected formula

Type the following formula: ="As of "&TEXT(TODAY(),"ddd mm/dd/yy")

4
Apply the formula

Press Enter. The cell will now properly display text like 'As of Mon 10/20/25' based on your current date.

Use TEXT and TODAY Functions to Display the Current Date
Formatting Flexibility: You can change the "ddd" in the formula to "dddd" if you prefer the full day name (e.g., Monday instead of Mon).
Powerful Spreadsheet Software

Easily Manage Dates and Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports all standard Excel functions like TODAY, WEEKDAY, and TEXT. It processes date serial numbers exactly like Microsoft Excel, ensuring your formulas work perfectly across both platforms.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the date errors.
  2. 2. Select the affected cell: Click on the cell currently showing the January 1900 date.
  3. 3. Update the formula: Replace the WEEKDAY formula with the TEXT and TODAY combination in the formula bar.
  4. 4. Press Enter: Hit Enter to execute the formula and instantly see the correct current date.
100% compatible with Microsoft Excel formulas and date serial systems.Intuitive formatting tools to quickly identify and fix number vs. date format mismatches.Free, lightweight, and fast alternative for all your data reporting needs.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel default to January 1900 when I use the WEEKDAY function?

Excel calculates dates using a serial number system that begins on January 1, 1900 (serial number 1). Since the WEEKDAY function returns a small integer between 1 and 7, formatting that cell as a Date tells Excel to display the 1st through 7th days of the year 1900.

How do I change my cell format from Date back to General?

Select the cell, go to the 'Home' tab on the ribbon, locate the Number Format drop-down menu in the 'Number' group, and select 'General'. This will reveal the underlying number rather than a calendar date.

Can the WEEKDAY function output the name of the day instead of a number?

No, the WEEKDAY function is strictly designed to return a number (1-7). To display the actual text name of the day, you should use the TEXT function, such as =TEXT(TODAY(), "dddd"), which will return 'Monday', 'Tuesday', etc.