+ Reply to Thread
Results 1 to 10 of 10

Converting DATE format to GENERAL format?

  1. #1
    Registered User
    Join Date
    07-04-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Converting DATE format to GENERAL format?

    Hi there,

    I have a program that exports an Excel with dates in it, and those dates are in 'General' format (which is fine). I use this exported excel in a Macro, and based all the Macro functions on the exact formating of the exported excel.

    However, when I manually edit the date in this exported Excel (even putting the cursor in the edit box and not changing anything!) it automatically changed it to the Date format. When I then put that into the Macro it gets everything wrong, so it really needs to stay in General format...

    I've tried, in vain, to have a (for example) '19/08/2011' that is in General format, but it seems impossible.

    Is there any possible way to do this?

    Thanks a lot.

  2. #2
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: Converting DATE format to GENERAL format?

    Have you tried formatting as text instead of General?
    -Greg If this is helpful, pls click Star icon in lower left corner

  3. #3
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: Converting DATE format to GENERAL format?

    Sorry...I just re-read your post and saw that you have conditions based on the formatting. Maybe you could post your code? Thanks!

  4. #4
    Registered User
    Join Date
    07-04-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Converting DATE format to GENERAL format?

    Hiya,

    This is what the program exports (note: the date cells on the right are in GENERAL format, but if you select it and put the cursor in the edit box it changes to date).

    I basically need to find out how to get the date like it's in there, as a GENERAL format.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-04-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Converting DATE format to GENERAL format?

    Thanks a lot for the quick response by the way!

    Hope my attachment helps

  6. #6
    Registered User
    Join Date
    07-04-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Converting DATE format to GENERAL format?

    And also, this is what I'm asking (someone else has asked the same thing, but more eloquently) but annoyingly the answer is hidden (and requires credit card details to show it - dammit!).

    http://www.experts-exchange.com/Soft..._24144083.html

  7. #7
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: Converting DATE format to GENERAL format?

    The only thing I can think of is maybe adding an apostrophe as a prefix to the date. You'd need to do this in the transfer process. You'd have replace all cells that contain dates with this for example: 07/08/2011 changes to '07/08/2011. I could help you with the code for that if you like.

  8. #8
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: Converting DATE format to GENERAL format?

    I checked out the solution on the Experts-Exchange site.
    Hi Barry and Dave,

    Barry's solution worked perfectly for an Excel beginner:
    1) Insert "dummy" column (for example, A) next to the date column to convert (for example, B)
    2) In the dummy column, first cell (or 2nd cell down if there are column headers), enter:
    =TEXT(A1,"m/d/yyyy")
    3) Copy the formula down the rest of the column
    4) Select the entire dummy column (except the heading if there is one), and copy
    5) Paste entire column over the original wrong dates in the original (A) column, Right click to paste instead of CTL-V and select Paste Special --> Values to paste the values instead of the formula, or just use Edit > Paste Special > values

    Dave - thank you so much for your response, but it's too sophisticated for me. IYour solution would be oerfect if I had the time to figure out everything, but I just need a simple Excel solution right now. But thank you for your input. Someday, I may be resorting to VBA for some of these solutions.
    His comment to dave was in regard to a VBA solution. I'm pretty sure the accepted solution in that thread is not what you're looking for. http://www.experts-exchange.com/Soft..._24144083.html

  9. #9
    Registered User
    Join Date
    07-04-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Converting DATE format to GENERAL format?

    Hi, that response on the site seems to work perfectly!

    Thanks a lot!

  10. #10
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: Converting DATE format to GENERAL format?

    That's great news! If you're satisfied with that answer, would you mind marking this thread as solved? Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1