Excel Date/Time Format

[BPNavigator]

Hi

I'm new to Visual Basic and writting code for excel.
My first attempt at writting a piece of code is to format a range of cells to display a particular date and time format. dd/mm/yy hh:mm.

My problem is that I have written the code that uses a drop down menu from the main menu in excel and an input box, when I enter a specific date and time into the input box and click ok the date is reversed from the format above to the following mm/dd/yy hh:mm. this is causing severe problems for me.

The cells are all formated using the custom format to the above. I'm very inexperinced at writting the code.

Can someone help my write the code I need.
Martin

[aktharshaik]

upload ur excel file here using the Go Advanced button to post the reply and i can try to do the needful and send it back to u.

[BPNavigator]

Heres the Program.
You'll find the Add in Menu at the top, just run through the commands and you'll see my problem the cells I need formated are
b6:b50.

thanks for your help
Martin

[gsatya]

Hi

I'm new to Visual Basic and writting code for excel.
My first attempt at writting a piece of code is to format a range of cells to display a particular date and time format. dd/mm/yy hh:mm.

My problem is that I have written the code that uses a drop down menu from the main menu in excel and an input box, when I enter a specific date and time into the input box and click ok the date is reversed from the format above to the following mm/dd/yy hh:mm. this is causing severe problems for me.

The cells are all formated using the custom format to the above. I'm very inexperinced at writting the code.

Can someone help my write the code I need.
Martin

Hi Martin,
Why don't you change the regional settings date format to dd/mm/yyyy and try. I have tried in your excel it is working here

here is the hint.. Control Panel > Regional Settings > Customize > goto Date Tab change the short date format to DD/MM/YYYY then try entering data in the cells.

In case of problem pls revert.

[BPNavigator]

Hi
I tried changing the date format in control panel, still reversing the month and date.

Any more Ideas?
I'm very inexperinced with excel
Cheers
Martin

[aktharshaik]

Working with it plz wait. 'll provide the solution asap

[aktharshaik]

Confirm that the Regional Settings in the control panel also has the dd/MM/yy setting of the system whenever u r placing this excel file in another new system.

Just check out this code here. I have modified your code in Module2 in procedure Enter_FOAP()

Sub Enter_FOAP()

    Dim xDate As Date
    Sheets("Figures").Select

    ActiveSheet.Unprotect

    Range("N1").Select
    w = InputBox("Enter Voyage No. i.e. IH0603-106L")
    ActiveCell.Value = w

    Range("b6").Select
    xDate = InputBox("Enter date & time for FAOP i.e. 01/01/06 12:00", "Date Time for FAOP", Format(Now, "dd/MM/yy hh:nn"))
'    x = InputBox("Enter date & time for FAOP i.e. 01/01/06 12:00")
    ActiveCell.Value = xDate

    Range("o6").Select
    xDate = InputBox("Enter date & time for ETA Singapore i.e. 01/01/06 12:00")
    ActiveCell.Value = xDate

    Range("t6").Select
    xDate = InputBox("Enter date & time for ETA Japan i.e. 01/01/06 12:00")
    ActiveCell.Value = xDate

End Sub

regards,
Shaik Akthar

[aktharshaik]

You may still have to validate if the data entered in the input box is in the expected date format or not and then go for updating the cell in the WorkSheet. Because a mere inputbox will allow me to enter any garbage, meaningless data also.

[BPNavigator]

Hi Shaik

I inserted your code and all is working extremly well, thats a great bit of code you've saved me a lot of time and effort. Thanks very much.

This is my first project using Visual Basic in Excel, can you recommend any good books where I can learn the language code for use in excel.

Thanks again for all your efforts
Best wishes
Martin