Prev: create a button to print certain tabs in excel 2003
Next: Merging different sheets
From: Steve on 11 Mar 2010 13:23
Thanks,

Works great, but I'm getting zeros now in the cells when there is nothing in
the P column cells, which is a lot. How can I eleminate the zeros if nothing
is in the P column cell ?

"ker_01" wrote:

> =if(len(P21)>3,LEFT(P21,3)&RIGHT(P21,3),P21)
>
> Assumes that cell P21 has nothing in it but either Sat, Sun, or Sat...Sun
>
> HTH,
> Keith
>
> "Steve" wrote:
>
> > I have days off in the P column in this format: Sat/Sun
> > I'm using them in another column in this format SatSun, so I'm using this
> > formula:
> > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun
> > However, I have some days off that just have Sun, which produces SunSun.
> > How can the formula be writen to produce SatSun when two days are showing,
> > and only Sun when one day is showing ?
> >
> > Thanks,
> >
> > Steve
From: Steve on 11 Mar 2010 13:23
Thanks,

Works great, but I'm getting zeros now in the cells when there is nothing in
the P column cells, which is a lot. How can I eleminate the zeros if nothing
is in the P column cell ?


"Glenn" wrote:

> Steve wrote:
> > I have days off in the P column in this format: Sat/Sun
> > I'm using them in another column in this format SatSun, so I'm using this
> > formula:
> > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun
> > However, I have some days off that just have Sun, which produces SunSun.
> > How can the formula be writen to produce SatSun when two days are showing,
> > and only Sun when one day is showing ?
> >
> > Thanks,
> >
> > Steve
>
> =IF(LEN(P21)=7,LEFT(P21,3)&RIGHT(P21,3),P21)
> .
>
From: Steve on 11 Mar 2010 13:24
I don't understand where to put this in my regular formula.
Thanks,

Steve

"Fred Smith" wrote:

> Use Substitute, as in:
> =Substitute(P21,"/","")
>
> Regards,
> Fred
>
> "Steve" <Steve(a)discussions.microsoft.com> wrote in message
> news:D1729A01-069B-4F49-BE64-678E1C6E3788(a)microsoft.com...
> >I have days off in the P column in this format: Sat/Sun
> > I'm using them in another column in this format SatSun, so I'm using this
> > formula:
> > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun
> > However, I have some days off that just have Sun, which produces SunSun.
> > How can the formula be writen to produce SatSun when two days are showing,
> > and only Sun when one day is showing ?
> >
> > Thanks,
> >
> > Steve
>
> .
>
From: Glenn on 11 Mar 2010 15:45
It doesn't go in your regular formula, it replaces it.

Steve wrote:
> I don't understand where to put this in my regular formula.
> Thanks,
>
> Steve
>
> "Fred Smith" wrote:
>
>> Use Substitute, as in:
>> =Substitute(P21,"/","")
>>
>> Regards,
>> Fred
>>
>> "Steve" <Steve(a)discussions.microsoft.com> wrote in message
>> news:D1729A01-069B-4F49-BE64-678E1C6E3788(a)microsoft.com...
>>> I have days off in the P column in this format: Sat/Sun
>>> I'm using them in another column in this format SatSun, so I'm using this
>>> formula:
>>> =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun
>>> However, I have some days off that just have Sun, which produces SunSun.
>>> How can the formula be writen to produce SatSun when two days are showing,
>>> and only Sun when one day is showing ?
>>>
>>> Thanks,
>>>
>>> Steve
>> .
>>
From: Steve on 11 Mar 2010 16:43
Duh!!

I guess it helps if I spell SUBSTITUTE correctly.

Thanks,

Steve

"Glenn" wrote:

> It doesn't go in your regular formula, it replaces it.
>
> Steve wrote:
> > I don't understand where to put this in my regular formula.
> > Thanks,
> >
> > Steve
> >
> > "Fred Smith" wrote:
> >
> >> Use Substitute, as in:
> >> =Substitute(P21,"/","")
> >>
> >> Regards,
> >> Fred
> >>
> >> "Steve" <Steve(a)discussions.microsoft.com> wrote in message
> >> news:D1729A01-069B-4F49-BE64-678E1C6E3788(a)microsoft.com...
> >>> I have days off in the P column in this format: Sat/Sun
> >>> I'm using them in another column in this format SatSun, so I'm using this
> >>> formula:
> >>> =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun
> >>> However, I have some days off that just have Sun, which produces SunSun.
> >>> How can the formula be writen to produce SatSun when two days are showing,
> >>> and only Sun when one day is showing ?
> >>>
> >>> Thanks,
> >>>
> >>> Steve
> >> .
> >>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: create a button to print certain tabs in excel 2003
Next: Merging different sheets