date in vba

[ilspensoroso]

I have already posted this topic but without success. I live in Ireland where the date format is dd/mm/yy

I am using an inputbox in vba to insert a date in an excel sheet as part of a procedure. My Regional settings are dd/mm/yy. The column in excel is formatted dd/mm/yy but when I use the input box as dd/mm/yy the date appears on the sheet as mm/dd/yy. If it is entered as 10/May/02 it will return 10/05/02. But entered as 10/05/02 will return 5/10/02.

Any help would be greatly appreciated

[Ivan F Moala]

On 2002-05-10 07:59, ilspensoroso wrote:
I have already posted this topic but without success. I live in Ireland where the date format is dd/mm/yy

I am using an inputbox in vba to insert a date in an excel sheet as part of a procedure. My Regional settings are dd/mm/yy. The column in excel is formatted dd/mm/yy but when I use the input box as dd/mm/yy the date appears on the sheet as mm/dd/yy. If it is entered as 10/May/02 it will return 10/05/02. But entered as 10/05/02 will return 5/10/02.

Any help would be greatly appreciated

This worked for me.

Sub tester()

Dim dDate



dDate = Format(InputBox("Enter date"), "dd/mm/yy")



With [Sheet1]

    .[A1] = dDate

    .[A1].NumberFormat = ("dd/mm/yy")

End With



End Sub

[Mark O'Brien]

Hi,

Here's a quick example for you, just open a new workbook and use the code, it saves you renaming the "Sheet1" part:

Public Sub main()



  Dim iDate As Date

  

  iDate = InputBox("Insert a Date dd/mm/yy", "Date")

  MsgBox iDate

  Sheets("Sheet1").Range("A1").Value = Format(iDate, "dd/mm/yy")



End Sub

This should work because my regional settings are set as US. (i.e. with the retarded version of the date format. I mean, even the military here uses dd/mm/yy)

HTH

[Mark O'Brien]

Ah, we meet again Mr. Moala.

Why is it that we post variations on the same theme, but seldom use the same techniques?

[Ivan F Moala]

I only bet you y 1 minute....usually don't
post when your online..diff time zone but
it's after 3:00 am in the morning here....
only enought time for a few more Q's.

[ilspensoroso]

Thank you Ivan and Mark. Both of your solutions worked perfectly. Keep up the good work. I hope I can answer a queery myself some day.