0

I have few cells where I fill date in those using 'FormatDatetime' function,

code:

Range("AX1") = FormatDateTime((Docx.getAttribute("r1ed")))
Range("AX2") = FormatDateTime((Docx.getAttribute("r2ed")))
Range("AX3") = FormatDateTime((Docx.getAttribute("r3ed")))
Range("AX4") = FormatDateTime((Docx.getAttribute("r4ed")))

If date is separated by "." all the cells would show like "12.1.2013",but if I change my system date format separated by "-","AX4" shows date as still "12.1.2013".but other shows correctly.

I need to have fix for this,since I use these dates' for calculation later in VBA.

Please suggest some answers.

Community
  • 1
  • 1
user2002774
  • 141
  • 1
  • 3
  • 10
  • Would something like the solution at `http://stackoverflow.com/questions/7106238/vba-excel-changing-date-format` work for you? – chuff Feb 22 '13 at 14:48
  • @chuff -> you can put links right in your comment by surrounding them with `()`. You can create an easier name by placing `[link name]`. SO calls it mini-markup. So it would be `[this](http://stackoverflow.com/questions/7106238...)` – Scott Holtzman Feb 22 '13 at 15:37
  • @chuff:I used the function, but when I tried to substract cells like "AX1-AX7" i get "#VALUE!", but both have same format like '2013-20-02'.What`s wrong here??? – user2002774 Feb 22 '13 at 15:50
  • Check whether the dates are actually text strings. If so, you can convert them to date values using the DATEVALUE("text_date") function. – chuff Feb 22 '13 at 16:04
  • One thing I dont understand,how come only one cell is showing format in a different format?? – user2002774 Feb 22 '13 at 16:06

1 Answers1

0

I think your problem is that FormatDateTime() returns a string, change it to DateValue() instead. If the return from Docx.getAttribute() contains dots you'll need to replace them with slashes first.

So;

'[AX1] is the same as Range("AX1")
[AX1] = DateValue(Docx.getAttribute("r1ed"))
[AX2] = DateValue(Docx.getAttribute("r2ed"))
[AX3] = DateValue(Docx.getAttribute("r3ed"))
[AX4] = DateValue(Docx.getAttribute("r4ed"))

Or, if there are dots;

[AX1] = DateValue(Replace(Docx.getAttribute("r1ed"), ".", "/"))
[AX2] = DateValue(Replace(Docx.getAttribute("r2ed"), ".", "/"))
[AX3] = DateValue(Replace(Docx.getAttribute("r3ed"), ".", "/"))
[AX4] = DateValue(Replace(Docx.getAttribute("r4ed"), ".", "/"))

If this doesn't solve the issue, can you please post more info about what Docx.getAttribute() is returning please.

Edit: Also, knowing the format you need the cells to contain would be helpful - I'm assuming proper dates will be acceptable - You might need a string with a date in a certain format. If that's the case you could wrap the above with something like;

[AX1] = Format(DateValue(Docx.getAttribute("r1ed")), "dd/mm/yyyy")

It might be that FormatDateTime() is betraying you, Format() might be more flexible

Richard
  • 312
  • 1
  • 6