[mso] Re: Excel Question

Gail Kolanowski wrote:
> How do I take this date
>
> 4011943
>
> and convert it to
>
> 04/01/1943 in an Excel worksheet?
>
> Thank you.
>

You don't say whether this is a text string or a number (value).  I am
assuming that, because it lacks a leading zero, it is a text string which
has become a value.  This means that some of your dates will be 7 characters
long and others will be 8 in the format [m]mddyyyy.  Fortunately, Excel is
able to use text parsing on this kind of value (or it did when I tried it!).

If your date string/value is in A1, use this formula in B1 to give a column
of dates:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

To explain this:
=DATE(YEAR,MONTH,DAY)is the formula used, YEAR is easy because it's always
the RIGHTmost 4 characters; MONTH and DAY, being counted from the left,
depend on the LENgth of the string, so the MONTH will either be the LEFTmost
one or two characters (found by subtracting 6 from the LENgth of the string)
and DAY will be the MIDdle 2 characters starting with the second or third
character, depending on the LENgth.

The above assumes that you are using the American format for your dates -
[m]mddyyyy.  If they are [d]dmmyyyy, then you should modify the above
formula to read:

=DATE(RIGHT(A1,4),MID(A1,LEN(A1)-5,2),LEFT(A1,LEN(A1)-6))

Hope this helps!

Andrew Kendon

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: