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"
