+ Reply to Thread
Results 1 to 6 of 6

Date isssue keeps changing the month and day

  1. #1
    Registered User
    Join Date
    05-22-2013
    Location
    Ipswich England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Date isssue keeps changing the month and day

    All I'm running in the "Immediate" window is the following;

    cells(1,1) = "01/04/2013"
    Date appears in the cell as "04/01/2013" but right aligned

    cells(1,1) = "23/04/2012"
    Date appears in the cell as "23/01/2013" but left aligned

    I assume the first instance, Excel sees it as a date, and reformats it hence it's right aligned. The second instance sees it as a string, hence left aligned.

    I've checked my Locale settings and its all set as UK date dd/MM/yyyy. I've manually set the cell too, but still the same when I run that line of code.

    What am I missing or doing wrong?

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Date isssue keeps changing the month and day

    I think if you enter cells(1,1) = "04/23/2012" it should put it as right aligned but keep the local date setting of dd/mm/yyyy.

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Date isssue keeps changing the month and day

    or use a date function
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    05-22-2013
    Location
    Ipswich England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Date isssue keeps changing the month and day

    I know that will fix it, but its very difficult to work with that solution.

    This is a snippet of code that I'm trying to do as part of a larger chunk of code and its part of a function called from multiple locations. Making that change would give me a lot of headache to ensure it doesn't break anything.

    I've never had an issue with the date within the variables, but as soon as I try to display the value in the cell, this occurs and coding an "exception" isn't what I want to do.

    Code scenario
    if commencedate = "" then
    commencedate = SetQuarterDate(QDate)
    cells(x,y).value = commencedate
    end if

    Bacially if someone hasn't put in a project commence date, it will use another field QDate to set the date.

    Hence SetQuarterDate is a function, takes in a string with data "Q1 - 12/13" and sets it to "01/04/13"

    even if I use format(commencedate,"dd/MM/yyyy"), the date still gets displayed as "04/01/2013".

    I can not change the "SetQuarterDate" function as its called elsewhere. And I rather not create a new function.
    Last edited by Lextar; 05-22-2013 at 10:33 AM.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Date isssue keeps changing the month and day

    They are both strings.

    If you want to put a 'real' date in a cell try this.
    Please Login or Register  to view this content.
    PS Why are you using strings for dates?
    If posting code please use code tags, see here.

  6. #6
    Registered User
    Join Date
    05-22-2013
    Location
    Ipswich England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Date isssue keeps changing the month and day

    Thanks, cdate fixes it. I responded and then saw the second post.

    And I don't know why I'm using string for dates Its used as string all over the code I'm working with.

+ 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