Inputbox - Excel 2000 - Dates

[NickEveritt]

I am having problems with dates from an inputbox produced by VBA into excel.

I 100% know that it is the regional settings, all PCs here should be on dd/mm/yy, but as usual they are not all set-up, I have changed my settings so that I can get round ther variations. I have read all the previous posts, and I am missing something here, it doesnt work, so if anyone can tell me exactly what i need to rather than copy and paste the help from VBA 6 that would be great.

The code is to use the inputbox to find a date on a worksheet.

I use this code to get the inputbox entered date into ChosenDate.

ChosenDate = Format(Application.InputBox("Enter a date&quot

When i then try to use the value from ChosenDate to search I get the error.

The value entered is 16/07/02.

the value showing against ChosenDate is 02/07/16, and the regional setting for short are mm/dd/yy.

Basically whatever is entered will always be in UK style dd/mm/yy, so I just need to stop the regional setting messing with it.

I look forward to your many replies!

Nick

 

[rudo]

Hello Nick,

Working with dates often give curious results and problems to solve. I didn't read previous post about this problem, but I would convert ChoosenDate and each of the values you want to compare:

set tf = Workbooks("Testfile.xls&quot.Sheets("Sheet1&quot
For r = firstrow to lastrow
For c = firstcolumn to lastcolumn
If CDate(ChoosenDate)= CDate(tf.Cells(r,c).Value)
then goto found
Next r
Next c

I hope this will solve your problem.

Rudo

 

[rudo]

Hi Nick,

(Posted a reply, but can't see wher it is gone, so once again

I would convert Choosendate and each of the values you want to compare.

If CDate(Choosendate) = CDate(mf.Cells(r,c).Value) then goto found

If explanation too short, just tell.

Greetings,

rudo

 

[NickEveritt]

cdate is based on the locale of the machine, which I have set to mm/dd/yy, the output from the inputbox, which is entered as dd/mm/yy, comes out as mm/yy/dd, and then finally the spreadsheet cells are set to dd/mm/yy!

Its this that makes me confused!

The the inputbox in my mind should be the same as the locale, which it isnt!.

Anyway, due to the code that I have, and want to keep, I want to turn the inputbox value into the spreadsheet version, ie. mm/yy/dd into dd/mm/yy, rather than the other way.

Is this possible?

 

[xlbo]

Best plan here is to use the DateValue function which converts any reasonably formatted date into it's serial number. Serial numbers do not change according to regional settings and therefore can be much more easily compared. Once any finding / matching etc is done, you can simply use
Format(myDate,"dd/mm/yy&quot or whatever for any visual puporses.

HTH
~Geoff~

 

[NickEveritt]

All methods and functions that I have tried will do the same thing, they take what is in the input box and always convert it to the regional setting version of the date.

e.g. if input box is a string, and you use cdate it will use the regional setting, same with datevalue etc.

Also in getting the string to a number, it has to go through the process of changing to a date to get to the number, therefore ending up with a number that doesnt tie up with the date.

I think other than changing the users regional settings by code I will simply tell them to go change their settings!

Unless anyone has the miracle answer

Nick

 

[xlbo]

Try this

Sub datetest()
Dim initdate As String, searchDate As Date
initdate = InputBox("enter date in any format&quot
searchDate = Format(DateValue(initdate), "dd/mm/yy&quot
MsgBox searchDate

Set fCell = Sheets("Sheet1&quot.Columns("A&quot.Find(searchDate)

MsgBox fCell.Address

End Sub

doesn't matter how I enter the date - it gets converted to dd/mm/yy format and gets found in a list of dates

The impotant thing here is to have searchDate dimmed as Date as opposed to string. If it's dimmed as string, it won't get found in a list of dates
HTH
~Geoff~

 

[NickEveritt]

Sorry I think I may not be explaining things well here.

It is not about how it is entered into the input box, (it is always entered dd/mm/yy style). it is about what the input box does with the date, it immediately transfers it to the locale version of the date, i.e mm/dd/yy.

with the code that you sent Geoff, the value that ends up in the sheet is actually completely wrong. I suggest you change your regional settings and see what happens. Im foxed.

Nick

 

[xlbo]

Hmmmm - tricky - I see what you mean now. I think you would be best off having an extra column to the left of your dates (hidden if necessary) that holds the serial number of the date. =Value(A1) for spreadsheet entry (where date is in A1)

then, use the DateValue function in VBA and search for the DateValue rather than the formatted date
eg
DateVal Formatted Date
37452 15/07/02
37453 16/07/02
37454 17/07/02
37455 18/07/02

use the DateVal column to search on - Date serial numbers are NOT dependant on any kind of regional setting
HTH
~Geoff~

 

[PeterMoran]

Hi Nick,

I really think you are going to have ongoing problems unless you get the regional settings the way you want them.

I have been through all of this and finally decided the only way was to get the machines changed so they have the correct settings. I did this as follows:

I included the following macro in my Workbook_Open event of a problem spreadsheet:

Dim Message, Title, Default, MyValue

' Check that the date format is d-m-y - NOT m-d-y (problems!!)
If Application.International(xlMDY) = True Then
Message = "The Windows date format is 'mm-dd-yy' instead of 'dd-mm-yy'. " & (Chr(13)) & _
"Please go to Control Panel and change Regional Settings to " & (Chr(13)) & _
"** English(Australian) ** and then restart Excel."
Title = "Windows Date Format"
MyValue = MsgBox(Message, , Title)
End
End If

(Unfortunately you can't change these values from within Excel - so the user has to manually change them)

This means the spreadsheet can't be used on the incorrectly set PC until the Regional Settings are the way you need them. This identifies all your problem machines and once done your problems are gone!

All you need to do then is to get your PC Support people to setup all new PC's properly - with the correct Regional Settings!!!!!

Good Luck and HTH!

Peter Moran (Melbourne Oz)

 

[bryanbayfield]

Just to upset the apple cart, is it not the case that VBA just knows the American date format whereas Excel takes it's default date format from Regional Settings?

 

[uberpudge]

Hello Nick,

Maybe my thinking is backwards here, but have you tried reformatting the string to your needs once it has been input?

Try this function:

Public Function ConvertToDMY(ByRef strChosenDate As String, Optional ByRef strDelimiter As String)
Dim strDay, strMonth, strYear As String
Dim lngPos As Long

'Test for optional variable.
If Information.IsMissing(strDelimiter) Or strDelimiter = "" Then
'Default to a slash.
strDelimiter = "/"
End If

'Find the delimiter.
lngPos = Strings.InStr(1, strChosenDate, strDelimiter)

If lngPos > 0 Then
strDay = Strings.Left(strChosenDate, lngPos - 1)
strChosenDate = Strings.Right(strChosenDate, Strings.Len(strChosenDate) - lngPos)

lngPos = Strings.InStr(1, strChosenDate, strDelimiter)
strMonth = Strings.Left(strChosenDate, lngPos - 1)
strChosenDate = Strings.Right(strChosenDate, Strings.Len(strChosenDate) - lngPos)

strYear = strChosenDate

strChosenDate = strMonth + strDelimiter + strDay + strDelimiter + strYear

ConvertToDMY = strChosenDate
Else
ConvertToDMY = "Cannot re-arrange the date string as the delimiter was not found."
End If
End Function

Usage would be:
ChosenDate=ConvertToDMY(ChosenDate)
or
ChosenDate=ConvertToDMY(ChosenDate, "-&quot

Hope this helps,
Pete