Forum Discussion

trunimoser's avatar
trunimoser
Copper Contributor
Apr 12, 2023

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...
  • NikolinoDE's avatar
    Apr 12, 2023

    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)

Resources