Convert to Date "dd-mm-yyyy"

[vmjan02]

I have a row from H8 to BC8 in this format, is there aw way vba code to convert it to dd-mm-yyyy

24/06/22, 00:00

24/06/22, 01:00

24/06/22, 02:00

24/06/22, 03:00

24/06/22, 04:00

24/06/22, 05:00

 

[lighting77]

On the Home tab, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category box, click Date or Time, and then choose the number format that is closest in style to the one you want to create.

 

[Chris_010101]

I have a row from H8 to BC8 in this format, is there aw way vba code to convert it to dd-mm-yyyy

24/06/22, 00:00

24/06/22, 01:00

24/06/22, 02:00

24/06/22, 03:00

24/06/22, 04:00

24/06/22, 05:00

1. Highlight all the dates
2. Right-click
3. Format cells
4. Under "Category" choose "Date"
5. Under "Type" choose which type you want

If the right type doesn't exist:

1. Choose "Custom" under category
2. In the "Type" field, type how you would like the date to display

For example, typing: dd-mm-yyyy
Will convert the date value to look like: 01-01-2023

For example, typing: dddd, d mmmm yyyy
Will convert the date value to look like: Sunday, 1 January 2023

And so on

 

[vmjan02]

Hi Chris and Lighting i tried both of your methods but non is working out for me.
it is still giving me the sale result as 24/06/22, 00:00 no change.

 

[Chris_010101]

Hi Chris and Lighting i tried both of your methods but non is working out for me.
it is still giving me the sale result as 24/06/22, 00:00 no change.

I see, think I’ve had this before with a report produced from a HR system.

Can you upload the sheet via XL2BB and I can look at it

 

[vmjan02]

I see, think I’ve had this before with a report produced from a HR system.

Can you upload the sheet via XL2BB and I can look at it

Hi please find attached.

 

[Chris_010101]

Hi please find attached.

XL2BB - Excel Range to BBCode

The above is how to download and use XL2BB, can you upload your sheet using the above then I can work on a copy

 

[Alex Blakenburg]

The comma in your original post and the fact that formatting doesn't change it indicates it is text.

Try one of these.

20230619 DateTime Conversion vmjam02.xlsx
	A	B	C	D	E	F	G
1
2	Original Line (looks to be text) --->		24/06/22, 00:00	24/06/22, 01:00	24/06/22, 02:00	24/06/22, 03:00
3
4
5	Converted to Date Time --->		24-06-2022 00:00	24-06-2022 01:00	24-06-2022 02:00	24-06-2022 03:00
6	(Applied custom format of dd-mm-yyyy hh:mm)
7
8	Converted to Date Text --->		24-06-2022 00:00	24-06-2022 01:00	24-06-2022 02:00	24-06-2022 03:00
9
10
Sheet1

Cell Formulas
Range	Formula
C5:F5	C5	=--(SUBSTITUTE(C2,",",""))
C8:F8	C8	=TEXT(--(SUBSTITUTE(C2,",","")),"dd-mm-yyyy hh:mm")

 

[Akuini]

Another option, with vba:
This will remove the time part, and keep the date only.

Sub TO_DATE1()
Dim c As Range
For Each c In Range("H8:M8")
    If Len(c) > 7 Then c = CDate(Left(c, 8))
Next
Range("H8:BC8").NumberFormat = "dd-mm-yyyy"
End Sub

 

[vmjan02]

This is what i was looking for, thanks a ton Akuini
thanks a lot Alex your formula is also perfect, but needed a vba code.