Member Avatar for 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

  • 3 Contributors
  • 8 Replies
  • 197 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by BPNavigator

Dani AI

Generated

: the symptom you saw (days and months swapping despite a cell format of dd/mm/yy hh:mm) is classic: Excel stores dates as serial numbers and the problem comes from how a text input is parsed into a Date. Parsing is locale- and parser-dependent, so an ambiguous string like "01/02/06 12:00" can become Jan 2 or Feb 1 depending on the system or the parser used. 's regional-settings idea can help on a single machine, and rightly moved the InputBox result into a Date variable — that reduces display/format confusion — but a more robust approach is to parse the user string yourself so it never relies on implicit, locale-driven parsing.

Below is a small, practical pattern that (1) forces the worksheet number format, (2) accepts a predictable DD/MM/YYYY HH:MM text entry, (3) validates and parses day/month/year/hour/minute explicitly, and (4) writes a true Date value to the cell.

Sub EnterDateTime_ExplicitParse()
    Dim s As String, dt As Date
    Dim dParts(), tParts()
    s = InputBox("Enter date & time as DD/MM/YYYY HH:MM", "Date/time", Format(Now, "dd/mm/yyyy hh:nn"))
    If Trim(s) = "" Then Exit Sub
    s = Replace(Trim(s), "-", "/")
    On Error GoTo BadFormat
    dParts = Split(Split(s, " ")(0), "/")
    tParts = Split(Split(s, " ")(1), ":")
    dt = DateSerial(CInt(dParts(2)), CInt(dParts(1)), CInt(dParts(0))) + _
         TimeSerial(CInt(tParts(0)), CInt(tParts(1)), 0)
    With Sheets("Figures").Range("B6:B50")
        .NumberFormat = "dd/mm/yy hh:mm"
        .Cells(1, 1).Value = dt
    End With
    Exit Sub
BadFormat:
    MsgBox "Invalid date/time. Use DD/MM/YYYY HH:MM", vbExclamation
End Sub

Notes and quick checks:

  • Make sure the target cells are not formatted as Text (look for leading apostrophes or left-aligned entries).
  • Two-digit years are ambiguous; ask for 4-digit years or normalise them in code.
  • If this workbook will be used on other machines, prefer explicit parsing (above) or ask for an ISO input (YYYY-MM-DD) to avoid regional ambiguity.
  • For a better UX, replace InputBox with a simple UserForm (separate controls for day/month/year and hour/minute) or a date-picker control when available.

Recommended Answers

All 8 Replies

Member Avatar for 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.

Member Avatar for 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

New_WinZip_File.zip (64.96 KB)
Member Avatar for 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.

Member Avatar for 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

Member Avatar for aktharshaik

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

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.