Remove Time from Date

R

Rob

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
 
D

Dave Peterson

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).
 
T

Tyro

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
 
D

Dave F

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).
Click to expand...

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