Advice to format column with real dates

[ipbr21054]

Please advise how i do as per title so these current dates are not overwritten as Text
Thanks

 

[ipbr21054]

Now changed thanks

 

[Alex Blakenburg]

You're welcome. Glad we could help.

 

[ipbr21054]

After chaning the format yesterday today i have encountered this.
This did do it a few days ago for the 1st of the month & i was advised to format with proper dates & not text dates but looks as though its doing it again anyway, screenshot shown

My userform shows the current date & after the user adding values to other textboxes etc he then sends to worksheet.

You can see that the last entry is shown as 12/04/2024 but we know it should be 04/12/2024
The format drop down also shows what i had done yesterday was correct.

Today i added 2 entries & the date for them is shown correctly.

 

[ipbr21054]

Ive now just spotted something else since yesterday.

When the use completes the form values are sent to my worksheet.
The listbox bon the form shows all outstanfing parcels awaiting delivery.
As you can see the date formate is now all incorrect

 

[Alex Blakenburg]

If you create a New Workbook and enter ctrl+; (semi-colon) what does it show ? Please show us an image including the formula bar for the cell.
Then click on the date for John Williams in Excel and show us an image including the formula bar.

 

[ipbr21054]

I have done a test run in that the textbox on the userform shows todays date 05/12/2024
The values are then sent to worksheet.
In column G the word POSTED is entered.
The date on worksheet is correct for today in that it shows 05/12/2024
So far all good

The userform & Listbox are now updated.
The Listbox for the record i just created shows 12/5/2024 & should be 05/12/2024
Once the parcel has been delivered the user opens this form & selects the customer in question.A code is run so that the word POSTED is then replaced with the date at the time.
Ive just done this & checked the worksheet & it correctly shows 05/12/2024

So it would seem a issue with the way the date is actually shown in the listbox.

See screenshot

 

[ipbr21054]

If you create a New Workbook and enter ctrl+; (semi-colon) what does it show ? Please show us an image including the formula bar for the cell.
Then click on the date for John Williams in Excel and show us an image including the formula bar.

Ah ok just sent above message as you posted.
Will do it now

 

[ipbr21054]

As requested
Semi Colon

John Willaims

 

[Alex Blakenburg]

Thanks. I agree that it indicates the issue is with how the list box is being populated. You might need to start a new thread for that, but if you want to post the code that populates the list box one of use can have a quick look first.

 

[ipbr21054]

Listbox code as requested

    Private Sub populate()
        ' clear the Listbox

        Me.ListBox2.Clear

        ' declare variables

        Dim i As Long, lRow As Long, ws As Worksheet

        ' set ws to the desired sheet

        Set ws = Application.Worksheets("POSTAGE")

        ' determine the last row of that desired sheet

        lRow = ws.Cells(Rows.count, 1).End(xlUp).Row

        ' populate the Listbox by looping down the rows of ws

        With Me.ListBox2

            For i = lRow To 8 Step -1

                'determine if this customer should be added to combobox list

                If UCase(ws.Cells(i, 7).Value) = "POSTED" Then

                    .AddItem Cells(i, 2)    'adds the customer name to combobox first column

                    .List(.ListCount - 1, 1) = Cells(i, 1) 'add the date to second column of line just added

                    .List(.ListCount - 1, 2) = Cells(i, 3) 'add the item to third column of line just added

                End If

            Next i

        End With
    
    End Sub