Excel day of week from date | Tutorial

Excel day of week from date

Excel day of week from date

If you want to store and print daily work in Excel like in a form, you can save work in some places. For example, you can have Excel read the day of week from the date. Thus, entering the date ensures that other entries are automatically executed. If the day of the week plays a role in the respective work, this application can therefore be very useful. In addition, other options are available that follow the same principle, but provide other data for the user.

Determine calendar week from date in Excel:

Besides the day of the week, it is also possible to determine the calendar week from the date and enter it automatically. How does it all work? A date is defined with a consecutive number in Excel. By converting the number and the date, exactly the correct weekday or calendar week can be matched with other stored data. This is not a lot of work for Excel, but for us it can be a time saver that goes far beyond entering, as we won’t have to research the necessary data either.

Step-by-step instructions:

So you can determine the day of the week from the date in Excel:

  • To set the appropriate date for each day, you can use the function =TODAY(). If necessary you can also use fixed dates
  • Option 1 (with reference):
    • The date (whether =TODAY() or fixed date) is in cell A1
    • Use the function: =WEEKDAY(A1)
  • Option 2 (without reference):
    • Use the function: =WEEKDAY(TODAY()) or WEEKDAY(01.01.2000) (date arbitrarily adaptable)

Important: The result is still output as a number. It is important to change the formatting of the cells:

  • Select the cells
  • Right-click and select “Format Cells”
  • Switch to the “Custom” tab
  • Enter DDD or DDDD as “Type
    • DDD gives the day in short form (e.g. Mo for Monday)
    • DDDD gives the complete day
  • Press OK

This is how you can determine the calendar week from a date in Excel:

  • To determine the appropriate calendar week from a date, you can use the function =TODAY(). If necessary, you can also use fixed dates
  • Option 1 (with reference):
    • The date (whether =TODAY() or fixed date) is in cell A1
    • Use the function: =WEEKNUM(A1)
  • Option 2 (without reference):
    • Use the function: =WEEKNUM(TODAY()) or WEEKNUM(01.01.2000) (date arbitrarily adaptable)

It’s a bit confusing, because just for the day of the week Excel provides a suitable function, but in the fewest cases it will provide what you need. Therefore, you should know the appropriate formatting of the cells, so that you can create the appropriate result for your own requirements. We hope that this explanation has helped you and wish you continued success with Microsoft Excel.


Video tutorial:

coming soon

Links with * are affiliate links. By purchasing via this link we receive a part of the purchase price as commission without any additional costs for you. This helps us to expand the offers available here for you! Thank you!

Be the first to comment

Leave a Reply