Day format in formula

john.bedford3 · 04-23-2005, 08:09 AM

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
--
John Bedford
Delete extra @ to reply.

Bob Phillips · 04-23-2005, 09:07 AM

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" <john.bedford3@@ntlworld.com> wrote in message
news:4Mqae.9406$u5.1218@newsfe2-gui.ntli.net...
> 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
> --
> John Bedford
> Delete extra @ to reply.
>
>

john.bedford3 · 04-23-2005, 09:07 AM

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

Most appreciated.
--
John Bedford
Delete extra @ to reply.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23ESyi3$RFHA.2964@TK2MSFTNGP15.phx.gbl...
> 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" <john.bedford3@@ntlworld.com> wrote in message
> news:4Mqae.9406$u5.1218@newsfe2-gui.ntli.net...
> > 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
> > --
> > John Bedford
> > Delete extra @ to reply.
> >
> >
>
>

Bob Phillips · 04-23-2005, 10:06 AM

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" <john.bedford3@@ntlworld.com> wrote in message
news:X8rae.9418$u5.1044@newsfe2-gui.ntli.net...
> Thanks Bob it is obvious now you have explained it. I do not think I would
> have got there without help.
>
> Most appreciated.
> --
> John Bedford
> Delete extra @ to reply.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23ESyi3$RFHA.2964@TK2MSFTNGP15.phx.gbl...
> > 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 · 04-23-2005, 11:06 AM

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

--
John Bedford
Delete extra @ to reply.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:O6mYvhASFHA.1172@TK2MSFTNGP12.phx.gbl...
> 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" <john.bedford3@@ntlworld.com> wrote in message
> news:X8rae.9418$u5.1044@newsfe2-gui.ntli.net...
> > Thanks Bob it is obvious now you have explained it. I do not think I
would
> > have got there without help.
> >
> > Most appreciated.
> > --
> > John Bedford
> > Delete extra @ to reply.
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%23ESyi3$RFHA.2964@TK2MSFTNGP15.phx.gbl...
> > > 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)
>
>

Peo Sjoblom · 04-23-2005, 12:06 PM

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

--
Regards,

Peo Sjoblom

"john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
news:UOsae.5089$YF5.4762@newsfe5-win.ntli.net...
> Yes Bob, that is much simpler. Thanks very much.
>
> --
> John Bedford
> Delete extra @ to reply.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O6mYvhASFHA.1172@TK2MSFTNGP12.phx.gbl...
>> 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" <john.bedford3@@ntlworld.com> wrote in message
>> news:X8rae.9418$u5.1044@newsfe2-gui.ntli.net...
>> > Thanks Bob it is obvious now you have explained it. I do not think I
> would
>> > have got there without help.
>> >
>> > Most appreciated.
>> > --
>> > John Bedford
>> > Delete extra @ to reply.
>> >
>> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> > news:%23ESyi3$RFHA.2964@TK2MSFTNGP15.phx.gbl...
>> > > 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)
>>
>>
>
>

RagDyeR · 04-23-2005, 12:06 PM

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

Regards,

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

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:udqycsBSFHA.1396@TK2MSFTNGP10.phx.gbl...
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

--
Regards,

Peo Sjoblom

"john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
news:UOsae.5089$YF5.4762@newsfe5-win.ntli.net...
> Yes Bob, that is much simpler. Thanks very much.
>
> --
> John Bedford
> Delete extra @ to reply.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O6mYvhASFHA.1172@TK2MSFTNGP12.phx.gbl...
>> 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" <john.bedford3@@ntlworld.com> wrote in message
>> news:X8rae.9418$u5.1044@newsfe2-gui.ntli.net...
>> > Thanks Bob it is obvious now you have explained it. I do not think I
> would
>> > have got there without help.
>> >
>> > Most appreciated.
>> > --
>> > John Bedford
>> > Delete extra @ to reply.
>> >
>> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> > news:%23ESyi3$RFHA.2964@TK2MSFTNGP15.phx.gbl...
>> > > 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)
>>
>>
>
>

Peo Sjoblom · 04-23-2005, 02:07 PM

Didn't read that far <bg>

--
Regards,

Peo Sjoblom

"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:e%23Zo$1BSFHA.3544@TK2MSFTNGP12.phx.gbl...
> And why not Bob's second suggestion?<g>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:udqycsBSFHA.1396@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> news:UOsae.5089$YF5.4762@newsfe5-win.ntli.net...
>> Yes Bob, that is much simpler. Thanks very much.
>>
>> --
>> John Bedford
>> Delete extra @ to reply.
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:O6mYvhASFHA.1172@TK2MSFTNGP12.phx.gbl...
>>> 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" <john.bedford3@@ntlworld.com> wrote in message
>>> news:X8rae.9418$u5.1044@newsfe2-gui.ntli.net...
>>> > Thanks Bob it is obvious now you have explained it. I do not think I
>> would
>>> > have got there without help.
>>> >
>>> > Most appreciated.
>>> > --
>>> > John Bedford
>>> > Delete extra @ to reply.
>>> >
>>> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>>> > news:%23ESyi3$RFHA.2964@TK2MSFTNGP15.phx.gbl...
>>> > > 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)
>>>
>>>
>>
>>
>
>
>