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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

"jbondsr >" <<(E-Mail Removed)> schrieb im
Newsbeitrag news:(E-Mail Removed)...
> How can I use the format function to change the stored value of a
cell
> 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
the
> column by the time and not the day.
>
> By the way, I do not know how to do macros or use VB.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

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.


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany

"jbondsr >" <<(E-Mail Removed)> schrieb im
Newsbeitrag news:(E-Mail Removed)...
> 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.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

"jbondsr >" <<(E-Mail Removed)> schrieb im
Newsbeitrag news:(E-Mail Removed)...
> Ok.
> Thanks.
> I got the first part to work, but now when I try to sort the column,
it
> won't sort.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>