Automatically converting date format in cell on entry

[kidneythief]

Hi! I'm sorry if this has been asked in some other form but I couldn't seem to find it in the forums. I need a column to convert date entries from this format (Jan 1, 2000 12:00 PM) to a short date format (mm/dd/yy) automatically once the date is entered within the same cell.

The raw data comes from sheets generated by a POS app, which is then cut and pasted directly into this Orders Sheet:

ReceiptId	Date	Cashier	CustomerName	CustomerNumber	CustomerAd	EntryType	EntryName	EntryAmount
KM-17	Dec 15, 2021 11:22 PM	Kiyo Miura	LHARRY FOODSTORE	9226220777	NOVALICHES MARKET
KM-17						Item	Bossing Chicken Hotdogs 200g (144 X 28.33)	4079.52
KM-17						Item	Lharry Siomai 1kg (72 X 107.62)	7748.64
KM-17						Item	Lharry Siomai 500g (72 X 54.04)	3890.88

The data needs to be ordered in a certain way so I have a button to the side which upon clicking transposes the sheet contents into a different sheet called Transposed using this VBA code:

Private Sub Transpose_Click()

    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, FR As Long, cnt As Long, i As Long, ii As Long, rng As Range
    Set srcWS = Sheets("ORDERS")
    Set desWS = Sheets("TRANSPOSED")
    With srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            FR = .Areas.Item(i).Row
            cnt = .Areas.Item(i).Rows.Count
            With desWS
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(cnt - 1) = Range("B" & FR)
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(cnt - 1) = Range("C" & FR)
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(cnt - 1) = Range("D" & FR)
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Resize(cnt - 1) = Range("F" & FR)
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(cnt - 1).Value = Range("I" & FR + 1).Resize(cnt - 1).Value
                With CreateObject("VBScript.RegExp")
                    .Global = True
                    .Pattern = "\(([^\)]+)\)"
                    For Each rng In Range("H" & FR + 1).Resize(cnt - 1)
                        If .test(rng.Value) Then
                            For ii = 0 To .Execute(rng.Value).Count - 1
                                desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1) = Split("'" & .Execute(rng.Value)(ii).submatches(0), "X")(0)
                                desWS.Cells(desWS.Rows.Count, "H").End(xlUp).Offset(1) = Split("'" & .Execute(rng.Value)(ii).submatches(0), "X")(1)
                                desWS.Cells(desWS.Rows.Count, "G").End(xlUp).Offset(1) = Split(rng, "(")(0)
                            Next ii
                        End If
                    Next rng
                End With
            End With
        Next i
    End With
End Sub

The contents come out like this:

DATE	SALESMAN	STORE NAME	LOCATION		PACK#	ITEM	PRICE/PACK	TOTAL
Dec 15, 2021 11:22 PM	Kiyo Miura	LHARRY FOODSTORE	NOVALICHES MARKET		144	Bossing Chicken Hotdogs 200g	28.33	4079.52
Dec 15, 2021 11:22 PM	Kiyo Miura	LHARRY FOODSTORE	NOVALICHES MARKET		72	Lharry Siomai 1kg	107.62	7748.64
Dec 15, 2021 11:22 PM	Kiyo Miura	LHARRY FOODSTORE	NOVALICHES MARKET		72	Lharry Siomai 500g	54.04	3890.88

\
Is there anything I can change or add to the VBA code to convert the date and time format to a short date format when the B column from the Orders Sheet is transferred to the A column in the Transposed Sheet?

My brain has stalled the past couple of hours so any help would be greatly appreciated!

 

[Jeffrey Mahoney]

Put this after the last End With line

desWS.Range("A:A").NumberFormat = "mm/dd/yy"

 

[kidneythief]

Put this after the last End With line

desWS.Range("A:A").NumberFormat = "mm/dd/yy"

Hi Jeffrey! This doesn't seem to work, but I did notice that the source data comes in a general format, not numbers or dates. I assume I would need to convert that first before applying the NumberFormat code? How would I go about doing that? Thank you!