Date Entry Not Being Correctly Interpreted

[HTSCF Fareha]

I’m trying to find a solution to a problem with date input interpretation. The dates that are being handled are UK format (dd/mm/yyyy) and are being input into a UK setup of Excel. There are two issues that are affecting accurate and correctly interpreted results.



The leap year issue (this I have read has something to do with Lotus 123 and enabling their spreadsheet formats to be imported into MS Excel)
Any date entered after 01/01/1900, is not input correctly. Although it would appear that only dates where the day is equal to or less than a month value (12) are affected. For example, 05/03/1901 (dd/mm/yyyy) will be shown as 03/05/1901, whereas 14/03/1901 (dd/mm/yyyy) will be shown correctly. The only way to get say 05/03/1901 to be input correctly is to enter it as 03/05/1901.



This is the current section of code (courtesy of Dave) which is being used to control date input.


Private Sub cmbEnter_Click()
' Input known soldier's number and enlistment date to add to selected Regiment / Battalion
Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
Dim Sht As Worksheet, SortRange As Range

If frmEnlistment.lboRegiment.ListIndex = -1 Then
MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
Exit Sub
End If
If frmEnlistment.lboBattalion.ListIndex = -1 Then
MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
Exit Sub
End If
If Not IsNumeric(frmEnlistment.txtSoldierNumber.Value) Or _
frmEnlistment.txtSoldierNumber.Text = vbNullString Then
MsgBox "Enter Soldier Number!", vbExclamation + vbOKOnly, "Soldier Enlistment"
Exit Sub
End If
If Not IsDate(frmEnlistment.txtEnlistmentDate.Text) Or _
frmEnlistment.txtEnlistmentDate.Text = vbNullString Then
MsgBox "Enter date in Day-Month-Year format!", vbExclamation + vbOKOnly, "Soldier Enlistment"
Exit Sub
End If

For Each Sht In ThisWorkbook.Sheets
If frmEnlistment.lboRegiment.List(frmEnlistment.lboRegiment.ListIndex) = Sht.Name Then
With Sheets(Sht.Name)
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For Cnt = 1 To LastCol
If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.lboBattalion.List(frmEnlistment.lboBattalion.ListIndex) Then
LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
For Cnt2 = 3 To LastRow
If CInt(Sheets(Sht.Name).Cells(Cnt2, Cnt)) = CInt(frmEnlistment.txtSoldierNumber.Value) Then
frmEnlistment.txtEnlistmentDate.Text = vbNullString
MsgBox "Soldier number already exists!", vbExclamation + vbOKOnly, "Soldier Enlistment"
frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
Exit Sub
End If
Next Cnt2
.Cells(LastRow + 1, Cnt) = frmEnlistment.txtSoldierNumber.Value
.Cells(LastRow + 1, Cnt + 1) = Format(frmEnlistment.txtEnlistmentDate.Value, "dd/mm/yyyy")


' Sort out 1900 date issue

If Left(CDate(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1)), 4) <> 1900 Then
frmEnlistment.txtEnlistmentDate.Text = Format(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1), "dd/mm/yyyy")
Else
frmEnlistment.txtEnlistmentDate.Text = CStr(Sheets(Sht.Name).Cells(Cnt2, Cnt + 1))
End If
Exit For
End If
Next Cnt
Exit For
End With
End If
Next Sht

With Sheets(Sht.Name)
Set SortRange = .Range(.Cells(3, Cnt), .Cells(LastRow + 1, Cnt + 1))
End With
With SortRange
.Sort Key1:=Cells(3, Cnt), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' Count total number of records


End With

frmEnlistment.txtSoldierNumber.Text = vbNullString
frmEnlistment.txtEnlistmentDate.Text = vbNullString
frmEnlistment.lboRegiment.ListIndex = -1
frmEnlistment.lboBattalion.ListIndex = -1
End Sub

[SamT]

Enter the dates as "MMM d, yyyy"

Lotus, therefore Excel, adds a Feb 29 day to 1900. In Excel, but not in VBA, all DateSerials after Feb 28, 1900 are off by 1. Excel and VBA correctly handles this to display dates in Excel.

Date Functions in VBA are correct even for dates before 1900.

VBA: Cell = Format(Cdate("Mar 9, 1989"), "dd/mm/yyyy")) will only return 09/03/1899, not 03/09/1899.



Do this: In A1 enter "Jan 1, 1900". Format Range("A1:C3") as a date. In B2 enter "=A1+365"
Run this Sub
Sub t4()
Cells(2, 2) = CDate(DateAdd("d", 365, Range("A1")))
Cells(3, 2) = CDate(DateAdd("d", 365, "1/1/1900"))
End Sub

Also see
Enter "Feb 29, 1900" in C1

Sub t()
MsgBox "IsDate Feb 29, 1900? " & IsDate("Feb 29, 1900")
MsgBox "IsDate Range C1? " & IsDate(Range("C1"))
End Sub

Moral is "Don't mix Excel Dates and VBA Dates." Good news is that User Form Control Values are Strings, not Excel Dates

[Paul_Hossler]

SamT --


Date Functions in VBA are correct even for dates before 1900.


Moral is "Don't mix Excel Dates and VBA Dates."

I didn't know this. For some weird reason I'd thought that Excel was consistent

[HTSCF Fareha]

Thanks for the explanation, Sam.

I tried the examples you gave.

Am I correct in assuming that there is no easy way to avoid a user entering dates as described (i.e. Mar 9, 1889)?

This is done via a Userform and the date is input into a TextBox, so having to type the first three letters of the month will become tedious and doesn't feel natural to us in the UK. Is there a way that the MMM d, yyyy format can be changed "behind the scenes" after the date is typed into the userform in dd/mm/yyyy format?

[SamT]

Paul, the only wrong date I was able to create was by adding numbers to a date in a cell, but only if the cell'sdate was between 1/1/1900 and 2/28//1900 and the new date was after 2/29/1900. VBA's DateAdd worked fine in that case

[SamT]

Fareha,
Yes, in fact it is a good way to visually insure the correctly formatted date was entered

txtEnlistmentDate_Exit()
With Me
.txtEnlistmentDate = Format(CDate(.txtEnlistmentDate), "MMM dd, yyyy")
'I would use a Form level Variable to store the Date As a DateSerial and use that Variable in the rest of the code
'EnlistmentDateSerial = DateSerial(Year(.txtEnlistmentDate), Month(.txtEnlistmentDate), Day(.txtEnlistmentDate))
End With
End sub
When a DateSerial is entered into an Excel Cell, it will be displayed in the Format you select for that cell.

It should be known that the world standard for Date recording is becoming Year Month Day and any format of such ("yy/mm/dd")("yyyy.mm.dd")("yy m d") and even ("yymmdd"). Date viewing remains regional. Computers, still and probably will always, store dates internally as DateSerial.

[p45cal]

re;
The dates that are being handled are UK format (dd/mm/yyyy) and are being input into a UK setup of Excel.
and:

Although it would appear that only dates where the day is equal to or less than a month value (12) are affected. For example, 05/03/1901 (dd/mm/yyyy) will be shown as 03/05/1901, whereas 14/03/1901 (dd/mm/yyyy) will be shown correctly. The only way to get say 05/03/1901 to be input correctly is to enter it as 03/05/1901.

I live in the UK and have Excel setup with the UK locale, and the above is not my experience at all; if I enter 05/03/1901 I get 5th March.

HTSCF Fareha, could you try something for me, start with a newly created sheet, enter 05/03/1901 into a cell and press Enter.
1. What do you see?
2. Select that cell, right-click and choose Format Cells…, you should get a dialogue box like so:
27838

Without changing anything, what do you see in the circled field (I've blanked it out in the picture)?

[HTSCF Fareha]

Hi p45cal,

The results I get are as follows (not sure why they upload in revers order :doh:) :-

Thanks,

Steve

[p45cal]

That looks like what I get here.
Now if you select the the option *14 March 2012 and press OK do you get 5th March or 3rd May?

[HTSCF Fareha]

I get 5th March.

Steve

[p45cal]

So this:
The only way to get say 05/03/1901 to be input correctly is to enter it as 03/05/1901.
no longer happens?

[HTSCF Fareha]

No, it doesn't.

Here is the link to the form that is being used. You will see the date problem.

http://www.vbaexpress.com/forum/showthread.php?68187-Predict-Date-of-Enlistment
(http://www.vbaexpress.com/forum/showthread.php?68187-Predict-Date-of-Enlistment)

[p45cal]

What version of Excel are you using? Is it 32 or 64 bit?
I have an idea that a date/time picker might solve a lot of your problems - be it an MS one or a third party one.

[Paul_Hossler]

From http://www.vbaexpress.com/forum/showthread.php?68187-Predict-Date-of-Enlistment

This might be OBE, but

I set PC to UK English formats, and entered 1234 as 25/12/1950. Col B is formatted as "Short Date"


27850


The commented line makes it into a string

Try the DateSerial()



Dim Y As Long, M As Long, D As Long
Y = Right(frmEnlistment.txtEnlistmentDate.Value, 4)
M = Mid(frmEnlistment.txtEnlistmentDate.Value, 4, 2)
D = Left(frmEnlistment.txtEnlistmentDate.Value, 2)
.Cells(LastRow + 1, Cnt + 1) = DateSerial(Y, M, D)
' .Cells(LastRow + 1, Cnt + 1) = Format(frmEnlistment.txtEnlistmentDate.Value, "dd/mm/yyyy")


Also more thoughts:

1. I always use DateSerial to put dates into worksheet


range("A1").Value = dateserial (2020,12,25)

2. To use the Regional Settings, you need to Format the cell as

27856

and not as Custom "dd-mm-yyyy". This way it stays a date, and if you send the spreadsheet to another location with different regional settings, it will automatically adjust

[HTSCF Fareha]

Thanks p45cal for taking the time to look at this.

[p45cal]

So: '1. What version of Excel are you using?
2. Is it 32 or 64 bit?'

[HTSCF Fareha]

The commented line makes it into a string

Try the DateSerial()



Dim Y As Long, M As Long, D As Long
Y = Right(frmEnlistment.txtEnlistmentDate.Value, 4)
M = Mid(frmEnlistment.txtEnlistmentDate.Value, 4, 2)
D = Left(frmEnlistment.txtEnlistmentDate.Value, 2)
.Cells(LastRow + 1, Cnt + 1) = DateSerial(Y, M, D)
' .Cells(LastRow + 1, Cnt + 1) = Format(frmEnlistment.txtEnlistmentDate.Value, "dd/mm/yyyy")


Also more thoughts:

1. I always use DateSerial to put dates into worksheet


range("A1").Value = dateserial (2020,12,25)

2. To use the Regional Settings, you need to Format the cell as

27856

and not as Custom "dd-mm-yyyy". This way it stays a date, and if you send the spreadsheet to another location with different regional settings, it will automatically adjust

Thanks for this input and advice. I can see how using the serial format for the date should be viewed as best practice.

I'm going to need some help in utilising this in my code. My thinking is that this will remove some of my existing code.

Will this mess up my previously entered data?

[HTSCF Fareha]

Sorry p45cal, forgot to answer this bit.

Office Professional Plus 2019, 32 bit.

[p45cal]

Office Professional Plus 2019, 32 bit.…and will this be just on one computer, or do you expect it to be run on other computers too?

[Sorry, should've asked this before (there are several date picker controls out there, including one from MS that I've used quite a lot and is quite good and handles dates before 1900, but is no longer available by default in more rescent versions of Excel (including your) but could be pressed into service (this might have the advantage that if soon MS decides to reintroduce a newer DT picker there might be little code change - (people have been crying out for MS to reinstate some kind of DT picker for quite a while now)). Anyway I need to make sure it's a suitable one.]

[Paul_Hossler]

Thanks for this input and advice. I can see how using the serial format for the date should be viewed as best practice.

I'm going to need some help in utilising this in my code. My thinking is that this will remove some of my existing code.

Will this mess up my previously entered data?


27863

Somewhat I believe

I think the major issue will be the fact that pre-1900 dates on the worksheet are formatted as strings, even if .NumberFormat = "Short Date" , while VBA can handle pre-1900 dates 99.9% OK (1900 is NOT a leap year)

27864



I like to separate the 'Presentation' from the 'Calculation' layer

That said, a possible path forward "might be" to leave the dates presented in the worksheet as strings (dd/mm/yyyy) but have (for ex) 'ToWorksheet' and 'FromWorksheet' functions so that VBA always has 'real dates' (at least after 100 CE):



SyntaxDateSerial(year, month, day)

The DateSerial function syntax has these named arguments (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#named-argument):


Part
Description


year
Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#numeric-expression).


month
Required; Integer. Any numeric expression.


day
Required; Integer. Any numeric expression.





All calculations, estimating, interpolation, etc. would require that the WS 'look like dates' be converted to 'VBA dates', crunched, and reformatted back


Even simple things like MATCH require different approaches depending on if the date is pre-1900 or post-1900

27865

Do-able. Let me and others think on it. Maybe they have a better way

[Paul_Hossler]

I'd do all calculating / estimating / guessing within VBA

Bring in the data from the WS, process, reformat and return to WS

That way I would know that all my dates are consistent and are real dates.




Option Explicit


Function WS2VBA(s As String) As Variant
Dim Y As Long, M As Long, D As Long
Dim v As Variant

On Error GoTo ReturnBlank
'dd/mm/yyyy or d/m/yyyy

v = Split(s, "/")
D = Right("00" & v(0), 2)
M = Right("00" & v(1), 2)
Y = Right("0000" & v(2), 4)

WS2VBA = DateSerial(Y, M, D)
Exit Function

ReturnBlank:
WS2VBA = vbNullString
End Function


Function VBA2WS(D As Date) As String

On Error GoTo ReturnBlank
VBA2WS = "'" & Format(D, "dd/mm/yyyy")
Exit Function


ReturnBlank:
VBA2WS = vbNullString
End Function




Function WS2Array(r As Range) As Variant
Dim rFirst As Range, rLast As Range
Dim i As Long
Dim v As Variant

With r.Columns(1).EntireColumn
Set rFirst = r.Columns(1).EntireColumn.Cells(1, 1)
Set rLast = .Cells(.Rows.Count, 1).End(xlUp)
End With

v = Application.WorksheetFunction.Transpose(Range(rFirst, rLast).Value)

For i = LBound(v) To UBound(v)
v(i) = WS2VBA(CStr(v(i)))
Next i


WS2Array = v


End Function


'return 1 dim column range
Sub Array2WS(A As Variant, r As Range)
Dim rFirst As Range, rLast As Range
Dim i As Long
Dim v As Variant

ReDim v(LBound(A) To UBound(A))

For i = LBound(A) To UBound(A)
If IsDate(A(i)) Then v(i) = VBA2WS(CDate(A(i)))
Next i

r.Cells(1, 1).Resize(UBound(v), 1).Value = Application.WorksheetFunction.Transpose(v)
End Sub






Sub test()
Dim A As Variant
Dim i As Long


'A contains Dates that work with VBA
A = WS2Array(Worksheets("Hampshire Regiment").Columns(2))


'simple return of Dates formatted as strings dd/mm/yyyy
Call Array2WS(A, Worksheets("Hampshire Regiment").Range("H1"))



'simple processing example of adding one month
For i = LBound(A) To UBound(A)
If IsDate(A(i)) Then A(i) = DateAdd("m", 1, A(i))
Next i

Call Array2WS(A, Worksheets("Hampshire Regiment").Range("J1"))


End Sub

[HTSCF Fareha]

Thanks to all who looked into this. I'll close this thread as solved at this time as the "main" project is here.

http://www.vbaexpress.com/forum/showthread.php?68187-Predict-Date-of-Enlistment