Thanks, Frederick! That works!
Thanks again, Jonas. You were saying the same thing, but Excel Help's talk of "serial number" threw me off on a tangent. Gratefully yours, Dwayne ---- Original message ---- >Date: Thu, 7 May 2009 12:56:46 -0500 >From: "Grooms, Frederick W" <frederick.w.gro...@xo.com> >Subject: Re: SV: Preparing import file with date fields in Excel >To: arslist@ARSLIST.ORG > >The TEXT function will format an Excel date into whatever format you specify. >For normal US format it would be > . . . &CHAR(34)&TEXT(O2,"MM/DD/YYYY HH:MM:SS")&CHAR(34)&. . . >Or > . . . &CHAR(34)&TEXT(O2,"MM/DD/YYYY h:mm:ss AM/PM")&CHAR(34)&. . . > >The 1st should be 24 hour format while the second has the AM or PM indicator > >Fred > >-----Original Message----- >From: Action Request System discussion list(ARSList) >[mailto:arsl...@arslist.org] On Behalf Of Dwayne Martin >Sent: Thursday, May 07, 2009 1:00 PM >To: arslist@ARSLIST.ORG >Subject: Re: SV: Preparing import file with date fields in Excel > >Thanks, Jonas. > >The problem is, I don't know what the day, month, and year are. It appears in >the cell as "5/6/2009 4:34:00 PM." > >I would have to do some very fancy parcing to come up with the values, and >even then it wouldn't include the time, which in the case of "Creation Date" >or "Date Modified" is important. I tried feeding it the whole value as one >chunk, but it wouldn't take it. > >According to my Excel help, DATE() returns the serial number of a date. What >I need is something that will take the serial number and return a date as a >string, but I can't seem to find that. > >An idea I'm working on: Export the dates separately to an ".arx" (import) >file. Then import them into the Excel spreadsheet, where they will appear as >numbers, which can be imported. As long as the data doesn't change between >exports it should work, but one little glitch and everything is all out of >sync. > >Thanks again, >Dwayne > >---- Original message ---- >>Date: Thu, 7 May 2009 17:17:01 +0200 >>From: Jonas Stumph Stevnsvig <jonas.stumph.stevns...@bec.dk> >>Subject: SV: Preparing import file with date fields in Excel >>To: arslist@ARSLIST.ORG >> >>Hi dwayne - I am using a localized version of excel, but I've tried just >>that. My solution was: >> >>TEKST(O2;"dd-mm-åå") >> >>So for an english excel my guess is >> >>TEXT(O2;"dd-mm-yy") >> >> >>Observe that this is not applicable for VBA - that is an entierly different >>approach. >> >> >> >>with regards, >> >> >> >>Jonas Stumph Stevnsvig >> >> >>Remedy Developer >> >>BEC Intern IT >>jonas.stumph.stevns...@bec.dk >> >>-----Oprindelig meddelelse----- >>Fra: Action Request System discussion list(ARSList) >>[mailto:arsl...@arslist.org] På vegne af Dwayne Martin >>Sendt: 7. maj 2009 17:09 >>Til: arslist@ARSLIST.ORG >>Emne: Preparing import file with date fields in Excel >> >>Dear List, >> >>This is really an Excel question, but I can't find the answer so I'll try you >>all. >> >>I am trying to create a template for exporting data from one form and >>importing it into a very different form. So I've exported the data from the >>first form into a comma-separated-file (csv), and opened the file in Excel. >>In the cell after all the data columns I am trying to build the import file >>data row. The date fields appear as dates in the main spreadsheet. But when >>I try to insert them into a formula they appear as long decimal numbers. >> >>Eg in the O2 cell I see "5/6/2009 4:34:00 PM", but when I insert "O2" into a >>formula as: >> >>. . . &CHAR(34)&O2&CHAR(34)&. . . >> >>the O2 value appears as "39939.6902777778". >> >>I've tried the DATE and DATEVALUE. The formula that Excel uses to create >>these date numbers is way too complicated to convert into a Remedy date >>integer. >> >>How can I get the date to appear as a date in my formulated field? >> >>Dwayne Martin >>James Madison University >> >>_______________________________________________________________________________ >>UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >>Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > >_______________________________________________________________________________ >UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > >_______________________________________________________________________________ >UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
