Date Format for days 01-12 are americanized

jcrennan · #1 09-06-2015, 08:36 PM

Hi All

I have created a spreadsheet that uses a userform to enter data. The first field of the data is the date which I have set up as dd/mm/yyyy. Once the form is completed the USER presses the Add Record button and the VBA code places the data at the end of the current table.

I have the column set up in the spreadsheet as short date format dd/mm/yyyy.

The issue is for any day 1st to the 12th the date goes into the cell as mm/dd/yyyy however the format of the cell is still dd/mm/yyyy. eg 8th Sept 2015 goes in as 9/08/2015 and becomes 9th Aug 2015. Any date after the 12th works fine.

I have checked though my VBA code by adding msgbox's to see the value of the variable and even upto the line prior to the value being saved into the cell the Variable is correct.

I am adding the value to the cell with ".Cells(lRow, 1).Value = Me.pDate.Value" (see below)

Are there any suggestions as to how I can fix this issue.

Quote:

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
MsgBox (Me.pDate.Value)
With ws
.Cells(lRow, 1).Value = Me.pDate.Value
.Cells(lRow, 2).Value = Me.pType.Value
.Cells(lRow, 3).Value = Me.pParticipants.Value
.Cells(lRow, 4).Value = Me.pEarly.Value
.Cells(lRow, 5).Value = Me.pJunior.Value
.Cells(lRow, 6).Value = Me.pYouth.Value
.Cells(lRow, 7).Value = Me.pAdult.Value
.Cells(lRow, 8).Value = Me.pSenior.Value
.Cells(lRow, 9).Value = Me.pComments.Value
.Cells(lRow, 10).Value = Month(Me.pDate.Value)
End With

NoSparks · #2 09-06-2015, 09:32 PM

see if either of these work

Code:

.Cells(lRow, 1).Value = Format(Me.pDate.Value, "Short Date")

.Cells(lRow, 1).Value = Me.pDate.Text

macropod · #3 09-07-2015, 04:42 AM

Quote:

Originally Posted by jcrennan

I have created a spreadsheet that uses a userform to enter data. The first field of the data is the date which I have set up as dd/mm/yyyy. ...
I have the column set up in the spreadsheet as short date format dd/mm/yyyy.

The issue is for any day 1st to the 12th the date goes into the cell as mm/dd/yyyy however the format of the cell is still dd/mm/yyyy. eg 8th Sept 2015 goes in as 9/08/2015 and becomes 9th Aug 2015. Any date after the 12th works fine.

I think you'll find the serial value of the date being output by the userform is as per the dates displayed in the worksheet...

jcrennan · #4 09-07-2015, 06:49 PM

Thanks

Quote:

Originally Posted by NoSparks

see if either of these work

Code:

.Cells(lRow, 1).Value = Format(Me.pDate.Value, "Short Date")
 
.Cells(lRow, 1).Value = Me.pDate.Text

I tried these but no luck

jcrennan · #5 09-07-2015, 07:05 PM

Quote:

Originally Posted by macropod

I think you'll find the serial value of the date being output by the userform is as per the dates displayed in the worksheet...

It looks like this could be the issue but is there a way to fix this, it seems anything days 1-12 come across as serial numbers but all other dates come across as a short date.

NoSparks · #6 09-07-2015, 07:11 PM

jcrennan

Can you post a sample file with user form which behaves this way ?

Also, what is the setting of Short Date in your regional settings ?

jcrennan · #7 09-07-2015, 08:40 PM

Quote:

Originally Posted by NoSparks

jcrennan

Can you post a sample file with user form which behaves this way ?

Also, what is the setting of Short Date in your regional settings ?

I have attached an empty copy

If you goto the Totals Worksheet and use Add Entry button this will bring up the form.
When you click the Add record button on the form the details from the user form get added to the Programs Worksheet.

To test select an entry that is in the 1st twelve days on the month and then also do an entry for one that is in the later part of the month.

The PC regional settings is English(Australia) Short Date d/MM/yyyy

macropod · #8 09-07-2015, 09:11 PM

Try changing:
Me.pDate.Value = Format(Date, "dd/mm/yyyy")
to:
Me.pDate.Value = Format(Date, "dd mmm yyyy")

jcrennan · #9 09-07-2015, 09:37 PM

Quote:

Originally Posted by macropod

Try changing:
Me.pDate.Value = Format(Date, "dd/mm/yyyy")
to:
Me.pDate.Value = Format(Date, "dd mmm yyyy")

Thanks Very much that seems to work fine and has solved my issue

NoSparks · #10 09-08-2015, 01:33 PM

Glad you got the situation rectified.

For what it's worth, I think the root of the problem is actually in the CommandButton1_Click procedure of the ufPrograms userform.
Commenting out the second formatting line allowed everything to display dd/mm/yyyy as desired and on my machine and I have Regional Settings Short Date set as M/d/yyyy.

Code:

Private Sub CommandButton1_Click()
    Me.pDate.Value = Format(CalendarForm.GetDate, "dd/mm/yyyy")
    'Me.pDate.Value = Format(Me.pDate.Value, Text)
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to count even exact date 90 days	redza	Excel	1	09-17-2013 03:36 AM
Date Field to add 10 Days to Current Date	Erbwon	Word	6	11-12-2012 06:17 PM
Set calendar alarm x days before end date	el_ja	Outlook	2	01-19-2012 10:09 AM
30+ days Variable Day Date Calculations via Fields	ztag	Word	2	01-06-2012 11:12 AM
Creating an Auto-Calc'd Date? Today+7 Days?	SoCalTelephone	Word	0	10-06-2010 10:27 AM