Converting NOW contents to TIME

[Phrank]

I have a worksheet that has a column where the time is entered (via
VBA) as NOW() and formatted as a time (this is the time the job is
submitted). All of the cells in this column show just a time (hh:mm),
but in the formula bar, it shows a date and time. I need to convert
this to simply a time. There is another column in which the operator
enters the time that they completed the job. This too is formatted as
hh:mm, but since the operator specifically enters the time, the number
in the cell is formatted as hh:mm and it's only a time in the formula
bar. I need both columns to simply show the time so I can do some
time calculations (the current layout yields an error message). How
do I make the initially entered (date and) time show up in the formula
bar AND the cell as just a time? Thanks.

Frank

 

[Rick Rothstein \(MVP - VB\)]

So I am guessing that both time values will occur within 24 hours of each
other and (probably) not across midnight. Why not change the VBA code to use
the Time() function instead of the Now() function... that would solve your
problem in the simplest manner.

Rick

 

[Phrank]

I have done that, and that will take care of any future entries;
however, there are about a 1000 lines with the time entered as Now().
So, in order to get my time analysis calculations to work, I need to
change this to show just time. Any ideas other than simply retyping
the times? Thanks for your time and help.

Frank

 

[Rick Rothstein \(MVP - VB\)]

Are those 1000 lines (rows, right?) contiguous throughout a single column?
If so, select those cells within the column that they are in and click
Data/TextToColumns in Excel's menu bar; then, on the dialog box that
appears, click the "Delimited" option button and then click the "Next"
button, put a check mark next to the "Space" checkbox and click the "Next"
button; choose the first column and click the "Date" option button, then
choose all other columns and click the "Do not import column (skip)" option
button for each of them; finally, click the "Finish" button. At this point,
you will probably have to change the selection's cell formatting to "Date"
to remove the "00:00" that might be left over from the original formatting
that Excel gave to the value. That should do it (assuming you can select
contiguous ranges of cells with a column).

Rick