Excel: date from form to worksheet

Own / Typical
January 2, 2009 at 07:55:16
Specs: Windows Vista, Pentium V 3GB
I have a problem with a form in Excel 2007.
In the form, I have formatted the date in dd-
mm-yyyy, but when I transfer the date to the
worksheet, it gets the format mm-dd-yyyy. Any
idea how to solve this?

See More: Excel: date from form to worksheet

Report •

January 2, 2009 at 08:20:53
Are you talking about an Access form? Excel doesn't use forms. Or are you using a third-party program?

"So won’t you give this man his wings
What a shame
To have to beg you to see
We’re not all the same
What a shame" - Shinedown

Report •

January 3, 2009 at 07:26:27
No I'm using Excel 2007 and a Form or UserForm in VBA. With the statement

ws.Cells(lngInsertRow, 1).Value = Format(Me.txtDatum.Value, "dd-mm-yyyy")

This is the Dutch way to write dates. I tried to write the value of the userform to the database in the worksheet, but there, it arrives as mm-dd-yyyy (dd and mm are switched). With dates above the 12th of a month (eg 15-01-2009), dd and mm are not switched, but the date arrives as text, because month 15 doesn't exist. I tried several ways to format the value in VBA as well in the worksheet, but with no result.

At this moment, I found a work around: I use the statement

ws.Cells(lngInsertRow, 1).Value = Format(Me.txtDatum.Value, "mm-dd-yyyy")

I switched dd and mm, and Excel switch it again, so it arrives in the worksheet as I want it to! But I think this solution isn't very elegant.

Any suggestions?

Kind regards, Otto

Report •

January 6, 2009 at 09:47:10
dates are a real pain with this sort of thing. I seem to remember having difficulty with the datevalue function on VBA (unreliable which date format it uses). The worksheet command seemed much more stable. So the only thing I can think of is:

ws.Cells(lngInsertRow, 1).Formula = "=Datevalue(""" & Me.txtDatum.Value & """)"

Works on my UK system, HTH

Report •

Related Solutions

Ask Question