Custom date format shows "051117.Thu" for today. Way to have "051117.Th", instead?

With these standard abbreviations holding true for the entire week:

Mon = s/b Mn
Tue = s/b Tu
Wed = s/b Wd
Thu = s/b Th
Fri = s/b Fr
Sat = s/b Sa
Sun = s/b Sn

?

Hope it's possible. I have code that does something similar elsewhere
in XL2K, but nothing I've tried in converting it to this particular
spreadsheet's use, works. Thanks so much. :oD

I don't think you can do it with a format. You can use a formula:

=TEXT(A1,"yymmdd.")&LEFT(TEXT(A1,"ddd"),2)

--
Kind regards,

Niek Otten

"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> With these standard abbreviations holding true for the entire week:
>
> Mon = s/b Mn
> Tue = s/b Tu
> Wed = s/b Wd
> Thu = s/b Th
> Fri = s/b Fr
> Sat = s/b Sa
> Sun = s/b Sn
>
> ?
>
> Hope it's possible. I have code that does something similar elsewhere
> in XL2K, but nothing I've tried in converting it to this particular
> spreadsheet's use, works. Thanks so much. :oD
>

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> I don't think you can do it with a format. You can use a formula:
>
> =TEXT(A1,"yymmdd.")&LEFT(TEXT(A1,"ddd"),2)

That is so cool, it doesn't automatically use my format though (i.e., Mn for
Mon, Sn for Sun, Wd for Weds., etc.) but just seems to take off the last
letter. I didn't want to confuse the issue in my initial message so didn't
copy the code I was trying to emulate in this new spreadsheet. Perhaps I
should have. But I can't find a copy of that particular spreadsheet here at
the office and only have a copy at home. But hoping you know what I mean re
the the day style from the list of day formats below.

Also, the difficulty is that you're right, it was in the formula that this
is affected. That was why I couldn't figure out how to modify that existing
code to what I need in this new situation. No wonder I was having such
trouble. The formulas involved are so completely different! <g>

In this new spreadsheet, each cell already has a formula which all look
pretty much like this:

=IF(B2<>"",B2+1,"")

It's quite a large spreadsheet actually, and all the affected fields are
populated based on the user input in the 2 first cells. So the requirement
is to have in adjacent cells the format of yyyymmdd.ddd, where ddd returns
the 2-character format as listed below. Sorry it's so specific, but that's
what they use here manually so would like to not confuse the users in their
electronic versions.

Thanks and appreciate any further help in fixing this. You're all so very
kind to help us "darned newbies"! <g>

> --
> Kind regards,
>
> Niek Otten
>
> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> news:[email protected]...
> > With these standard abbreviations holding true for the entire week:
> >
> > Mon = s/b Mn
> > Tue = s/b Tu
> > Wed = s/b Wd
> > Thu = s/b Th
> > Fri = s/b Fr
> > Sat = s/b Sa
> > Sun = s/b Sn
> >
> > ?
> >
> > Hope it's possible. I have code that does something similar elsewhere
> > in XL2K, but nothing I've tried in converting it to this particular
> > spreadsheet's use, works. Thanks so much. :oD
> >
>
>

On Thu, 17 Nov 2005 08:37:56 -0500, StargateFan
<IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

>With these standard abbreviations holding true for the entire week:
>
>Mon = s/b Mn
>Tue = s/b Tu
>Wed = s/b Wd
>Thu = s/b Th
>Fri = s/b Fr
>Sat = s/b Sa
>Sun = s/b Sn
>
>?
>
>Hope it's possible. I have code that does something similar elsewhere
>in XL2K, but nothing I've tried in converting it to this particular
>spreadsheet's use, works. Thanks so much. :oD

If you want to use the cell contents in other calculations, it will not be
possible without changing the subsequent formulas.

If you are only concerned that the results be displayed the way you show, then
you can convert the date to a text string. With the date in A1:

=TEXT(A1,"yymmdd.")& CHOOSE(WEEKDAY(A1),"Sn","Mn","Tu","Wd","Th","Fr","Sa")


--ron

That can easily be achieved with a VLOOKUP() function;
=TEXT(A1,"yymmdd.")&VLOOKUP(TEXT(A1,"ddd"),{"Mon","Mn";"Tue","Tu";"Wed","Wd";"Thu","Th";"Fri","Fr";"Sat","Sa";"Sun","Sn"},2,FALSE)

--
Kind regards,

Niek Otten

"StargateFanFromWork" <[email protected]> wrote in message
news:[email protected]...
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
>> I don't think you can do it with a format. You can use a formula:
>>
>> =TEXT(A1,"yymmdd.")&LEFT(TEXT(A1,"ddd"),2)
>
> That is so cool, it doesn't automatically use my format though (i.e., Mn
> for
> Mon, Sn for Sun, Wd for Weds., etc.) but just seems to take off the last
> letter. I didn't want to confuse the issue in my initial message so
> didn't
> copy the code I was trying to emulate in this new spreadsheet. Perhaps I
> should have. But I can't find a copy of that particular spreadsheet here
> at
> the office and only have a copy at home. But hoping you know what I mean
> re
> the the day style from the list of day formats below.
>
> Also, the difficulty is that you're right, it was in the formula that this
> is affected. That was why I couldn't figure out how to modify that
> existing
> code to what I need in this new situation. No wonder I was having such
> trouble. The formulas involved are so completely different! <g>
>
> In this new spreadsheet, each cell already has a formula which all look
> pretty much like this:
>
> =IF(B2<>"",B2+1,"")
>
> It's quite a large spreadsheet actually, and all the affected fields are
> populated based on the user input in the 2 first cells. So the
> requirement
> is to have in adjacent cells the format of yyyymmdd.ddd, where ddd returns
> the 2-character format as listed below. Sorry it's so specific, but
> that's
> what they use here manually so would like to not confuse the users in
> their
> electronic versions.
>
> Thanks and appreciate any further help in fixing this. You're all so very
> kind to help us "darned newbies"! <g>
>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>> news:[email protected]...
>> > With these standard abbreviations holding true for the entire week:
>> >
>> > Mon = s/b Mn
>> > Tue = s/b Tu
>> > Wed = s/b Wd
>> > Thu = s/b Th
>> > Fri = s/b Fr
>> > Sat = s/b Sa
>> > Sun = s/b Sn
>> >
>> > ?
>> >
>> > Hope it's possible. I have code that does something similar elsewhere
>> > in XL2K, but nothing I've tried in converting it to this particular
>> > spreadsheet's use, works. Thanks so much. :oD
>> >
>>
>>
>
>

Ron's solutions is simpler thus better.

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> That can easily be achieved with a VLOOKUP() function;
> =TEXT(A1,"yymmdd.")&VLOOKUP(TEXT(A1,"ddd"),{"Mon","Mn";"Tue","Tu";"Wed","Wd";"Thu","Th";"Fri","Fr";"Sat","Sa";"Sun","Sn"},2,FALSE)
>
> --
> Kind regards,
>
> Niek Otten
>
> "StargateFanFromWork" <[email protected]> wrote in message
> news:[email protected]...
>> "Niek Otten" <[email protected]> wrote in message
>> news:[email protected]...
>>> I don't think you can do it with a format. You can use a formula:
>>>
>>> =TEXT(A1,"yymmdd.")&LEFT(TEXT(A1,"ddd"),2)
>>
>> That is so cool, it doesn't automatically use my format though (i.e., Mn
>> for
>> Mon, Sn for Sun, Wd for Weds., etc.) but just seems to take off the last
>> letter. I didn't want to confuse the issue in my initial message so
>> didn't
>> copy the code I was trying to emulate in this new spreadsheet. Perhaps I
>> should have. But I can't find a copy of that particular spreadsheet here
>> at
>> the office and only have a copy at home. But hoping you know what I mean
>> re
>> the the day style from the list of day formats below.
>>
>> Also, the difficulty is that you're right, it was in the formula that
>> this
>> is affected. That was why I couldn't figure out how to modify that
>> existing
>> code to what I need in this new situation. No wonder I was having such
>> trouble. The formulas involved are so completely different! <g>
>>
>> In this new spreadsheet, each cell already has a formula which all look
>> pretty much like this:
>>
>> =IF(B2<>"",B2+1,"")
>>
>> It's quite a large spreadsheet actually, and all the affected fields are
>> populated based on the user input in the 2 first cells. So the
>> requirement
>> is to have in adjacent cells the format of yyyymmdd.ddd, where ddd
>> returns
>> the 2-character format as listed below. Sorry it's so specific, but
>> that's
>> what they use here manually so would like to not confuse the users in
>> their
>> electronic versions.
>>
>> Thanks and appreciate any further help in fixing this. You're all so
>> very
>> kind to help us "darned newbies"! <g>
>>
>>> --
>>> Kind regards,
>>>
>>> Niek Otten
>>>
>>> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>>> news:[email protected]...
>>> > With these standard abbreviations holding true for the entire week:
>>> >
>>> > Mon = s/b Mn
>>> > Tue = s/b Tu
>>> > Wed = s/b Wd
>>> > Thu = s/b Th
>>> > Fri = s/b Fr
>>> > Sat = s/b Sa
>>> > Sun = s/b Sn
>>> >
>>> > ?
>>> >
>>> > Hope it's possible. I have code that does something similar elsewhere
>>> > in XL2K, but nothing I've tried in converting it to this particular
>>> > spreadsheet's use, works. Thanks so much. :oD
>>> >
>>>
>>>
>>
>>
>
>

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Thu, 17 Nov 2005 08:37:56 -0500, StargateFan
> <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:
>
> >With these standard abbreviations holding true for the entire week:
> >
> >Mon = s/b Mn
> >Tue = s/b Tu
> >Wed = s/b Wd
> >Thu = s/b Th
> >Fri = s/b Fr
> >Sat = s/b Sa
> >Sun = s/b Sn
> >
> >?
> >
> >Hope it's possible. I have code that does something similar elsewhere
> >in XL2K, but nothing I've tried in converting it to this particular
> >spreadsheet's use, works. Thanks so much. :oD
>
> If you want to use the cell contents in other calculations, it will not be
> possible without changing the subsequent formulas.

Yes, that makes sense and is why I later wrote what the code was. Again,
wasn't conscious that the display was governed by the formula. I mean, I
was seeing it, but not realizing that as it had been such a long time since
this concept was presented to me and knew less about XL2K than I do now
(yes, I _do_ know a little bit more than then <g>).

That's what I'm having trouble doing, though. I don't know why VB is so
difficult sometimes for me to try to work with, but it is.

As I originally stated, I need the exact 2-character display above for the
days, and the formula involved to achieve the dates in all the cells looks
like this:

=IF(B2<>"",B2+1,"")

How can I show the format in a customized yyyymmdd.ddd in that formula,
where ddd returns the above 2-character date format?




That's what I've been needing all along; it seems I just didn't explain it
properly.




Thanks so much. :oD

> If you are only concerned that the results be displayed the way you show,
then
> you can convert the date to a text string. With the date in A1:
>
> =TEXT(A1,"yymmdd.")&
CHOOSE(WEEKDAY(A1),"Sn","Mn","Tu","Wd","Th","Fr","Sa")
>
>
> --ron

On Thu, 17 Nov 2005 14:56:40 -0500, "StargateFanFromWork"
<[email protected]> wrote:

>As I originally stated, I need the exact 2-character display above for the
>days, and the formula involved to achieve the dates in all the cells looks
>like this:
>
>=IF(B2<>"",B2+1,"")
>
>How can I show the format in a customized yyyymmdd.ddd in that formula,
>where ddd returns the above 2-character date format?

Well, you've got to extract the part of the string that represents the date,
add one to it, and then convert it back into the format you're interested in.

Here is a worksheet formula equivalent to your IF function above with the
conversions embedded in it:

You could enter this in B3 and copy/drag it down.

=IF(B2="","",TEXT(DATE(1900+LEFT(B2,2)+100*(--LEFT(B2,2)<30),
MID(B2,3,2),MID(B2,5,2)+1),"yymmdd.")&CHOOSE(WEEKDAY(DATE(
1900+LEFT(B2,2)+100*(--LEFT(B2,2)<30),MID(B2,3,2),MID(B2,5,2)+1)),
"Sn","Mn","Tu","Wd","Th","Fr","Sa"))

Obviously, a lot more complicated.


--ron