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

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.
--
David Biddulph

"Wendy" wrote in message
...
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

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)



"Wendy" wrote in message
...
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

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




"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Wendy" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
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)



"Wendy" wrote in message
...
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

On Thu, 20 Mar 2008 09:02:38 -0000, "Wendy" wrote:

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



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

On Thu, 20 Mar 2008 09:02:38 -0000, "Wendy" wrote:

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



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