# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Remove Time on Date/Time Stamp

## pinoydarv

The current date/time stamp is below:

9/2/2009  8:59PM

I need to keep the date but remove the time stamp on the data. I have been using the formula below:

=MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)

But now it is giving me a !value error. Can anyone help? THanks!

----------


## shg

If it is indeed an Excel date (rather than text), =int(a1) is all you need.

----------


## teylyn

Hi,

shg's approach is surely the quickest and most elegant. But you're not far off from a solution with your approach. Instead of concatenating with the & sign, you can use the date function like this:

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

then format the cell to display the date the way you like it

----------


## pinoydarv

I am getting a #VALUE!


Is it because it's a text date?

 :Mad:

----------


## teylyn

> Is it because it's a text date?



Probably. Try =YEAR(A1) on its own. If that returns #Value, you're dealing with a text, not a date. Now you have two options: 

*extract the date from the text as a text*
=LEFT(A1,LEN(A1)-FIND(" ",A1)+2)

The result will still be text.

*convert the text into dates*
If the dates are written exactly as you did above, you only need to add a space before the PM and then format the cell as date. After that, you can use the function shg supplied.

try this: highlight the whole range of cells with this kind of date stamp, 
select Edit - Replace
search for "PM" and replace with " PM"  (not the space!, don't add the " marks!!)
hit Replace all
repeat for AM

now format your cells as dates and play on.....

----------


## Tracyd32

All I did was format the cells into a dd/mm/yyyy and it automatically removed the time.

Lots easier than using formulas!

 :Wink:

----------


## arthurbr

Tracyd32,

please read what the thread is about before posting wrong solutions.
Formatting only changes how the cell looks, not the underlying value.

This being said, I doubt if the OP has waited two years for your input.

----------


## concatch

Hi I had a similar problem and this is my solution:

1) Select Text to Column button on the data tab.
2) Select Fixed Width then next
3) Then hit next again
4) Under column data format select date and choose the format.
Then hit finish. 

The time will be separated from the date and placed in the adjacent column leaving the date.
I'm assuming the reason INT didn't work was because the data was text. The text is now transformed into a date. Very useful!

----------


## arthurbr

2 year old thread - Cosed

----------

