Sleeper: Date Format

[utreg-4ever]

Hi there! Very nice forum here with nice people. I learn a lot of others' postings and got quick replies on my first posting last week. Awesome!

here is a 2nd one.

I've got problems with date format.
I copy and pastespecial(values) data into a worksheet where some calculations are done (all with VBA).
The data that concerns a date are then still in the my preferred ddmmyyyy-format.

Yet, as i use the date in a conditional formula, the VBAcode treats the date automatically as mmddyyyy. You may understand that this is annoying. the code is similar to:

MyArray()
MyArray=range("A2:F100")
Cells(1,2) = MyDate
Do Until MyDate>EndDate
  Do Until MyArray(row,2)<>MyDate
  ' perform some action
  row=row+1
  Loop
MyDate=MyDate+1
Loop

Also if I then paste the output the dateformats change from ddmmyyyy to mmddyyyy. Is their some code to prevend this?
I've been searching a bit with F1 but couldn't find a proper solution.
I expect this should be some small setting.
Thanks

[unmarkedhelicopter]

native VBA always assumes mmddyyyy unfortunately (it really is annoying isn't it ?)
ddmmyyyy is like your address ; House Number, Street, Town.
More sensible is yyyymmdd so you get the least significant bit last and it's easy to sort visually.
mmddyyyy is just STUPID :- Street, House Number, Town (how about just putting them in random order ?)

What you need to do when pasting is specify your destination format at the same time so something like :- range("A1").NumberFormat = "ddmmyyyy"

[utreg-4ever]

thanks for your reaction. but what if only one column in the range needs to be reformatted like this? Is there an easy way to do this?

Or should i do it after i pasted the range with a separate line of code like:
Column(A:A).NumberFormat = "ddmmyyyy"

[unmarkedhelicopter]

Or should i do it after i pasted the range with a separate line of code like:
Column(A:A).NumberFormat = "ddmmyyyy"

Unfortunately it's your only option

[utreg-4ever]

unfortunately your advice seems not to work as a proper solution for this problem.

I attach an excelsheet with two tabs: 'data' and ' output'.
and a straightforward macro-code:

Sub Problemwithdateformat2()
Application.ScreenUpdating = False
Sheets("Data").Select
Dim StartDate, EndDate, MyDate As Date
StartDate = Cells(3, 16)
EndDate = Cells(4, 16)
MyDate = StartDate
Range(Cells(1, 2), Cells(64000, 2)).NumberFormat = "dd-mm-yyyy"
Do Until MyDate > EndDate
    Row = 2
    Do Until Cells(Row, 2) = MyDate
        Row = Row + 1
    Loop
    Row2 = Row
    Do Until Cells(Row2, 2) <> MyDate
        Row2 = Row2 + 1
    Loop
    ' I need values for row and row2 to select and manipulate a range of data from the same date being MyDate.
    ' I leave those calculationsteps out of the code because it is not relevant
    ' the result of the procedure are values for Hedgeloss and CumTurnover, which i will give values of 1 and 2 respectively for simplicity.
    Hedgeloss = 1
    CumTurnover = 2
    OutputArray = Array(MyDate, Hedgeloss, CumTurnover)
    ' i copy the output for this date to a separate sheet. the output from previous dates are replaced 1 row lower.
    Sheets("Output").Select
    Columns("A:A").NumberFormat = "dd-mm-yyyy"
    Range("A8:BB10000").Copy
    Range("A9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A8:C8") = OutputArray
    ' now i set MyDate 1 higher to calculate Hedgeloss and Cumturnover for the next date.
    MyDate = MyDate + 1
    Sheets("Data").Select
Loop
End Sub

Now, my problem: as you can see in column A of the 'Output'-sheet, the dates are reformatted during the VBA-procedure into mm/dd/yyyy format. in other words: mm and dd are mixed.
Undoubtedly I am unaware of a small piece of code that prevends this. Who could help me?

Thanks

[Gert Jan]

native VBA always assumes mmddyyyy unfortunately (it really is annoying isn't it ?)
ddmmyyyy is like your address ; House Number, Street, Town.
More sensible is yyyymmdd so you get the least significant bit last and it's easy to sort visually.
mmddyyyy is just STUPID

You know what is stupid?
Some narrow minded people in the United States of America, who think that the rest of the world is a small annoying island just of their coast.
What a pain in the ... it must be, that we don't want take over everything YOU think is good for us.

[unmarkedhelicopter]

You know what is stupid?
Some narrow minded people in the United States of America, who think that the rest of the world is a small annoying island just of their coast.
What a pain in the ... it must be, that we don't want take over everything YOU think is good for us.

That's what I like to see someone who really reads the posts before launching an attack. I AGREE WITH YOU (although nowhere near as strongly as you put it) re-read the post Gertrude.

[utreg-4ever]

my posting wasn't meant to startup a discussion about " pains in the ...".

It would be very nice if someone is willing to help me out with my problem!

[Gert Jan]

Hav'nt tried it, but would something like this help?

Sub Problemwithdateformat2()
Dim StartDate As Date, EndDate As Date, MyDate As Date
Application.ScreenUpdating = False
Sheets("Data").Select
StartDate = Format(Cells(3, 16), "dd-mm-yyyy") 'change to preferred format
EndDate = Format(Cells(4, 16), "dd-mm-yyyy")
MyDate = StartDate