Re: Formula to convert yyyy.mm.dd to yyyy.mm.dd.ddd? (Perhaps a tricky one?)

From: StargateFan (IDon'tAcceptSpam_at_IDon'tAcceptSpam.com)
Date: 08/19/04 

Date: Wed, 18 Aug 2004 20:32:04 -0400

On Fri, 13 Aug 2004 10:14:22 -0400, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:

>On Fri, 13 Aug 2004 08:34:32 -0400, StargateFan
><IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:
>
>>I have various folders with dated filenames that were created with a
>>script set in old days to putting date at the required yyyy.mm.dd.
>>But we've been needing the days in the filename, too in last few
>>months.
>>
>>For example, I have the a filename with date, say, 2002.12.23 which
>>needs to read 2002.12.23.Mn. I can easily plug in 2002.12.23 into the
>>first cell but just ran into the problem that, of course, Excel deals
>>in 12/23/2002. Is there a formula that can take 2002.12.23 and
>>convert it to 2002.12.23.Mn for me?
>>
>>Thanks!
>>
>>p.s., to be very specific, Excel would return the day as Mon though we
>>actually really required the days of the week to read like this:
>>
>>Mon - Mn
>>Tues - Tu
>>Weds - Wd
>>Thurs - Th
>>Fri - Fr
>>Sat - Sa
>>Sn - Sn
>>
>>I know this might further complicates things, but I might as well as
>>the question now to know if it's possible to write a script to cover
>>this situation. I have no idea how easy/difficult this will be or if
>>it's even possible to do (????). We have done the renaming manually
>>up till now but it's been a pain as we're update the old folders and
>>now deal with new backups coming in. Would be nice to automatically
>>get this autoput to begin with.
>>
>>Thanks so much. This is an unusual one, I know, but field pressures
>>make me come up with doozies! <g>
>
>If the 2002.12.23 is a text string, then
>
>=A1&"."&CHOOSE(WEEKDAY(DATEVALUE(SUBSTITUTE(
>A1,".","/"))),"Su","Mn","Tu","Wd","Th","Fr","Sa")
>
>will convert it to 2002.12.23.Mn
>
>If it is an Excel Formatted date, then use:
>
>=TEXT(A1,"yyyy.mm.dd.")& CHOOSE(WEEKDAY(
>A1),"Su","Mn","Tu","Wd","Th","Fr","Sa")
>
>Both results will be text string as Excel formatting cannot return the two day
>weekday string you want.

Ron, you are a _genius_! <g> The first formula worked perfectly for
the text string that is entered from a copy of a the filename made
with a script before we modified the script some time back to include
the week day. Thanks for this!

I know this is an imposition, but the above this worked so well, it
would be so neat to have a *** that works for both formats we run
across. For the standard format one, dates entered as mm/dd/yyyy in
A2 returns 2004.08.18.Wed in A3 for today. How can the above
2-character date format change 2004.08.18.Wed to 2004.08.18.Wd so that
the days of the week are, again, Mn Tu Wd Th Fr Sa Sn for the days of
the week? I tried to modify the formula but just got #value in A3
:o(.

Thanks so much! Getting a lot accomplished here thanks to this ng
<g>.