Microsoft Excel – finding a week number

Although it is easy enough to work out the month or year of an Excel date, using the MONTH and YEAR functions, it is not as obvious to work out what week of the year a date falls in.

However there is a function called WEEKNUM which we can use.

The syntax for this is

=WEEKNUM(serial number, return type), where serial number is the cell containing the date and return type is a number 1 or 2 which indicates whether you want the week to begin on a Sunday (1 or omitted) or a Monday (2).

You will then see the relevant week number.

Weeknum

When using Pivot tables, it is easy enough to group dates by Day, Month, Quarter or Year, but not so easy to group on a weekly basis, unless you use the WEEKNUM function. If you use the resulting field as a row label in your Pivot Table, you then have weekly data.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Advertisements

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s