Splitting dd/mm/yyyy hh:mm

J

John H

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.
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Forcing a date entry format of dd/mm/yyyy 1
Date and Time Conversion 3
Extracting just the time portion 6
date and time difference 19
Working with Date/Time 1
Date Format 10
Date format problems after csv import 4

Top