Convert Date-as-Text

Using Excel 2000

I have an Excel worksheet (.xls) that was produced by an accountant
exporting data from a particular accounting package.  There is a colum
of dates of the m/d/yyyy format that don't sort as dates.  They sort
alphabetically, which is of no use.

I'm looking for the VBA function that will convert text in the
m/d/yyyy format to an Excel date number.

   Cells(n, m).formula = DateConverter(Cells(n, m).value)

   or some such

And I will then write a loop macro to process each cell in the
column/range to convert the cell contents to an Excel date of the
usual kind that will sort properly.

A search in the Excel VBA help on "date" doesn't yield anything
interesting, and I can't think of a good search keyword for this.

I have no idea how this circumstance occurred, but I don't want to
bother the accountant about it.  I have downloaded a lot of banking
and credit card stuff in .csv file format, opened it in Excel, and
saved it as an .xls and have always (usually?) gotten proper Excel
dates to appear in the cells in the date column.

Many thanks,

Fred Holms
0
Fred
3/13/2010 9:22:34 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
753 Views

Similar Articles

[PageSpeed] 30

Does the normal "Format Cells..." not work?



"Fred Holmes" <fsh@his.com> wrote in message 
news:0lvnp512c0f07p58oq295b05oeo4cgirqp@4ax.com...
> Using Excel 2000
>
> I have an Excel worksheet (.xls) that was produced by an accountant
> exporting data from a particular accounting package.  There is a colum
> of dates of the m/d/yyyy format that don't sort as dates.  They sort
> alphabetically, which is of no use.
>
> I'm looking for the VBA function that will convert text in the
> m/d/yyyy format to an Excel date number.
>
>   Cells(n, m).formula = DateConverter(Cells(n, m).value)
>
>   or some such
>
> And I will then write a loop macro to process each cell in the
> column/range to convert the cell contents to an Excel date of the
> usual kind that will sort properly.
>
> A search in the Excel VBA help on "date" doesn't yield anything
> interesting, and I can't think of a good search keyword for this.
>
> I have no idea how this circumstance occurred, but I don't want to
> bother the accountant about it.  I have downloaded a lot of banking
> and credit card stuff in .csv file format, opened it in Excel, and
> saved it as an .xls and have always (usually?) gotten proper Excel
> dates to appear in the cells in the date column.
>
> Many thanks,
>
> Fred Holms 

0
Dennis
3/13/2010 10:10:38 PM
Formatting the cells as date, e.g., mm/dd/yyyy doesn't make the cells
sort properly and doesn't change the way the dates are displayed.
They appear to be fixed text.

Fred Holmes

On Sat, 13 Mar 2010 15:10:38 -0700, "Dennis Tucker"
<dennis13030@cox.net> wrote:

>Does the normal "Format Cells..." not work?
>
>
>
>"Fred Holmes" <fsh@his.com> wrote in message 
>news:0lvnp512c0f07p58oq295b05oeo4cgirqp@4ax.com...
>> Using Excel 2000
>>
>> I have an Excel worksheet (.xls) that was produced by an accountant
>> exporting data from a particular accounting package.  There is a colum
>> of dates of the m/d/yyyy format that don't sort as dates.  They sort
>> alphabetically, which is of no use.
>>
>> I'm looking for the VBA function that will convert text in the
>> m/d/yyyy format to an Excel date number.
>>
>>   Cells(n, m).formula = DateConverter(Cells(n, m).value)
>>
>>   or some such
>>
>> And I will then write a loop macro to process each cell in the
>> column/range to convert the cell contents to an Excel date of the
>> usual kind that will sort properly.
>>
>> A search in the Excel VBA help on "date" doesn't yield anything
>> interesting, and I can't think of a good search keyword for this.
>>
>> I have no idea how this circumstance occurred, but I don't want to
>> bother the accountant about it.  I have downloaded a lot of banking
>> and credit card stuff in .csv file format, opened it in Excel, and
>> saved it as an .xls and have always (usually?) gotten proper Excel
>> dates to appear in the cells in the date column.
>>
>> Many thanks,
>>
>> Fred Holms 

0
Fred
3/13/2010 11:05:26 PM
While formatting the cells didn't work directly, I thought about it
some more after your question, and the following worked:

   cells(n, m).formula = cells(n, m).value

After processing the column with the above, formatting does work and
stuff sorts.

Fred Holmes

On Sat, 13 Mar 2010 15:10:38 -0700, "Dennis Tucker"
<dennis13030@cox.net> wrote:

>Does the normal "Format Cells..." not work?
>
>
>
>"Fred Holmes" <fsh@his.com> wrote in message 
>news:0lvnp512c0f07p58oq295b05oeo4cgirqp@4ax.com...
>> Using Excel 2000
>>
>> I have an Excel worksheet (.xls) that was produced by an accountant
>> exporting data from a particular accounting package.  There is a colum
>> of dates of the m/d/yyyy format that don't sort as dates.  They sort
>> alphabetically, which is of no use.
>>
>> I'm looking for the VBA function that will convert text in the
>> m/d/yyyy format to an Excel date number.
>>
>>   Cells(n, m).formula = DateConverter(Cells(n, m).value)
>>
>>   or some such
>>
>> And I will then write a loop macro to process each cell in the
>> column/range to convert the cell contents to an Excel date of the
>> usual kind that will sort properly.
>>
>> A search in the Excel VBA help on "date" doesn't yield anything
>> interesting, and I can't think of a good search keyword for this.
>>
>> I have no idea how this circumstance occurred, but I don't want to
>> bother the accountant about it.  I have downloaded a lot of banking
>> and credit card stuff in .csv file format, opened it in Excel, and
>> saved it as an .xls and have always (usually?) gotten proper Excel
>> dates to appear in the cells in the date column.
>>
>> Many thanks,
>>
>> Fred Holms 

0
Fred
3/14/2010 12:08:53 AM
Reply:

Similar Artilces:

How do you get the maximum value to display in text box form
In a form, if I have 4 text boxes and 3 of them will contain a number. How do you get the maximum value to display in the 4th. I have tried many different things, I can make it work for a single field in a table, but that finds the value through all records. I just want it to display the max value from the numbers on the form, which could be different for various records. You could use something like this as the Control Source of the 4th text box (the underscore is for ease of reading here, and must be removed from the Control Source): =IIf([Field1]>[Field2] And [Field1]>[Field3],...

Can you automatically date stamp emails that you send out? #2
My emails need a date stamp on them as I send them. It does not appear that you can do this..does someone have an idea to do this? Thanks ...

Date range for report
Hi, I tried to set up controls in a form in order to prompt for date criteria in a report. I added textboxes to the report, with references to the forms controls, in the controlsources for those boxes. When I try to run the report it doesn't prompt for the dates, it just puts #Name? where those controls are. If I open the form and enter the dates directly into the form and click ok it brings up the report with the proper filters though. Is there a way to make the report open the form to prompt for the dates, rather than having to open the form itself? ...

Summing numbers between a date range
I need to sum values in a row of data where the date in the column of the row falls within a certain date range. Here is an example of the data. The data to be summed is in sheet one and the dates used for the criteria are in sheet 2. Sheet_1: col a col b col c col d Nov 15/92 Feb 15/93 Sep 15/93 Feb 15/94 15.00 20.00 30.00 10.00 Sheet_2: col a col b Jan 1/93 Dec 31/93 Jan 2/94 Dec 31/94 I basically want to sum the data in Sheet_1 that is between a date range based on Sheet_2. For example, sum values in sheet_...

DATE #10
Feb 28,2008 ---> 2008-2-28 Feb 1,2008 ---> 2008-2-1 Format? "����" <cola@hz.cn> wrote in message news:OOQhTbRaKHA.5472@TK2MSFTNGP02.phx.gbl... > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > See your other post "ÎÞÃû" wrote: > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > > > . > Hi, Highlight the cell, right click in the mouse, cell format, custom format and enter yyyy-mm-dd "ÎÞÃû" wrote: > Fe...

What can an Account record be converted from?
The Account record has a 'Originating Lead' field but I cannot see how to convert from the Lead record? If you have a Lead record opened, there's a "Convert Lead..." button on the grid, and there's also a "Convert Lead..." option in the Actions menu. "Agile CRM" wrote: > > The Account record has a 'Originating Lead' field but I cannot see how to > convert from the Lead record? Sorry I didn't explain myself properly. See new question 'How to map Lead fields to Account fields. "Chad Fulda" wrote: > I...

Document Date vs. Batch Date
Can someone explain to me the difference between document date and batch date. The way I understand it, the document date refers to the date the item will hit the subledger and the batch date (if batch date is the posting option) refers to the date the transaction will hit the general ledger. Is this correct? Does this mean that if I have all my periods open for a year and I enter a cash receipt transaction with a document date of 05/01/04 in a batch with a batch date of 06/01/04 that my subledger will not balance to my general ledger for that month? Elizabeth, You've got it ...

how can I insert text file's text into my email in outlook 2007
I used to use outlook express. There is a lovely function to insert text file's text into my currently writing email. How can I do that in my outlook 2007 ? Time before, I first create a text file, say 'payment method.txt', then type in all my standard payment method information to my customer. When I was replying an email with my standard text, I simply use the "insert - text file" function, instead of openning the 'payment method.txt', copy and paste to my email. Can I do that similar in outlook 2007 ? Thank you very much Henry Fung wrote: > I used t...

eliminate spaces after a text field
Hello, I am designing in query and there are spaces after text field. How do I delete the spaces after? Thanks Try something like: NewField: Trim([YourField]) To remove trailing spaces only (not leading spaces): NewField: RTrim([YourField]) Spaces in the middle of the field will not be affected. Use your actual field name in place of YourField. Use whatever you like in place of NewField. "Cam" <Cam@discussions.microsoft.com> wrote in message news:5BB9EA84-D7F8-416C-B2C0-2155F02B8BBA@microsoft.com... > Hello, > > I am designing in query and there are spaces ...

hide a text box in a continuous form
I have a form that i created that display each form as a row in a continous form. What I need is to have one text field on one row not visible when a check box is checked. Does anyone know how to do this? Thanks! Using Conditional Formatting, evalaute the state of the field bound to the CheckBox to set the Background color of the TextBox to match that of the Background color of the form. You might have to set the Disabled prop via CF as well. There is sample code showing one wat to accomplish this here: http://www.lebans.com/conditionalformatting.htm A2K or Higher Only! New Feb 08,2002...

importing text file into excel file using macro
Hi, How to import text file into excel file using Macro? Can this proces run on form menu button? Please help and give me some ideas of doin it. I have attached a text file for your reference. Thanks, An Attachment filename: rdata.txt Download attachment: http://www.excelforum.com/attachment.php?postid=66130 -- Message posted from http://www.ExcelForum.com >Hi, >How to import text file into excel file using Macro? Can this process >run on form menu button? Please help and give me some ideas of doing >it. I have attached a text file for your r...

COUNTIF with DATE Logic
I have an array with hourly data for an entire year. Normally I use CountIf on this array to ascertain how many occurences occur ove certain data ranges over the entire year. So assuming the data is i B1:B8760. I would Count the number of occurences for the whole yea where data is greater than 1: Entire Year =COUNTIF($B$1:$B$8760,>1) However I now want to use this COUNTIF to assess how may occurence occur over certain data ranges in certain months. The easy way to d this is to build 12 separate COUNTIFs and change the array: January =COUNTIF($B$1:$B$744,>1) February =COUNTIF($B$74...

Converting Printmaster mailing list
Does anyone know how to convert a 1066 name mail list used with Printmaster 15 so it can be used with Publisher 2003? What format is the file? -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "golfpapa34" <golfpapa34@earthlink.net> wrote in message news:19fc01c4a7e8$1004ae50$a301280a@phx.gbl... > Does anyone know how to convert a 1066 name mail list > used with Printmaster 15 so it can be used with Publisher > 2003? ...

Outbox messages get stuck with Scansoft PDF Converter Addin
If your Outbox is acting funny and changing messages from waiting to be sent (Italic Bold) to draft status (Normal Bold) and you have Scansofts PDF Converter Addin installed, you need to disable the Addin. This is from the Scansoft KB: Problem: When sending messages to Microsoft Outlook Outbox in off-line mode, the message is converted to a draft instead of a sendable message. Cause: The PDF Converter Com add-in for Microsoft Outlook XP causes messages in Microsoft Outlook Outbox (off-line mode) to convert from sendable messages to draft messages that cannot be sent when Outlook ...

SQL statement in form text control
Hello! In a social services DB, one of the reports is all incidents involving a particular client. There are a few thousand records. I am using an SQL statement which filters records by the client's full name and Birthdate. The SQL is sent to the rowsource property of a combo box. The combination of name and DOB provides 1 unique client. (or none if the DOB is mis-entered or is wrong) The report (controlled by a query with parameters provided by the DOB text control and the combo box.) The combo box seems to be just one more thing to click through, so I thought of changi...

Sure this is simple (date type prob)
Hi all, I have a cell with a date in it representing hours worked. eg 34:58 34 hours and 58 minutes. I need to multiply it by an hourly wage, e 4.52 and get the amount earned. Unfortuantely I do not know how to d this. Can anyone help? -- Cheers -- SO ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com where i5 is 34:58 and j4 is 4.50 = $157.35 =I5*24*$J$4 -- Don Guillett SalesAid Software donaldb@281.com "SOL-ion" <SOL-ion.x9rbb@excelforum-nospam.com>...

date in database compare with the current date
Is that possible to create an alert function, that gives an alert message when a date in Access database is going to expired soon So I need this function to compare the date in database with current date when the dates are the same, it gives an warning msg on the web browser and is that possible to achieve that using Microsoft script editor? Hi, Sure, but you need to write code. The basic idea would be to check when the next event should occur, and run Schedule Tasks, or other tool, appropriately. Sure, you have to check that when the db is loaded, when a record is appended, modified, o...

Convert degree from decimal number to the standard format (degre.
How to Convert degree from decimal number to the standard format (degree ° minutes ´ seconds″. I tried custom but could not work out for some degrees. Thanks On Sat, 26 Mar 2005 08:23:01 -0800, "Zainelabdin" <Zainelabdin@discussions.microsoft.com> wrote: >How to Convert degree from decimal number to the standard format (degree � >minutes � seconds?. >I tried custom but could not work out for some degrees. >Thanks Here's one way: Divide your decimal degrees by 24. Then format the cell as: Format/Cells/Number/Custom Type: h� m' s.00\" ...

Showing a date field as a number
Hi. I was wondering if there was any way that I could have a column showing the date and time (20/12/2007 06:00:00) and another column show the date and time as a number, (29548). This would be done in a select query, but I am not sure how? Is it possible? Regards AJ AJ, Use format... mm/dd/yy hh:nn:ss for 20/12/2007 06:00:00 Use format... #.0000 (with Decimal Places = to suit your needs) for 39436.2500 Use format... # (with Decimal Places = 0) for 39436 -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccs...

How do I flip text
Version: 2008 Hi there, <br> How do I flip text so that I can print on &quot;Iron On Transfer paper&quot;? <br> I managed to flip some word art text, but I'm trying to flip just regular text. Is there a way to do this so that it reads backwards? <br> Thanks! There is no way to mirror regular text in Word. For what you are attempting you need graphics software designed for the purpose. Regards |:>) Bob Jones [MVP] Office:Mac On 3/15/10 11:45 AM, in article 59bb541f.-1@webcrossing.JaKIaxP2ac0, "KBurgo@officeformac.com" <KBurgo@...

Extracting text from cells
Can anyone suggest a formula to use to extract certain text from a cell? The text may be found anywhere in a single cell, it may vary in length and not always preceded by punctuation, however, it will always be preceded by a "M0" or "P0" . Here's an example: CCN 02/04/04, PAID 05/28/03, M02-10708, BOX AE-3912, DD 01/31/08. The task is to extract just the text, "M02-10708". -- AusTexRich Hi: Use the text function FIND in conjunction with LEFT, RIGHT or MID functions to extract it. However, this might prove to be difficult since you say that what you...

text mask problem
A2k. I'm using the following to mask an unbound text box so that the user can ONLY enter 50 characters: CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC I seem to recall this is a problem with access since this also means that editing in that control means that typing in the middle of the string overwrites characters regardless of the how the keyboard key "Insert" is set (on or off). I need to limit the # of charactes AS the user types. I do NOT want to Trim the characters afterward, nor do I want to pop up some lame warning in the BeforeUpdate event or in the ValidationRul...

Sales order to manufacturing due dates
We have our sales orders set to make manufacturing orders manual upon entering a line that is to be made. In the manufacturing series sales item detail window where you can generate a manufacturing order there are 3 date fields (In-house due date, requested ship date, and customer promise date). I noticed that the default for manufacturing orders made from SOP is to schedule it by the backward infinite method. We are wondering is there a way for it to schedule by forward infinite so that on the detail screen it can take the routing and let the salesman know about how long it will tak...

convert char* to wchar_t?
How can I convert char* to wchar_t? c2l6ZV90IG1ic3Rvd2NzKCAtICAgIChtdWx0aWJ5dGUgc3RyaW5nIHRvIHdpZGUgY2hhcmFjdGVy cyBzdHJpbmdzKQpBTlNJIC0+IFVOSUNPREUKICAgd2NoYXJfdCAqd2NzdHIsIC0g0YPQutCw0LfQ sNGC0LXQu9GMINC90LAg0YHRgtGA0L7QutGDINC60YPQtNCwCtCx0YPQtNC10YIg0L7RgdGD0YnQ tdGB0YLQstC70Y/RgtGB0YzRjyDQv9C10YDQtdCy0L7QtAogICBjb25zdCBjaGFyICptYnN0ciwg LSDRg9C60LDQt9Cw0YLQtdC70Ywg0L3QsCDQvtCx0YvRh9C90YPRjgrRgdGC0YDQvtC60YMKICAg c2l6ZV90IGNvdW50IOKAkyDQutC+0LvQuNGH0LXRgdGC0LLQviDRgdC40LzQstC+0LvQvtCyCtC9 0LXQvtCx0YXQvtC00LjQvNGL0YUg0LTQu9GPINC/0LXRgNC10LLQvtC00LAKKTsK Hi, Include <atlbase.h>...

SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6ED3C.00C7C2B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a = list form on a daily basis. Any date may have multiple entries due to = different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in = RAW DATA SHEET. Basically the summary sheet is summing based on the date = grouped in weeks. =20 RAW DATA SHEET DATE DAY CUSTOMER AMOUNT=20 ...