Find the nearest Monday for date which is more useful than Weeknum()

Find the Monday for a week that a date falls into

=A2-(MOD(WEEKDAY(A2)-2,7))

Where A2 is the field with the date.

The great thing is that

  1. It works across year breaks and you don’t end up with Week 53
  2. It fits with SQL  – date_trunc(‘week’, Date)  – functions so you can correctly match Excel data
Advertisements

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