Potential bug in Excel

[trunimoser]

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()?

[#2 · Accepted solution]

@trunimoser 

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! 

NikolinoDE

I know I don't know anything (Socrates)

[#3]

Thanks@NikolinoDE , that fixed it!

That was a very good explanation too.