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

[StargateFan]

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>

 

[Frank Kabel]

Hi
why not enter the date as real date in the cell and use the custom
format
YYYY.MM.DD.DDD

 

[StargateFan]

Hi
why not enter the date as real date in the cell and use the custom
format
YYYY.MM.DD.DDD

I'm sorry, I thought I made that clear. Because the dates are already
in the yyyy.mm.dd format. Already we have to manually go in an add
the day in Mn, Tu, Wd, Th, Fr, Sa, Sn format to some of the old ones.
For all the backups we do today, the script takes care of naming them
in the correct manner automatically, but for the old ones. They might
be few, but as I bring up old files occasionally from CD backups, it
would be nice to have the formula do this for us. Excel doesn't do
anything with dates that are pasted in in their native format of
yyyy.mm.dd format.

Why yyyy.mm.dd.ddd?? Because things get sorted properly in
chronological order. No other format does that.

Thanks.

 

[Frank Kabel]

Hi
the question is just HOW you have inserted the values with your
macros/manually. It seems you have stored them as 'Text'. This is IMHO
not a good idea. Nearly always better to insert the dates as REAL date
values and let formating do the rest. This way also Excel will sort
automatically in the correct order. So you may really consider changing
the script / changing the existing values to real date values

 

[Ron Rosenfeld]

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

 

[StargateFan]

Hi
the question is just HOW you have inserted the values with your
macros/manually. It seems you have stored them as 'Text'. This is IMHO
not a good idea. Nearly always better to insert the dates as REAL date
values and let formating do the rest. This way also Excel will sort
automatically in the correct order. So you may really consider changing
the script / changing the existing values to real date values

Don't need it for sorting, though. The _whole_ idea is for Excel to
convert dates in text form for me from one date format to another.
That is the whole purpose of this worksheet. Can it be done with the
limitations I've described? If it can't, then this is no good, that's
all.

Thanks.

 

[StargateFan]

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

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

 

[Ron Rosenfeld]

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

From what you write, I'm not certain what the different formats/values are that
the formula has to test for. If the value is an Excel date, then the "date"
based formula I gave will work.

If the value is a text string in the form of 2004.08.18.Wed, then a slight
modification of the "text" based formula should do it:

=LEFT(A2,10)&"."&CHOOSE(WEEKDAY(DATEVALUE(LEFT(SUBSTITUTE(
A2,".","/"),10))),"Su","Mn","Tu","Wd","Th","Fr","Sa")

and if it could be either, then you can test for Number vs String by using an
"IF" function to determine which formula to use:

=IF(ISNUMBER(A2),TEXT(A2,"yyyy.mm.dd.")& CHOOSE(WEEKDAY(
A2),"Su","Mn","Tu","Wd","Th","Fr","Sa"),LEFT(A2,10)&"."&
CHOOSE(WEEKDAY(DATEVALUE(LEFT(SUBSTITUTE(
A2,".","/"),10))),"Su","Mn","Tu","Wd","Th","Fr","Sa"))


If there are more possibilities, it will be less messy and easier to understand
to do the work in a User Defined Function (UDF) in VBA.


--ron

 

[David McRitchie]

Hi Stargate,
I think you have some inconsistencies in you concepts of
dates, and would be a lot better of if you actually converted
to and used dates rather than text. You would be able to
sort dates, and format them for display almost anyway
you want them except for that two letter day of week thing.

Would suggest you look over the following two web pages.
http://www.cpearson.com/excel/datetime.htm shorter
http://www.mvps.org/dmcritchie/excel/datetime.htm

The ISO date format [ISO 8601], format, yyyy-mm-dd, by
the way uses hyphens as preferred format (never periods,
or slashes), specifically to stay clear of dd.mm.yy and mm/dd/yy
and still use a separator not unfamiliar to users of the
other date formats.
A Summary of the International Standard Date and Time Notation
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

 

[StargateFan]

Hi Stargate,
I think you have some inconsistencies in you concepts of
dates, and would be a lot better of if you actually converted
to and used dates rather than text. You would be able to
sort dates, and format them for display almost anyway
you want them except for that two letter day of week thing.

Would suggest you look over the following two web pages.
http://www.cpearson.com/excel/datetime.htm shorter
http://www.mvps.org/dmcritchie/excel/datetime.htm

The ISO date format [ISO 8601], format, yyyy-mm-dd, by
the way uses hyphens as preferred format (never periods,
or slashes), specifically to stay clear of dd.mm.yy and mm/dd/yy
and still use a separator not unfamiliar to users of the
other date formats.
A Summary of the International Standard Date and Time Notation
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Looking into the above pages now, but more to learn than anything
else.

The macros all work perfectly fine. I've been using them to rename
files. The good thing is that I also set up an AutoIt script to help
me name the files correctly in the first place, so this Excel file
will be used for all those old ones that are not in the correct
format.

So, as I said, the formulas working perfectly, so I won't tamper with
them.

p.s., the Excel file gives me text values that I then use to name
files, so the dates are irrelevant to Excel, ultimately. Filenames
handle periods and dashes just fine.

Thanks.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

From what you write, I'm not certain what the different formats/values are that
the formula has to test for. If the value is an Excel date, then the "date"
based formula I gave will work.

If the value is a text string in the form of 2004.08.18.Wed, then a slight
modification of the "text" based formula should do it:

=LEFT(A2,10)&"."&CHOOSE(WEEKDAY(DATEVALUE(LEFT(SUBSTITUTE(
A2,".","/"),10))),"Su","Mn","Tu","Wd","Th","Fr","Sa")

and if it could be either, then you can test for Number vs String by using an
"IF" function to determine which formula to use:

=IF(ISNUMBER(A2),TEXT(A2,"yyyy.mm.dd.")& CHOOSE(WEEKDAY(
A2),"Su","Mn","Tu","Wd","Th","Fr","Sa"),LEFT(A2,10)&"."&
CHOOSE(WEEKDAY(DATEVALUE(LEFT(SUBSTITUTE(
A2,".","/"),10))),"Su","Mn","Tu","Wd","Th","Fr","Sa"))


If there are more possibilities, it will be less messy and easier to understand
to do the work in a User Defined Function (UDF) in VBA.


--ron