Removing the Time value from cells containing Date/Time values to leave only the date

[porkchop]

Hi,

This is just something I worked out rather than a question.
I had over 4000 rows of data that needed to be sorted by date. Problem was that they all had a time stamp - ie: 29/05/2008 7:10:17 AM - and reformatting the cell didn't fix it.

I inserted a column after the one I needed and used this formula:
=ROUND(A1-0.5,0)
Then just a quick copy/paste special/values to remove the formulas.

I am posting this here because it took me ages to find an answer for this problem.. the closest I could find before this was the formulas:

For days numbered 0-9 in the month
="0" & DAY(AP2) & "/" & MONTH(AP2) & "/" & YEAR(AP2)

and

For days numbered 10+ in the month
=DAY(AP2) & "/" & MONTH(AP2) & "/" & YEAR(AP2)


Hope this helps someone

Cheers

 

[Scott Huish]

If you just want to strip the time off:

=INT(AP2)

Format as date.

 

[porkchop]

****, that's even easier. Thank you


I suppose that saying is true, - that people don't know what they don't know - as I actually didn't realise a) that there was a function that would round down only; and b) that it's called the INT function.

I have tried to make the subject heading as close to google friendly as possible so that no one ends up spending as much time on this one as I did.

 

[Wgafaw]

Hey guys. Thank you so very much for this. I have to deal with about 6 big reports a day that have the date with a time stamp and I manually convert it to just time before uploading into my database. This is going to save me a lot of time.

Thanks Again,
Wgafaw

 

[porkchop]

Glad to have been of assistance to someone instead of being the helpee.