remove time from date field (Excel 2003 / SP2)

Topic

New Reply

Monday, Monday! I can’t for the life of me remember how to remove the time value out of a field to be left with the date only. I don’t want to change the formatting, I actually want the date only because the time is making my calculations go haywire that I want based on date only. Help me!
6/1/2007 3:38:00 AM

Reply | Quote

Replies

Say you have a date/time in A1. You could put this formula in B1: =INT(A1) and format it as a date, then use the value in column B.

Or you can run the following macro (select some cells before running it):

Sub ConvertDates()
Dim oCell As Range
For Each oCell In Selection
oCell = Int(oCell)
Next oCell
Selection.NumberFormat = “mm/dd/yyyy”
End Sub

Reply | Quote

So are you actually some great ancient superhero or is that just in my imagination?!?!?!?!? Many thanks yet again!

Reply | Quote

I’m ancient but not a superhero

Reply | Quote

A bit late but you could also do a find/replace:
Find: ‘ *’ (space followed by asterisk, without the quotes)
Replace: leave blank.
Select ‘Replace All’

Reply | Quote