PDA

View Full Version : [SOLVED] ndate returning time in Holland not London



heathb
05-09-2017, 09:28 AM
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
05-09-2017, 10:43 AM
Try, formatted as required



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

heathb
05-10-2017, 01:48 AM
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
05-10-2017, 02:12 AM
Try this

heathb
05-10-2017, 02:21 AM
can't access due to security, can you break it down please

mdmackillop
05-10-2017, 02:26 AM
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
05-10-2017, 02:49 AM
still doesn't like the Format - as attached?????

snb
05-10-2017, 02:53 AM
Private Sub CommandButton1_Click()
msgbox Format([CZ2], "mm_dd_yyyy")
End Sub

I think you better keep far from VBA or geography.

heathb
05-10-2017, 03:26 AM
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
05-10-2017, 04:46 AM
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
05-10-2017, 05:01 AM
A useful lesson :yes