Excel & getting Week number from Date?

paulgalway · 2011-05-16 23:30:29

I have the date in Cell A1 and would like to work out the week # from the date. Monday would be the start of the week . So, 16th May would be week 20 and 23rd May would be week 21. Anyone know the formula I could use.

17-05-2011 12:30AM

#1
paulgalway

Registered Users, Registered Users 2 Posts: 535 ✭✭✭

Join Date: November 2008

Posts: 432
I have the date in Cell A1 and would like to work out the week # from the date.
Monday would be the start of the week . So, 16th May would be week 20 and 23rd May would be week 21.
Anyone know the formula I could use.

Tagged:

excel date

0

Comments

#2 17-05-2011 10:11AM

Buscuits

Closed Accounts Posts: 16 ✭

Join Date: February 2011

Posts: 16

Weeknum.
Click cell B1 and function Weeknum.
If you have 16/05/2011 in cell enter this into the formula and you get a return of 21.
Return type of 1 or 2 but defaults to 1 anyway.

0
#3 17-05-2011 03:23PM

Mr November

Registered Users, Registered Users 2 Posts: 64 ✭✭

Join Date: March 2011

Posts: 57

Here's a formula if that works better for you...

=TRUNC(A1+(1-(IF((WEEKDAY(A1))=1,7,SUM((WEEKDAY(A1))-1))))-DATE(111,1,3))/7

:pac::pac::pac::pac::pac::pac:

0