+ Reply to Thread
Results 1 to 11 of 11

Date Value not recorded correctly in cell

  1. 03-21-2022, 03:57 AM #1
    gamenano
    gamenano is offline
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Date Value not recorded correctly in cell

    Hi, I have this frustrating issue when appending a date into a sheet via a textbox in a user form. The locale of my PC is set to the UK but I am still getting the day and month in the wrong way if the day value is below 12. So if the first 2 numbers of the textbox inputted value is 12 or below then it will think the first 2 digits is a month value and the 3rd and 4th digits which should be the month value will then become the day value. Also, in the sheet itself, this becomes a text and not a date value. I use the below code in a command button to put that to a sheet. Please help guys...... 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 03-21-2022, 04:38 AM #2
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Date Value not recorded correctly in cell


    Hi,

    just use the VBA function CDate rather than Format …
    Reply With QuoteRegister To Reply
  3. 03-21-2022, 04:48 AM #3
    gamenano
    gamenano is offline
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Date Value not recorded correctly in cell

    Thank you Marc! Will give that a try now.
    Reply With QuoteRegister To Reply
  4. 03-21-2022, 04:50 AM #4
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,436

    Re: Date Value not recorded correctly in cell 

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 03-21-2022, 04:51 AM #5
    gamenano
    gamenano is offline
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Date Value not recorded correctly in cell

    Hi Marc, now the value is a date value but the day and month are still the wrong way around. Is there any way to force the day and month in UK format?
    Reply With QuoteRegister To Reply
  6. 03-21-2022, 04:56 AM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,436

    Re: Date Value not recorded correctly in cell

    See post #4. That works for me in the UK.
    Reply With QuoteRegister To Reply
  7. 03-21-2022, 04:59 AM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,436

    Re: Date Value not recorded correctly in cell 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 03-21-2022, 04:59 AM #8
    torachan
    torachan is offline
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,304

    Re: Date Value not recorded correctly in cell

    try the code format below, also ensure your column is date formatted the same.
    dates can sometimes be a pain and you have to use a 'big hammer' to get them right.

    Please Login or Register  to view this content.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
    Reply With QuoteRegister To Reply
  9. 03-21-2022, 05:54 AM #9
    gamenano
    gamenano is offline
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Date Value not recorded correctly in cell

    Thank you all!!! It works brilliantly. Great support from this forum as always.
    Reply With QuoteRegister To Reply
  10. 03-21-2022, 06:29 AM #10
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,436

    Re: Date Value not recorded correctly in cell

    You're welcome. Thanks for the rep.
    It works brilliantly.
    What works brilliantly?


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  11. 03-21-2022, 08:55 AM #11
    torachan
    torachan is offline
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,304

    Re: Date Value not recorded correctly in cell

    thanks for the feedback and rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Recorded macro not running correctly reasons?
    By greenfox74 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-10-2020, 12:36 AM
  2. Formula for last recorded date
    By bongofbobledore in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-06-2018, 03:48 AM
  3. Cell linked to Microsoft MonthView Control not formatting date correctly.
    By jeffadvanced in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2016, 05:08 PM
  4. [SOLVED] Recorded Pivot Table Macro Doesn't Do What I Recorded!
    By diakonos1984 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2013, 11:01 AM
  5. UK Date not displayed correctly in cell
    By ES_Jake in forum Excel General
    Replies: 6
    Last Post: 10-09-2009, 04:36 PM
  6. changing a recorded macro - date problem....
    By Daan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2005, 12:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1