How can I use the format function to change the stored value of a cell in excel?

[jbondsr]

How can I use the format function to change the stored value of a cel
in excel?

For example, In a colum I have data that is displayed like this:
19:59 12/31/69

But the actual stored data when you go to edit the cell is like this:
12/31/1969 7:59:00 PM

I want the stored data to be like the displayed value so I can sort th
column by the time and not the day.

By the way, I do not know how to do macros or use VB.:

 

[Frank Kabel]

Hi
you can search by date and time. No problem with this display. what are
you exactly looking for

 

[jbondsr]

You see, the problem is that since the stored data in each cell consists
of the date first before the time, if i click sort, it will sort the
data in the collumn according to the day. If I wanted to sort by the
time in each cell, I'd have to manually go into each cell and move the
time to be in front of the day.

I was wondering if there was I way I could have excel automatically
move around the data in the cell so that the time is first in the cell,
then the date after it. Changing the display format of the cells in the
column doesn't help because it doesn't change the stored data in the
cell, and I need it to be changed in order to sort it the way I want
it.

 

[Frank Kabel]

Hi
now I understand
The only way to solve this would be using a helper column. enter the
following formula in B1 for example:
=MOD(A1,1)
and copy this down for all rows (and format the cells as time)

Now sort with this column
Changing the format won't help

 

[jbondsr]

Ok.
Thanks.
I got the first part to work, but now when I try to sort the column, i
won't sort

 

[Frank Kabel]

Hi
it should work
Have you selected both columns? And also what appears in column B