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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages

  • Re: How to save several variables with the same prefix?
    ... well the problem is that if I write fprintf(' filename', X) in each iteration it will create only one file named filename, it will overwrite it each time and I end up with only one file that has the value of X but just for s=11. ... is create the string this is why I put it inicialy without ' '. ... Thanks for answer me why fwrite did not work, that was somenthing I did not know but after fprintf did not work I begun to explore other ideas, I need it with format so fwrite is not an option. ... name that references the size of the window (filter) I am using in my ...
    (comp.soft-sys.matlab)
  • Re: Change in font size inside field in document
    ... At least in the FILENAME field, and better in all three fields, replace the ... Dim strCustomerName As String ... How does the macro format the variable ... The problem I experience is that after the underscore the font ...
    (microsoft.public.word.vba.userforms)
  • Re: Change in font size inside field in document
    ... At least in the FILENAME field, and better in all three fields, replace the ... Dim strCustomerName As String ... How does the macro format the variable ... The problem I experience is that after the underscore the font ...
    (microsoft.public.word.vba.userforms)
  • Re: Python, MS SQL, and batch inserts
    ... machine running the script. ...  I don't know of such a function in python s I tried grouping ... my inserts into a single string using string += syntax. ... when I format it as single string. ...
    (comp.lang.python)
  • Re: unit testing C++ code from perl
    ... void ReportAssert(char const* description, char const* filename, int const ... That's probably the complaint string for an error. ... Then we have a filename and lineNumber. ... void FormatToStream(MemoryOutStream& stream, char const* format, ValueType const& value) ...
    (comp.programming)