Problem with week number/day type formula

W

Wendy

Hi

I have a formula to work out the week number and day per month..
C1 ="W"&CEILING(DAY(B1)/7,1)&TEXT(WEEKDAY(B1),"DDD") and B1=TODAY( )

It was working fine but today should be W2Mon and the formula gives W1Mon.

Thanks

Wendy
 
J

Joel

The code is right. Check the data on you PC to make sure your have the right
date set. You should get 8/7 = 1.333 and the ceiling function should round
up to 2.
 
R

Ron Rosenfeld

Hi

I have a formula to work out the week number and day per month..
C1 ="W"&CEILING(DAY(B1)/7,1)&TEXT(WEEKDAY(B1),"DDD") and B1=TODAY( )

It was working fine but today should be W2Mon and the formula gives W1Mon.

Thanks

Wendy
Click to expand...

The problem may be that you were not clear in your specifications. For
example, you wrote:

" 2nd Monday is always W2Mon "

Since today is the First Monday of April, then either you will have two W2Mon's
or you need to rethink your specifications.
--ron
 
R

Ron Rosenfeld

The code is right. Check the data on you PC to make sure your have the right
date set. You should get 8/7 = 1.333 and the ceiling function should round
up to 2.
Click to expand...

From where are you getting the "8"?

--ron
 
W

Wendy

Hi Joel

When I do 8/7 my answer is 1.142857. Windows XP, Office 2003 sp2. It is
working ok today!

Wendy
 
W

Wendy

Hi Ron

It doesn't matter it is the 1st monday in april it is still the start of
week2.
Can you suggest a way round this to make it work?

Thanks

Wendy
 
R

Ron Rosenfeld

Hi Ron

It doesn't matter it is the 1st monday in april it is still the start of
week2.
Can you suggest a way round this to make it work?
Click to expand...


With the specifications you have supplied, only by making both yesterday and
next Monday = W2Mon.

You have written:

" 2nd Monday is always W2Mon "

and also:

"It doesn't matter it is the 1st monday in april it is still the start of
week2."

The only way I see to resolve these two statements is to make both yesterday
and next Monday to be W2Mon.

If that is not what you want, then you will have to lay out unambiguous
specifications.
--ron
 
Top