Splitting dd/mm/yyyy hh:mm

[Guest]

I want to be able to sort (or us in a pivot table) the time portion of
dd/mm/yyyy hh:mm formatted cells. To get there, I 'm trying to split the
dd/mm/yyy hh:mm field into two cells. When I use Data Text to Columns, it
splits the data into three columns (date - time - AM/PM). While the Text to
Columns helps segregate the date portion, the time is still messed up.
2:00PM appears as 2:00AM and I can't figure out how to keep the correct AM/PM
indicator. Any help would be appreciated.

 

[Niek Otten]

Hi john,

=MOD(A1,1) gives you the fraction part of the date/time, which is the time. This has nothing to do with the way the time is
formatted and can be used for sorting without bothering about AM/PM

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I want to be able to sort (or us in a pivot table) the time portion of
| dd/mm/yyyy hh:mm formatted cells. To get there, I 'm trying to split the
| dd/mm/yyy hh:mm field into two cells. When I use Data Text to Columns, it
| splits the data into three columns (date - time - AM/PM). While the Text to
| Columns helps segregate the date portion, the time is still messed up.
| 2:00PM appears as 2:00AM and I can't figure out how to keep the correct AM/PM
| indicator. Any help would be appreciated.
| --
| John H

 

[John H]

Niek -
Thank you SO much!!! Excellent help!!