+ Reply to Thread
Results 1 to 8 of 8

Text to Date Only After "Enter" button

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Text to Date Only After "Enter" button

    Hello,

    I have a column with dates.
    Every time that the previous part of the Sub imports new records in the table, the Date values are pasted in Text format.
    I apply this part of code for converting them.

    Please Login or Register  to view this content.
    I'm asking your help because the conversion is effective only when I "enter" the cell (clicking in it or with F2 button) and I press Enter.
    Do you know how to immediately properly convert all the dates?
    Second Option: may I create a loop that emulates the manual procedure that I explained before (click in the cell + Enter)?

    Thanks in advance
    Regards
    Attached Files Attached Files
    Last edited by BrianAll; 08-17-2018 at 03:29 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Text to Date Only After "Enter" button

    I would be happy to try this in your file if you provide it with an example of a date entered as text, but meantime I think this might work (why do you use Offset(0,0)? )

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Text to Date Only After "Enter" button

    Offset(0,0) must be a mistake, I didn't overwrite it properly.

    The code you provided gives the error:
    Object doesn't support property or method.

    Attached the file (ath the previous post), no macro for more simple use.
    Some examples highlighted in yellow.

    Thanks for the help

  4. #4
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Text to Date Only After "Enter" button

    try this

    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Teach me Excel VBA

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Text to Date Only After "Enter" button

    Quote Originally Posted by BrianAll View Post
    Every time that the previous part of the Sub imports new records in the table, the Date values are pasted in Text format.
    Why not correct that part instead of trying to apply a fix later?
    Rory

  6. #6
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Text to Date Only After "Enter" button

    To ImranBhatti:
    Range("B:B").Value = Range("B:B").Value
    Appears to work but only "randomly" some cells are now properly shown as dates on the right hand side of the cell,while some other remain on the left hand side (even if the Format shown is = "Date").

    To rorya:
    already thought about it. In this case is a simple copy-paste process but I can apply the format change when copying the cells or apply a For cycle later just on these. I adopted the later fix since it appears to be a destination format error (before the copy-paste the values are already dates).
    The only thing that appear to work is CDate() in a For Cycle.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Text to Date Only After "Enter" button

    Hard to comment without any code.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Text to Date Only After "Enter" button

    Please Login or Register  to view this content.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 01-22-2016, 09:21 AM
  2. Date format of just "st" "nd" "rd" and "th" with text included
    By notrandom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2013, 05:45 PM
  3. Find "TEXT 1" in a range and enter "TEXT 2" in the adjacent cell
    By madreag in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-28-2013, 05:34 PM
  4. I need to change what the "Enter" button does once it reaches a certain cell.
    By Dmaracic in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-24-2011, 12:51 PM
  5. Replies: 1
    Last Post: 09-15-2010, 01:55 AM
  6. Replies: 1
    Last Post: 04-16-2010, 12:12 PM
  7. Macro to enter a date without the "/" and convert it to a real date entry
    By Argile79 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-14-2010, 05:12 PM

Tags for this Thread

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