Automatically converting date format in cell on entry

kidneythief

New Member
Joined
Mar 17, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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:

ReceiptIdDateCashierCustomerNameCustomerNumberCustomerAdEntryTypeEntryNameEntryAmount
KM-17Dec 15, 2021 11:22 PMKiyo MiuraLHARRY FOODSTORE9226220777NOVALICHES MARKET
KM-17ItemBossing Chicken Hotdogs 200g (144 X 28.33)4079.52
KM-17ItemLharry Siomai 1kg (72 X 107.62)7748.64
KM-17ItemLharry 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:
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:
DATESALESMANSTORE NAMELOCATIONPACK#ITEMPRICE/PACKTOTAL
Dec 15, 2021 11:22 PMKiyo MiuraLHARRY FOODSTORENOVALICHES MARKET144Bossing Chicken Hotdogs 200g 28.334079.52
Dec 15, 2021 11:22 PMKiyo MiuraLHARRY FOODSTORENOVALICHES MARKET72Lharry Siomai 1kg 107.627748.64
Dec 15, 2021 11:22 PMKiyo MiuraLHARRY FOODSTORENOVALICHES MARKET72Lharry Siomai 500g 54.043890.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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Put this after the last End With line

VBA Code:
desWS.Range("A:A").NumberFormat = "mm/dd/yy"
 
Upvote 0
Put this after the last End With line

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top