Monday, Tuesday, etc.) from a date, there are several options depending on your needs. If you only want to display a day name, you don't need a formula – you can use a...

So, you have to subtract 7 days from January 5 and hence you get -2 in the first DATE function: To make sure the formulas always deliver the right dates, please have a look at the following screenshot.The date must be in a form that Excel recognizes as a valid date.The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate the week number. In the example, the formula is: B4 contains a date value for January 5, 2016.Formulas are the key to getting work done in Excel.In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges.

So, you have to subtract 7 days from January 5 and hence you get -2 in the first DATE function: To make sure the formulas always deliver the right dates, please have a look at the following screenshot.

The date must be in a form that Excel recognizes as a valid date.

The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate the week number. In the example, the formula is: B4 contains a date value for January 5, 2016.

Formulas are the key to getting work done in Excel.

In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges.

If you need to extract the year from a date, you can use the YEAR function.