PDA

View Full Version : Date Format



utreg-4ever
10-08-2007, 11:49 AM
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
10-08-2007, 12:16 PM
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
10-08-2007, 12:27 PM
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
10-09-2007, 01:33 AM
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
10-09-2007, 03:06 AM
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? : pray2:

Thanks

Gert Jan
10-09-2007, 11:57 AM
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
10-09-2007, 03:14 PM
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
10-10-2007, 01:56 AM
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
10-10-2007, 12:44 PM
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