Day format in formula

[john.bedford3]

Can someone please tell me where I am going wrong with the formula in cell
K46.

I have the following in a spreadsheet :-

J K L M
43 01-May-2005 31 Sunday Date/Days Left in Month/Months
44 245 35.000 8.000 No. Days/weeks/Months Left
45 0.596 4.171 18.250 Target No. per Day/Week/Month
46 0.596 18.473 Target No. This Week/Month

The relevant formulas are as follows :-

J43
=IF(TODAY()>=DATE(2005,6,1),"",IF(TODAY()<DATE(2005,5,1),DATE(2005,5,1),TODA
Y()))

L43 =J43

This is formatted as dddd to produce the day of the week.

J45 =IF(J43="","",IF('Input List-Year Summary'!K41<0,ABS('Input
List-Year Summary'!K41)/J44,('Input List-Year Summary'!K41-(2*'Input
List-Year Summary'!K41))/J44))

The number in 'Input List-Year Summary'!K41 is -146 in this instance.

K46
=IF(L43="Sunday",7*J45,IF(L43="Monday",6*J45,IF(L43="Tuesday",5*J45,IF(L43="
Wednesday",4*J45,IF(L43="Thursday",3*J45,IF(L43="Friday",2*J45,J45))))))

What I want the formula in K46 to do is to count the days left in the
current week ( week commencing on a Sunday) if today's date is in May and if
today is earlier that 01-May-2005 the days left in the week commencing
Sunday from 01-May-2005. I have obviously got the formula or format wrong
because the formula says that L43 does not equal Sunday even though that is
what is displayed in L43.
The answer in this instance should be 4.171

 

[Bob Phillips]

Try using WEEKDAY(L43)=1 instead of L43="Sunday", as you only have L43
formatted as dddd, it is still a full date.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[john.bedford3]

Thanks Bob it is obvious now you have explained it. I do not think I would
have got there without help.

Most appreciated.

 

[Bob Phillips]

Thanks okay John.

BTW, this formula

=IF(L43="Sunday",7*J45,IF(L43="Monday",6*J45,IF(L43="Tuesday",5*J45,IF(L43="
Wednesday",4*J45,IF(L43="Thursday",3*J45,IF(L43="Friday",2*J45,J45))))))


can be simplified with (with the aforementioned correction)

=CHOOSE(WEEKDAY(L3),7,6,5,4,3,2,1)*J45

or even

=(8-WEEKDAY(L43))*J45

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[john.bedford3]

Yes Bob, that is much simpler. Thanks very much.

 

[Peo Sjoblom]

I would personally use something like


=INDEX({6;5;4;3;2;1;7},MATCH(WEEKDAY(L43,2),{1;2;3;4;5;6;7},0))*J45


Why?

Because CHOOSE is a volatile function

 

[RagDyeR]

And why not Bob's second suggestion?<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

I would personally use something like


=INDEX({6;5;4;3;2;1;7},MATCH(WEEKDAY(L43,2),{1;2;3;4;5;6;7},0))*J45


Why?

Because CHOOSE is a volatile function

 

[Peo Sjoblom]

Didn't read that far <bg>