Week number date formula

W

Wendy

Hi

I'm trying to write a formula to show the date where 20/03/2008 would show
as W3Thur and 31/03/08 would show as W5Mon and 01/04/2008 would show as
W1Tue.

I have A1= 20/03/2008 and B1= "W"&weeknum(workday(a1,0),1)/4&text
(weekday(a1),"ddd")

It currently works fine if the week number divides by 4 as a whole number,
but I don't want the fractions!

Thanks

Wendy
 
D

David Biddulph

To get rid of the fractions, change your formula to
= "W"&INT(WEEKNUM(WORKDAY(A1,0),1)/4)&TEXT(WEEKDAY(A1),"ddd")
but 1/4/08 is still week 3, not week 1.
 
B

Bob Phillips

Try this

="W"&ROUNDUP(WEEKNUM(WORKDAY(A1,0),1)/4,0)&TEXT(WEEKDAY(A1),"ddd")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
W

Wendy

Hi David

Thanks, but unfortunately that doesn't do what I want. The 1st of the month
is always W1, 2nd Monday is always W2Mon etc.
Int shows what is W4 as W3 because it rounds 3.25 down rather than up. I
need week numbers per month.

Wendy
 
W

Wendy

Hi Guys

Neither Roundup nor Int works for every day in the month. 1st working day
of the month is always W1ddd.

My original formula only works correctly on week 3
Int formula shows weeks 2 and 3 correctly.
Roundup formula shows weeks 3 and 4 correctly.

Thanks

Wendy
 
R

Ron Rosenfeld

Hi

I'm trying to write a formula to show the date where 20/03/2008 would show
as W3Thur and 31/03/08 would show as W5Mon and 01/04/2008 would show as
W1Tue.

I have A1= 20/03/2008 and B1= "W"&weeknum(workday(a1,0),1)/4&text
(weekday(a1),"ddd")

It currently works fine if the week number divides by 4 as a whole number,
but I don't want the fractions!

Thanks

Wendy
Click to expand...

Not sure exactly what you mean. Is Day 1 of every month W1ddd?

If so, then

="W"&INT((DAY(A1)-1)/7)+1&TEXT(A1,"ddd")

should do it.

If you mean that the first Monday is when W1 starts, then what about the days
in the month prior to the first Monday?
--ron
 
R

Ron Rosenfeld

Hi

I'm trying to write a formula to show the date where 20/03/2008 would show
as W3Thur and 31/03/08 would show as W5Mon and 01/04/2008 would show as
W1Tue.

I have A1= 20/03/2008 and B1= "W"&weeknum(workday(a1,0),1)/4&text
(weekday(a1),"ddd")

It currently works fine if the week number divides by 4 as a whole number,
but I don't want the fractions!

Thanks

Wendy
Click to expand...

I'm not sure of the details of your week counting method.

You wrote, "The 1st of the month
is always W1, 2nd Monday is always W2Mon"

Does that mean that, for example 1 Feb 2008 would be W1Fri?

If so, then simply:

="W"&INT((DAY(A1)-1)/7)+1&TEXT(A1,"ddd")

or do you mean something else?

--ron
 
D

David Biddulph

Then obviously you don't want to use the WEEKNUM function.
And I realised belatedly that your TEXT(WEEKDAY(A1),"ddd") ought sensibly to
be merely TEXT(A1,"ddd").

If you are saying that the first Monday of the month is W1Mon, the 2nd
Wednesday of the month is W2Wed, the 4th Friday of the month is W4Fri, etc,
then try:
="W"&1+INT((DAY(A1)-1)/7)&TEXT(A1,"ddd")
 
D

David Biddulph

My formula can be simplified a bit further, to
="W"&INT((DAY(A1)+6)/7)&TEXT(A1,"ddd")
 
Top