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

[StargateFan]

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. D

 

[Niek Otten]

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

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

 

[StargateFanFromWork]

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

 

[Ron Rosenfeld]

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. D

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

 

[Niek Otten]

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)

 

[Niek Otten]

Ron's solutions is simpler thus better.

 

[StargateFanFromWork]

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. D

 

[Ron Rosenfeld]

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