2

I need to convert dates (up to last row) in column "C" from the existing format 24/01/2016 to 24.01.2016 The result has to be in date format.

My current code is:

LastRow9 = ws5.Cells(Rows.Count, "C").End(xlUp).Row
For X9 = 1 To LastRow9
searchvalue = Cells(X9, "C").Value
Answer = Split(searchvalue, "/")
ws5.Cells(X9, "A").Value = Answer
ws5.Cells(X9, "A").Value = Format(Answer, "dd.mm.yyyy")
Next X9

the answer i get is 30.12.1899 a bit off the mark

Zakky
  • 59
  • 8

1 Answers1

2

Try changing the Range.NumberFormat property.

with ws5
    .range(.cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp)).NumberFormat = "dd.mm.yyyy"
end with
  • ++ Nice :) You missed a DOT before `Rows` – Siddharth Rout Apr 16 '16 at 08:53
  • I use `.Rows.Count` inside `With .Cells(1, 1).CurrentRegion` so much that I tend to use `Rows.Count` on the worksheet to keep things organized. It can only make a difference if you have an application instance with both xls and xlsx style workbooks open at the same time with the wrong worksheet active and that is quite rare for me. –  Apr 16 '16 at 09:04
  • True, It may be rare for you but We often chose to ignore that. It may work most of the time but if you are running this code from XL2007 and ws5 is a worksheet from XL2003 workbook (compatibility mode) then you will get incorrect results :) See [THIS](http://stackoverflow.com/questions/19985345/vba-excel-compability-mode#comment29753337_19985345) question on the possible error that you may get. And hence I always advise using `.` (DOT) before `Rows.Count` and `Columns.Count` :). – Siddharth Rout Apr 16 '16 at 09:05
  • the code works fine; however; i can't achieve my final objective of doing a lookup with the same date from a SAP report (SAP format is 26.01.2016) because the format is different. when i manually input the date in cell C1 as 26.01.2016 the lookup works. The other way is to increase the date 26.01.2016 by a formula +1 so i get consecutive calendar days each month. Excel's +1 formula does't work with format 26.01.2016 only with 26/01/2016. Any suggestions? – Zakky Apr 17 '16 at 00:47
  • Sorry to say but that would be another question (see [Russian Doll Question](http://meta.stackexchange.com/questions/188625)). The [Range.Find method](https://msdn.microsoft.com/en-us/library/office/ff839746.aspx) can be a little tricky with dates but the SAP data is probably text-that-looks-like-a-date (left aligned in the cell). I would suggest manipulating the SAP data into a **real date** and proceeding from there but there isn't enough information and no sample data to be specific. –  Apr 17 '16 at 00:50