[mso] Re: Date Format Conversion
- From: "Carneal, Robert" <carnealre@xxxxxxxxxxxx>
- To: Ray Blake <mso@xxxxxxxxxxxxx>
- Date: Mon, 23 Aug 2004 15:32:04 -0500
---Emphasis---
But I'm curious as to why a simple cell= format won't do the trick for you
(Format-Cells-Date and time and enter= "yyyy-MM".)
---End---
Very fair question. Excel does not "like"= the majority of the dates. Some
date examples are prior to 1900, they read= as
07101829
07041774
and so on.
These are dates gleaned from cemetery= headstones. If there is a date such
as"1885-10-12" Excel cringes. By= making it a text, I can get the year off
and if it is valid, then add what= I call a 'validifier' to it. Using the
date of 1885-10-12, I can add 2000= years to make it 3885-10-12 and Excel is
happy with that. Of course, before= I let a reader see that, I have to
convert it back to the correct date!= (Yeah, it is confusing sometimes,
calendar changes, etc. I have a Bible= here that reads like this:
James died today. Today is Sunday. Two= Sundays ago was Jan 30, so today is
Jan 44. (Whew) I changed it to Feb 13= for calculation reasons.
Robert
On Mon, 23 Aug 2004 20:28:20 +0100, Ray= Blake wrote:
>=A0There are several ways of doing= this. The first is with a
lookup>=A0table, and you can= find an article on my site here to get you
>=A0started with this:
>
>=A0http://www.grbps.com/raq1.htm[0]
>
>=A0Or, you could use the CHOOSE= worksheet command - more details here:
>
>=A0http://www.grbps.com/ExcelA.pdf[0]
>
>=A0(On the third page of that article= is the exact construction
you>=A0need.)
>
>=A0But I'm curious as to why a simple= cell format won't do the
trick>=A0foryou= (Format-Cells-Date and time and enter "yyyy-MM".)
>
>=A0Ray
>
>=A0-----------------------------------= -
>=A0GR Business Process= Solutions
>=A0Ray Blake
>=A0Head of Software Design
>=A0ray@xxxxxxxxx
>=A0Braedon
>=A0Newell Road
>=A0Hemel Hempstead
>=A0Herts HP3 9PD
>=A0tel: 01442 396518
>=A0fax: 01442 389353
>=A0www.grbps.com
>=A0-----------------------------------= -
>
>
>=A0-----Original= Message-----
>=A0From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
>=A0Behalf Of MCQUEEN, RICHARD H (SWBT)= Sent: 23 August 2004 18:36
To:>=A0'mso@xxxxxxxxxxxxx'= Subject: [mso] Date Format Conversion
>
>
>=A0I have an outside Excel report that= populates dates in the>=A0following
mmddyyyy= format: 04172004. I need a method of converting
>=A0this date into the format yyyy-mm.= I know that I can use
the>=A0following formula:= =3DTEXT(B11,"yyyy")&IF(MONTH(B11)=3D1,"-
>=A001",IF(MONTH(B11)=3D2,"-02",IF(MONTH(= B 11)=3D
>=A03,"-03",IF(MONTH(B11)=3D4,"-04"))))= where B11 is the location of
the>=A0original date, but I= run out of arguments after the fourth IF. What
>=A0method would allow all 12 months to= be returned? I cannot change>=A0the
original report,= and I need the new format for pivot table
>=A0options. Thank you for your= help.
>
>=A0Rick McQueen
>=A0rx8273@xxxxxxx <">mailto:rx8273@xxxxxxx></a>;
>
>
>=A0***********************************= **************************
You>=A0are receiving this= mail because you subscribed to mso@xxxxxxxxxxxxx
>=A0or= MicrosoftOffice@xxxxxxxxxxxxxxxx>
>=A0To send mail to the group, simply= address it to mso@xxxxxxxxxxxxx>
>=A0To Unsubscribe from this group,= send an email to
>=A0mso-request@xxxxxxxxxxxxx with the= word "unsubscribe" (without
the>=A0quotes) in the= subject line.
>
>=A0Or, visit the group's homepage and= use the dropdown menu.
=A0This>=A0will also allow you= to change your email settings to digest or
>=A0vacation (no mail).
>=A0//www.freelists.org/webpag= e/mso[0]
>
>=A0To be able to use the files section= for sharing files with
the>=A0group,send a= request to mso-moderators@xxxxxxxxxxxxx and you will
>=A0be sent an invitation with= instructions. =A0Once you are a member of
>=A0the files group, you can go here to= upload/download
files:>=A0http://www.smartgroups.com= /vault/msofiles[0]
>=A0***********************************= **************************
*************************************************************
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).
//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:
