ndate returning time in Holland not London

[heathb]

Hi all,

I have a savedate function that works fine when run in London, howere returns a time when run in holland?? I am presuming it is a default format in excle rather than vba, but how do I change it please[
HTML]Dim ndate As Date
Dim savedate As Variant
ndate = Range("cz2").Value
savedate = Mid(ndate, 1, 2) & "_" & Mid(ndate, 4, 2) & "_" & Mid(ndate, 7, 4)[/HTML]

field cz2 is populated as = 2017/05/08

in London this returns ndate = 08/05/2017

in holland this returns ndate = 12:00:00 AM

Will log this on the excle as well just in case.

Thanks in advance for any help

[mdmackillop]

Try, formatted as required

savedate = Format(DateValue(Range("CZ2")), "mm_dd_yyyy")

[heathb]

thanks, however

savedate = Format(DateValue(Range("CZ2")), "mm_dd_yyyy")

[/QUOTE]

Tried and get the following compile error

Wrong number of arguments or invalid property assignment

[mdmackillop]

Try this

[heathb]

can't access due to security, can you break it down please

[mdmackillop]

Same as before + a button and a message box

Option Explicit


Private Sub CommandButton1_Click()
Dim SaveDate
SaveDate = Format(DateValue(Range("CZ2")), "mm_dd_yyyy")
MsgBox SaveDate
End Sub

[heathb]

still doesn't like the Format - as attached?????

[snb]

Private Sub CommandButton1_Click() 
    msgbox Format([CZ2], "mm_dd_yyyy") 
End Sub

I think you better keep far from VBA or geography.

[heathb]

I think you better keep far from VBA or geography.

, If only I could or we had any help to hand then I wouldn't have too..

[heathb]

there was a call function called Format, hence why it was failing if your interested.. lesson -> don't just use an old process without fully checking it

[mdmackillop]

A useful lesson