VBA dates collected correctly but output wrongly

Question

I grab dates from one spreadsheet and output them onto another spreadsheet. After grabbing the date, when I debug.print it is in the correct format. When I output the date, debug.print also displays the correct format. However, the format on the spreadsheet the value has just been sent to, doesnt show the correct format.

I am using:

Sheets(SheetName).Cells(RowNum, ColNum).Value = Data
Sheets(SheetName).Cells(RowNum, ColNum).NumberFormat = "dd/mm/yyyy"

after I have pasted the value, but the months and days are still switched the wrong way.

Is there something I am doing wrong?? If I right click the cell it thinks it's date is dd/mm/yyyy but instead of 4th Sept it is showing 9th April.

Cerbera · Accepted Answer · 2014-07-02 01:20:22Z

2

Sorry to resurrect an old post, however I had a problem with VBA outputting a valid date as US style with the actual date changed for example 1st May changed to 5th Jan. I came upon this post but I didn't find the answer I needed here and now that I have worked it out I thought I would share it:

The key is not to define the variable storing the date as a "date" but as a "double", e.g.

Dim ReportDate As Double
ReportDate = Date


Range("E6").Value = ReportDate

This works as it outputs the numeric "date value" which excel then formats locally e.g. 41644 gets formatted as "05/01/14" using UK format or "01/05/14" using US format.

Hope this proves useful to other people (probably me when I forget how I solved it in six months time).

answered Jul 2, 2014 at 1:20

Cerbera

1213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jook · Accepted Answer · 2012-09-17 14:39:17Z

1

This might be some trouble with localization:

Try using NumberFormatLocal, if DanielCooks tip didn't help ;)

edit: erlier it was statet by mister Cook, to check if the given data is correct.

edit:

With my german version I have quite some trouble to use / as the seperator, that is why i tryied with this code .NumberFormat ="dd-mm-yyyy;@" - works fine; I can switch days and month as I like.

edit:

With .NumberFormatLocal = "TT/MM/JJJJ" I have to use the german shorts for day, month and year, but now I can use / as the seperator.

You should experiment a litte bit with some formats strings ;)

edited Sep 17, 2012 at 14:39

answered Sep 17, 2012 at 14:27

Jook

4,7523 gold badges31 silver badges54 bronze badges

5 Comments

intrigued_66 Over a year ago

Daniel's tip didnt work :( In excel my locale is set to English(UK)

Daniel Over a year ago

My tip didn't help, hopefully yours does :-)

intrigued_66 Over a year ago

Didnt help :( The data is correct because debug.print is showing the correct values in VBA editor, they're just not showing in the sheet correctly.

Jook Over a year ago

so debug.print on Data gives you what exactly?

intrigued_66 Over a year ago

Posted the "solution"/hack below. Not sure why the problem existed.

intrigued_66 · Accepted Answer · 2012-09-17 16:09:28Z

0

In the end I had to format the cell as "TEXT" to keep the correct format

answered Sep 17, 2012 at 16:09

intrigued_66

17.6k51 gold badges131 silver badges210 bronze badges

Comments

RAS · Accepted Answer · 2014-04-09 09:50:41Z

0

(1) You need to define the variable to "Date" type to read the input, then set the date format before assigning it to the date variable.

(2) You also need to format the date output to make it work !

'**Read the date input****

Dim date1 as Date

Range("B2").NumberFormatLocal = "dd/mm/yyyy"

date1 = Range("B2").Value

'**Output the date****

Range("E2").Value = date1 

Range("E2").NumberFormatLocal = "dd/mm/yyyy"

edited Apr 9, 2014 at 9:50

RAS

8,16017 gold badges68 silver badges86 bronze badges

answered Apr 9, 2014 at 9:33

Joyce Cheung

1

Collectives™ on Stack Overflow

VBA dates collected correctly but output wrongly

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related