I have the following problem.
I use a macro to sort time values. But when I copy paste the time from a sharepoint list. It's written in the cell as for example "14:30". When I type the time myself in the cell. it's written als 14:30:00.
When I sort the times with different notations. Everything with two zeros comes before everything without zeros.
How can I make sure all the cells have the same notation when sorting?
The cell format will not have any impact on the sort order. It is much more likely that the pasted value is text and not a real time value.
To test that, try to change the format for the apparent time value to General. If it is a true time value, it will be a decimal number less than 1, like 0.25 for 6 am.
To sort by time, all values must be time. If they are not, text values will appear after the time values, hence all your real, manually typed time values come first.
You can convert all text to time values like this: select the column, click Data > Text to Columns > Next > Next. In Step 3 of the wizard specify that the data is date (yes, date) and hit OK. Now all values are time values. This can be done with VBA, of course. Just fire up the macro recorder before you convert and look at the code.
