• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA & Excel :converted dates stored as general or date

Bossie

Bossie

New Member
When I convert a string to a date (European format dd/mm/yyyy) using VBA, something strange happens when I put the date in a cell in Excel.
A date like 26/02/2014 (which is february 26 2014) is stored as "general" (and alignes to the left of the cell);
a date like 09/01/2014 (which is september 1 2014) is stored as "date" (and alignes to the right of the cell).

Nevertheless, Excel does recognize the value (stored as general) as a date, because when I subtract one from the other (date - general or general -date) Excel does give the right answer.

I first tried Cdate (which gave me the "American" date") and now I use format(datestr,"dd/mm/yyyy") which does give the right date. It's only when I store it in a cell, it acts weird and chooses "standard" or "date" as cell format.

I did search other forums and the best answer i got was that this seems to be a bug in Excel.
But maybe someone here can help me?
 
It seems that "Wulluby" has what seems to be the same problem (see thread
"Format cells 'Custom m/d/yyyy' in excel VBA")
Glad I'm not alone :-)
 
Hi ,

Can you specify what is your system date format ?

If you say that a date such as February 26 , 2014 is being stored in a cell as text ( since it is left aligned ) , it means that Excel is not able to interpret 26/02/2014 ( assuming that is what is being stored in the worksheet cell ) as a valid date.

But then you say that subtracting one from the other gives you a valid numeric result , which is surprising.

Can you upload a sample file with data and code ?

Narayan
 
Hi Narayank991,

thx for replying :-)
My system date format is "d/mm/yyyy"

I'll upload two files (hope it works, it 's my first time here :-) )
In the excel file you'll see two tabs. The first is (part of) the original data. The second tab is the result of my VBA module.
The second file is a file with the vba code. In short it works like this: choose which format the original date is in, rearrange it and store it back to its original cell. (it is still a bit messy, but that's how my brain works: from chaos to order :-) )


greetings
 

Attachments

those who search, find....
I went on searchng and found in (http://www.worksheet.nl/forumexcel/showthread.php?t=78483) a solution. The text is in Dutch, so for those who don't understand this language, and are interested in the solution provided, here's how:
when Excel 'receives' data from VBA (or another source I guess) and this data is formatted (somehow) as general, it gets intelligent and tries to recognize the data. If it sees a date, it interprets it as a date. Which is why "26/02/2014" is not recognized as a date, but "9/1/2014" is. (26 can't be a month).
So the solution lies in the way data is presented to excel. I ran a little test and read strings (like "1z/2g"2014tt"),
extracted the "1" (that's my month), the "2" (that's my day) and the "2014" (that's my year).
Converted them to a date (by using dateserial) and then present these data to excel.
Now every 'date' is interpreted as a date (which is fine by me) and all the cell values are formatted in the same way.
If there's no better way (there usually is), do I close this thread as solved?
 
You must log in or register to reply here.
Back
Top