Dates in VBA ???

[erik.van.geit]

Hi, gurus,

running this

Sub test()
MsgBox Date
ReportDate = Application.InputBox("Please enter report date", "REPORT DATE", Date)
'click OK
Range("A1") = ReportDate
Set c = Range("A1").Find(ReportDate)
If Not c Is Nothing Then MsgBox c
End Sub

I get
1. 5/07/05 ("European" format)
2. 7/5/2005 ("American" format)
NO third box

It's a very obvious formatproblem

what to do ?
can somebody point me to a good webpage or thread on this subject?

kind regards,
Erik

EDIT: just added a "Not" at the end of the code

 

[Oorang]

I started to work through this to see what you were saying but I am getting hung up on the second message box because it is expecting a value in A1. What value is in A1 when the code starts?

 

[erik.van.geit]

very kind, Oorang,

After pasting the code here I had put one line lower
Range("A1") = MyString 'do you see this !should be before the msgbox

thank you for your help!

kind regards,
Erik

 

[Oorang]

With this code I notice a couple things.
A.) Even after reformating the string Excel will "helpfully" re-reformat it for you when do Range("A1") = Mystring, if the number format of Range("A1") is General.
B.)Pre fromatting the cell manually with custom format "yyyy-mm-dd" or with code prevents this.

Range("A1").NumberFormat = "yyyy-mm-dd"

CEven when you "Fix" the appearence in the cell with the above code, when you retrive the value using range("a1").value the code will see the value as m/d/yy, because all we did was change the appearence of the value, not the value. You can fix THAT with:
Well... I modified your code a little... let me know what you think?

Sub RunThis()
Dim MyString As String
MyString = InputBox("Enter your annoying american date here.", , Format(Date, "yyyy-dd-mm"))
Call EURODate(MyString)

MsgBox MyString & Chr(10) & _
DateValue(MyString) & " month:" & Month(MyString) & Chr(10) & _
Format(MyString, "#") '38539

Range("A1") = Format(MyString, "yyyy-mm-dd") 'do you see this !
MsgBox Range("A1") & Chr(10) & _
DateValue(Range("A1")) & " month:" & Month(Range("A1")) & Chr(10) & _
Format(Range("A1"), "#") '38539

Range("A1") = MyString 'do you see this !
MsgBox "string put in A1 as is"
MsgBox "rangeA1 = string?"
MsgBox Range("A1") = MyString 'how can this be False ?
MsgBox "Because A1 = " & Range("A1").Value & " and MyString = " & MyString
MsgBox "BUT Format A1 yyyy-mm-dd DOES equal " & Format(Range("A1").Value, "yyyy-mm-dd") & "."
MsgBox "And so..." & Chr(10) & (Format(Range("A1").Value, "yyyy-mm-dd") = MyString) 'how can this be False ?

Range("A1") = Format(MyString, Range("A1").NumberFormat) 'do you see this !
MsgBox "string put in A1 with format of A1"
MsgBox "range = string?"
MsgBox Range("A1") = MyString 'how can this still be False ?
MsgBox "comparing range to formatted string"
MsgBox Range("A1") = Format(MyString, Range("A1").NumberFormat) 'how can this still be False ?
MsgBox "both formatted"
MsgBox Format(MyString, "dd-mm-yy") = Format(MyString, "dd-mm-yy") 'TRUE !!!!!! but can't use this

Range("A1").NumberFormat = "dd-mm-yy"
MsgBox "A1 formatted to dd-mm-yy"
MsgBox "range = string?"
MsgBox Range("A1") = MyString
'HOW CAN THIS BE False when both are formatted in the same way ?

End Sub

Function EURODate(MyString As String)
If IsDate(MyString) = True Then MyString = Format(MyString, "yyyy-dd-mm")
End Function

 

[erik.van.geit]

Thank you again, Oorang !!
It's fine walk along with me

the crucial line is

MsgBox "And so..." & Chr(10) & (Format(Range("A1").Value, "yyyy-mm-dd") = MyString)

but it can't be used to find, instead of this we will need to search for the Mystring formatted as appearing on the sheet

the idea is to ask first for the format and then start the code
but it DOESN'T find the date
the date today is 07-07-05 so today it wouldn't even matter to exchange month and day
no results for this inputs
07-07-05 05-07-07 2005-07-07 (or with / sign)

Option Explicit

Sub RunThis()
'Erik Van Geit
'070705
Dim MyString As String
Dim DateCell As Range
Dim FormatToFind As String

Set DateCell = Application.InputBox("Click in a cell which has a date: " & _
"you need this to be sure the dates will be found. (formatting question!)", "Click in Date", ActiveCell.Address, Type:=8)
    If DateCell Is Nothing Or Not IsDate(DateCell) Then
    MsgBox "operation canceled", 48, "END"
    Exit Sub
    End If
FormatToFind = DateCell.NumberFormat

MyString = Format(InputBox("Enter your annoying american date here." & Chr(10) & _
"format: " & FormatToFind, , Format(Date, FormatToFind)), FormatToFind)
    If MyString = "" Or Not IsDate(MyString) Then
    MsgBox "operation canceled", 48, "END"
    Exit Sub
    End If

Set c = Cells.Find(MyString)
    If Not c Is Nothing Then
    c.Select
    Else
    MsgBox "no match found", 64, "nothing"
    End If

End Sub

Map1.xls
	A	B	C	D
1	04-07-05
2	05-07-05
3	06-07-05
4	07-07-05
5	08-07-05
6	09-07-05
7	10-07-05
8	11-07-05
9	12-07-05
Blad1

what's the error here ?
can you manage to get this or something else to work ?
has somebody a sheet with an working example ?
you may send it at all times till an entire year has passed (till July 07 2006)
I don't care to recieve 365 examples

kind regards,
Erik

EDIT: the "38537" displayed here is not what's appearing in the formulabar of my sheet

 

[Oorang]

In this code example the final input box is prompting you to enter the code in the format of the cell you selected earlier. So lets say the user enters "08-07-05", the input box is still reformatting the string as though it recieved the input in mmddyy. So in this example the user will enter 08-07-05 but what is loaded into the string is 05-08-07. You have to consider some things.

If your users are going to automaticly use yy-mm-dd then you do not need to reformat the input box.

If you format the spread sheet to yy-mm-dd and your users select that cell and type 07/05/05 (Because they are typing in euro date style.) The cell is going to read 05-07-05. Because excel assumes the INPUT will be american style and then it will reformat it to euro style. But if they type 05/05/07 THEN it will read 07-05-05.

So before a working code example can be given we need to know:

1.) What format are the cells formatted with?
2.) How are the users actually typing in the dates?
3.) What are the specfic tasks you want the maco to accomplish.

 

[Andrew Poulsom]

Hope you don't mind me butting in here.

The only sure fire way I have found to get VBA to recognise a date from an InputBox or a TextBox is to use the DateValue function to convert it to a true serial date. The interpretation of the function's argument follows the short date format in the user's Control Panel settings, allowing for a 2 digit year.

Also, as Chip Pearson points out here:

http://www.cpearson.com/excel/DateTimeVBA.htm#Finding

using the .Find method to search for dates can be a bit tricky. Again the DateValue function gives the correct result.

Some sample code, using Erik's posted data:

Sub Test()
    Dim MyString As String
    Dim MyDate As Date
    Dim c As Range
    MyString = InputBox("Enter date in format dd/mm/yy", "Date", Format(Date, "dd/mm/yy"))
    If MyString = "" Or Not IsDate(MyString) Then
        MsgBox "operation canceled", 48, "END"
        Exit Sub
    End If
    MyDate = DateValue(MyString)
    MsgBox "Date is " & Format(MyDate, "dd mmmm yyyy") & " Serial Value " & CLng(MyDate)
    Set c = Cells.Find(DateValue(MyString))
    If Not c Is Nothing Then
        c.Select
        MsgBox "Date found in cell " & c.Address(False, False)
    Else
        MsgBox "no match found", 64, "nothing"
    End If
End Sub

Hope that helps.

 

[Oorang]

Hi Andrew, good input I have a question for you... Have you found a way to deal with the issue of people entering dates in cells formatted yy-mm-dd and then excel assuming it recieved American format input?

(Ex: I type 05-03-10 into such a cell and Excel converts it to 10-05-03)

 

[Andrew Poulsom]

Formatting a cell only changes the appearance of what's in it, not what it contains. To enter a date you must use a form that Excel understands. The way it is interpreted is determined by your Control Panel settings.

 

[erik.van.geit]

Hi, Oorang and Andrew,

Thank you for your posts.
I'm not sleeping but doing research...

The way it is interpreted is determined by your Control Panel settings.

How can this be retrieved? That would be intresting? I think it could help, but still not sure.

sincere regards,
Erik

 

[Oorang]

Ahh that makes sense. Eric, Iif you go to control panels and change then regional setting to say Belgian the default date format in Excel is then j/mm/aa (d/mm/yy) AND it seems to know that it will be recieving the input as such. ALSO VB's default date will change to j/mm/aa (d/mm/yy). So in theroy all you need to do is update your regional setting to a region that uses your format and that should do it for you. Now if you'll have to excuse me, I have to reboot because Google is now showing in french.