Copy Range to new Range and keep DATE format??

[HammerJoe]

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

 

[Dave Peterson]

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

 

[Dave Peterson]

ps. I'm curious what happens when you change the numberformat to that
unambiguous date format, but I bet your code will work if you use:

.value2 = .value2

 

[HammerJoe]

Value2 does work!!! Finally.

I think you figured this out so dont make me wait any longer.

As for the ambigous format, yes it would change on the range M10, but
M3 range would be messed up the same way,

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

I thought of that, maybe the FORMAT in the line :
hisWorkbook.Sheets("Settings").Range(TempWeekRange +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")

is to blame?
I will switch it to CDATE instead and check it out.

 

[Dave Peterson]

Yep.

If you use format(), then it's like typing the value into the cell.

And if your windows short date setting is mdy and you type: 1/2/2003, you'll
get Jan 2, 2003.

Even if you expected February, 1, 2003.

I think it's better to just assign the value (or .value2) and use the number
format to make it look pretty. Take excel's parsing routine completely out of
the process.

 

[HammerJoe]

Thanks for the help Dave.

What is the difference between .Value and .Value2 as an object?

Which Excel parsing routine are you talking about?
You are right about the dates, thats exactly what is happening.
I removed the Format and use Cdate instead anywhere a new date value
is added.
I think I will keep the copy line as well, it's working and I am tired
of messing with this.

Cheers

 

[Dave Peterson]

Cells with Dates/times and currencies are special in excel.

..value2 returns the underlying value (the serial number of the date) of the
cell.

VBA's help describes it in more detail.