Sorting dates

[HomePro]

I have a column on a sheet with date/ times I want to sort. But to do so I need to pad the day and the month. Or change the date format on the column. But it will not change.
Any idea why?

Appointment_General_Data.xlsx
	A
1	Added_Date
2	7/6/2022 8:55 AM
3	7/6/2022 9:02 AM
4	7/6/2022 9:03 AM
5	7/6/2022 9:09 AM
6	7/6/2022 10:26 AM
7	7/6/2022 12:18 PM
8	7/6/2022 12:26 PM
9	7/6/2022 12:43 PM
10	7/6/2022 1:33 PM
11	7/6/2022 2:26 PM
Sheet2

 

[johnnyL]

Not sure what you mean. It sorts just fine for me.

Book1
	A	B	C
1	Added_Date
2	7/6/22 2:26 PM	7/6/22 8:55 AM
3	7/6/22 8:55 AM	7/6/22 9:03 AM
4	7/16/22 9:02 AM	7/6/22 9:09 AM
5	7/6/22 9:03 AM	7/6/22 12:18 PM
6	7/6/22 12:26 PM	7/6/22 12:26 PM
7	7/6/22 9:09 AM	7/6/22 12:43 PM
8	7/26/22 10:26 AM	7/6/22 1:33 PM
9	7/6/22 12:18 PM	7/6/22 2:26 PM
10	7/6/22 12:43 PM	7/16/22 9:02 AM
11	7/6/22 1:33 PM	7/26/22 10:26 AM
12
Sheet1

 

[Joe4]

If changing the format of the column has no effect on the data, then that means that your data is probably entered as text, and not as dates.
If you select the column, go to the Data menu and select "Text to Columns", and then got to Step 3 and choose the "Date" option with the proper Date order (don't know if it is in MDY or DMY in your data), and then click "Finish", it should convert those entries to dates, and then you should be able to sort them.

 

[johnnyL]

VBA code to sort it:

    Sheets("Sheet2").Range("A1:A11").Sort Key1:=Sheets("Sheet2").Range("A1"), _
                Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers, Header:=xlYes

 

[jbklyman]

HomePro - be aware that dates are stored as date-encoded numbers in excel. So if your data was entered as a text value (to see what the cell is formatted as, from the Home tab - Number grouping - the drop down box will show you if the cell is General, Number, Date, Time, Custom, ect.) Try changing the selection types and see what happens to your cell values. This is where you will really see the difference between a Date value and a Number value or if it is string text.

 

[HomePro]

not sure why but I can not seem to identify / change the format in general / text/ date formatting and I can not sort it as Johnnyl did above.
What is my next step to work this out?

 

[johnnyL]

Did you try the code in Post #4?

 

[Joe4]

Did you try the steps I outlined in step 3?
Note that changing the the format of a column after the fact will NOT change the type of data that is located in that column (it will only format entries that are already valid numbers/dates - it will do nothing to text entries).
Doing a "Text to Columns" will re-convert the data and allow you to change the data type (from text to dates).

 

[HomePro]

yes i did. not sure why but seems that the spreadsheet works different than the converted xl2bb version

 

[Joe4]

yes i did. not sure why but seems that the spreadsheet works different than the converted xl2bb version

Can you post a print screen/image of your sheet, so we can see what it looks like?

Are some values left-justified int the cells and others right-justified?

It looks like your first value is in cell A2.
If you enter this formula in any blank cell on your sheet, what does it return?

=ISNUMBER(A2)