Week number date formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wendy - 20 Mar 2008 10:02 GMT

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

Reply to this Message

David Biddulph - 20 Mar 2008 10:19 GMT

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.

Signature

David Biddulph

> Hi
>
[quoted text clipped - 11 lines]
>
> Wendy

Reply to this Message

Wendy - 20 Mar 2008 10:34 GMT

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

> To get rid of the fractions, change your formula to
> = "W"&INT(WEEKNUM(WORKDAY(A1,0),1)/4)&TEXT(WEEKDAY(A1),"ddd")
[quoted text clipped - 14 lines]
>>
>> Wendy

Reply to this Message

David Biddulph - 20 Mar 2008 20:18 GMT

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")
--
David Biddulph

> Hi David
>
[quoted text clipped - 23 lines]
>>>
>>> Wendy

Reply to this Message

David Biddulph - 20 Mar 2008 20:27 GMT

My formula can be simplified a bit further, to
="W"&INT((DAY(A1)+6)/7)&TEXT(A1,"ddd")
--
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
[quoted text clipped - 34 lines]
>>>>
>>>> Wendy

Reply to this Message

Bob Phillips - 20 Mar 2008 10:21 GMT

Try this

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

Signature

---
HTH

Bob

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

> Hi
>
[quoted text clipped - 11 lines]
>
> Wendy

Reply to this Message

Wendy - 20 Mar 2008 11:28 GMT

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

> Try this
>
[quoted text clipped - 15 lines]
>>
>> Wendy

Reply to this Message

Ron Rosenfeld - 20 Mar 2008 13:52 GMT

>Hi
>
[quoted text clipped - 11 lines]
>
>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

Reply to this Message

Ron Rosenfeld - 20 Mar 2008 14:07 GMT

>Hi
>
[quoted text clipped - 11 lines]
>
>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

Reply to this Message