Forum Discussion
trunimoser
Apr 12, 2023Copper Contributor
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 we...
- Apr 12, 2023
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)
NikolinoDE
Apr 12, 2023Gold Contributor
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)
trunimoser
Apr 12, 2023Copper Contributor