Forum Discussion
Potential bug in Excel
Hi guys, I found weird behavior in Excel that might be a bug. I tried using the WEEKDAY() function with TODAY() to provide the current date, so I could get the date of the first day of the current week. It has consistently given me a date back in 1900.
The actual formula I am entering is:
WEEKDAY(TODAY(), 1)
This produced the following output:
1/4/1900
From Microsoft's official documentation, WEEKDAY() has the following syntax:
WEEKDAY(serial_number,[return_type])
If I understand correctly, I should be able to get the serial representation of today's date for WEEKDAY() from TODAY(), but that isn't what is happening.
I tried putting TODAY() in a separate cell and referencing that cell, and there was no change. I also tried formatting that cell which had the output of TODAY() as a number and manually typing the serial representation of the output of TODAY() into the first parameter of WEEKDAY(), but it gave me the same result.
At first, this looked to me like I simply didn't understand how to use WEEKDAY(), but now it's looking to me like a bug. Should I report this to Microsoft, or am I just missing something on how to use WEEKDAY()?
The date 1/4/1900 is the default date format for Excel.
When you enter a formula that returns a number, such as WEEKDAY(TODAY(), 1), Excel may automatically format the cell as a date and display the result as a date.
Since the WEEKDAY function returns a number between 1 and 7, Excel interprets this as the number of days since January 0, 1900 (which is the default starting date for Excel), and displays the result as a date in January 1900.
The WEEKDAY function returns a number representing the day of the week for a given date. The TODAY function returns the current date.
So, WEEKDAY(TODAY(), 1) returns a number representing the day of the week for today’s date.
To get the date of the first day of the current week, you can use a formula like this:
=TODAY()-WEEKDAY(TODAY(),2)+1.
This formula subtracts the weekday number from today’s date and adds 1 to get the date of the first day of the current week (assuming that the week starts on Monday).
I hope this helps!
I know I don't know anything (Socrates)
The date 1/4/1900 is the default date format for Excel.
When you enter a formula that returns a number, such as WEEKDAY(TODAY(), 1), Excel may automatically format the cell as a date and display the result as a date.
Since the WEEKDAY function returns a number between 1 and 7, Excel interprets this as the number of days since January 0, 1900 (which is the default starting date for Excel), and displays the result as a date in January 1900.
The WEEKDAY function returns a number representing the day of the week for a given date. The TODAY function returns the current date.
So, WEEKDAY(TODAY(), 1) returns a number representing the day of the week for today’s date.
To get the date of the first day of the current week, you can use a formula like this:
=TODAY()-WEEKDAY(TODAY(),2)+1.
This formula subtracts the weekday number from today’s date and adds 1 to get the date of the first day of the current week (assuming that the week starts on Monday).
I hope this helps!
I know I don't know anything (Socrates)
Once I enter the formula in a cell for the current week. How do I lock in the date when I add a new(copy) sheet. The following week will show the new week's date, but I want to keep the date the same as the date I created it. NikolinoDE
Select the cell with the formula.
Copy it, then click the lower half of the Paste button on the Home tab of the ribbon. and select Values.
This will replace the formula with its result.
