Dates in VBA ???

erik.van.geit · Jul 5, 2005

Hi, gurus,

running this

Code:

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 · Jul 5, 2005

Eric, I am an analyst with a mortgage company, as you can imagine we have to manipulate date values quite a lot. I went to answer your question, and I realized I needed to clarify one thing.... What date format are you WANTING to use?

BrianB · Jul 5, 2005

"It is a feature, not a bug." (if you live in America, anyway).

We have to expect VBA code to do its damdest to convert our dates to American format, and take action accordingly.

Here is some standard code I use. You will see that it uses 2 different variables to store the date depending on whether it is required as string or date. It uses Excel's ability to convert data types depending on how the variable is defined (a date is always stored internally as a number). Somehow it does seem to get this right. The main idea was to get the date *in the string format I wanted* no matter which one the user entered.

Code:

Private Sub CommandButton1_Click()
    Dim FindDate As Date
    Dim FindString As String
    '------------------------------------------------------
    FindString = TextBox1.Value
    FindDate = FindString 'Excel converts text
    '- reformat date string & display
    '- useful if date is text instead of date format
    '- use this instead of 'FindDate' in Find lines below
    FindString = Format(FindDate, "dd-mm-yyyy")
    Me.TextBox1.Value = FindString
    '-------------------------------------------------
    '- find date in column 1 & return data to form
    Set FoundCell = Activesheet.Columns(1).Find _
        (what:=FindDate, After:=Datasheet.Range("A1"))
    If FoundCell Is Nothing Then
        MsgBox (FindString & " not found.")
    Else
        ' do something
    End If
End Sub

erik.van.geit · Jul 5, 2005

Thank you, Oorang, for wanting to help.
Thank you, Brian, for the example. I tried out all kind of solutions but stil turning in circles ...

BECAUSE:
am I right with this statement ?

when VBA is treating an inputbox as date it is ALWAYS considered as entered in mm-dd-yy (or mm-dd-yyyy or mm-dd if no year entered) format

(of course we are not taking about just entering an integer)

anyway I want dates in the format dd-mm-yy
the best would be to put the format at the beginning of the macro into a string
Const DateFormat = "dd-mm-yyyy"

kind regards,
Erik

Oorang · Jul 5, 2005

Eric

when VBA is treating an inputbox as date it is ALWAYS considered as entered in mm-dd-yy (or mm-dd-yyyy or mm-dd if no year entered) format

This is 100% accurate.

I tend to decide on my format and then in any instance where a date is read from an input box, recordset, range, etc. I put it in a format command. And then ai never deal with a date anywhere in my code without nesting it in a format. It creates great consitency that way. One way of doing this is to just make a function to format it then refer to it.

Code:

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

BrianB · Jul 6, 2005

when VBA is treating an inputbox as date it is ALWAYS considered as entered in mm-dd-yy (or mm-dd-yyyy or mm-dd if no year entered) format

Not strictly true. We need to remember that *all Inputbox entries are strings* ie. text. It is not until processing is taken further that the text is converted to something else eg. numbers or dates. It is therefore best to explicitly convert the text string output into the type of data required immediately after entry.

erik.van.geit · Jul 6, 2005

thank you,
Oorang and Brian

I'll do further tests and try out Oorangs code.

My statement was inspired by this fact:
when you format an input as 05-07-2005 to "dd-mm-yyyy" it will reverse the 05 ad 07. That's why I think the inputbox reads it as mm-dd-yyyy before it is formatted. So I think the mm and dd will have to be reversed before anything usefull can be done with it in "European" format.

kind regards,
Erik

erik.van.geit · Jul 6, 2005

Oorang,

The code still gives me the wrong results. Date and day are exchanged when put in the worksheet. I don't mean the format, but really the value.
Or do I miss something ?

Map1.xls

A

B

C

D

1

inputbox

result

2

06/07/05

07/06/05

3

7/06/2005

06/07/05

4

06/07/2005

07/06/05

t

to everybody
I feel rather ridiculous and angry at the same time, also confused.
How is it possible that such an important matter has been treated in such an unclear way for the user? Some experts here can probably show how it works, but regarding the numerous posts on this subject, often resolved with workarounds, it is not userfriendly.

The only thing I want to do is ask the user to input a date and find it!

We can not expect from the users to know how to input the date, so we tell them in the Prompt.
Going for a walk now, perhaps that will clear me up!

kind regards,
Erik

Oorang · Jul 6, 2005

Eric make sure that the date formatting in the output column is how you want it to be as well. If you step through the macro with F8 or put a msgbox right after the format statement you will see the variable IS changed. I suspect one of the following happened.
Either
A: The column was already formatted for mm/dd/yy so it re-re-formatted your output.

OR

B: The column was in the "General" format. (In which case excel will switch to mm/dd/yy automaticly if given a date.)

Just reformat the column.
Alternantly, you could put a ' in front of the output OR make the cell text. But these solutions would prevent you from interacting with the value as a date within the spread sheet.

erik.van.geit · Jul 6, 2005

Thanks you for persisting with me, Oorang.

This is really generating more questions than answers. I got the habit to answer questions here, and I often feel people have their hands in their hair. Well this time have my hands in my ... on my head

I was helping somebody. The format is already set. For me a date is a date. Dates are stored as numbers, regardless the displayed format. We should be able to find it. Perhaps with the Datevalue (or how is that called: numbers like 38539)

This is all my research of the last hour. I'm stil confused; Perhaps digged to deep

Code:

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

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 ?

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

especially the last part is

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 ?

Am I dreaming or flipping ?

kind regards,
Erik

EDIT: to answer your questions. The cells are really in the right format: dd-mm-yy.

Dates in VBA ???

erik.van.geit

MrExcel MVP

Excel Facts

Oorang

Well-known Member

BrianB

Well-known Member

erik.van.geit

MrExcel MVP

Oorang

Well-known Member

BrianB

Well-known Member

erik.van.geit

MrExcel MVP

erik.van.geit

MrExcel MVP

Oorang

Well-known Member

erik.van.geit

MrExcel MVP

Similar threads

Forum statistics

Share this page

Dates in VBA ???

MrExcel MVP

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock