How do I remove the time from a date field ? In Excel, the date is displayed
as follows : 10/30/2007 2:34:24 PM

I need to remove the time from this value. I have tried to format the cell
with the proper date format (ie - mm/dd/yyyy) but the time still remained.

Any help would be GREATLY appreciated.

Thanks!
Rob

This worked for me with my Windows USA date settings (mdy order):

Select the range to fix:
Edit|replace
what: _* (spacebar followed by an asterisk)
with: (leave blank)
replace all

And format the range to show only the date (mm/dd/yyyy for example).

Rob wrote:

How do I remove the time from a date field ? In Excel, the date is displayed
as follows : 10/30/2007 2:34:24 PM

I need to remove the time from this value. I have tried to format the cell
with the proper date format (ie - mm/dd/yyyy) but the time still remained.

Any help would be GREATLY appreciated.

Thanks!
Rob

--

Dave Peterson

On Mar 5, 12:25*pm, Dave Peterson wrote:
This worked for me with my Windows USA date settings (mdy order):

Select the range to fix:
Edit|replace
what: _* *(spacebar followed by an asterisk)
with: (leave blank)
replace all

And format the range to show only the date (mm/dd/yyyy for example).

Rob wrote:

How do I remove the time from a date field ? *In Excel, the date is displayed
as follows : *10/30/2007 *2:34:24 PM

I need to remove the time from this value. *I have tried to format the cell
with the proper date format (ie - mm/dd/yyyy) but the time still remained.

Any help would be GREATLY appreciated.

Thanks!
Rob

--

Dave Peterson

Also you can just use this formula: =DATEVALUE(LEFT(A1,10)) and format
the cell as date.

If you have a real Excel date/time number in the cell, mm/dd/yyyy will show
10/30/2007. If you have the date/time as text, mm/dd/yyyy will have no
effect.
10/30/2007 2:34:24 PM is 39385.6072222222 which is Excel date/time number.
You can see this by pressing Ctrl+` (Ctrl+ the key above the tab key). If
you see 10/30/2007 2:34:24 PM instead then you have text in the cell not an
Excel date/time number. Press Ctrl+` to return to normal display. The
integer part of the number is the date and the fractional part is the time.
To get rid of the time completely, put =INT(Your_Time_Cell) in an empty
cell.

Tyro

"Rob" wrote in message
...
How do I remove the time from a date field ? In Excel, the date is
displayed
as follows : 10/30/2007 2:34:24 PM

I need to remove the time from this value. I have tried to format the
cell
with the proper date format (ie - mm/dd/yyyy) but the time still remained.

Any help would be GREATLY appreciated.

Thanks!
Rob