How can I "drop time"?

[Lythande]

Hey Board,

I have a spreadsheet that in column M each cell has a time-stamp like 2/27/09 7:21:00 PM and other such dates and times each cell down. The times are annoying as hell because I'm counting only by date (countif stuff) and the time being there messes things up. I see that this is a cell format time option instead of date, so I highlighted the column, clicked format, and opted for date as 27-Feb.

The time is gone in the cell, but the count still doesn't work. Why? When I click on the cell the **** time still appears in that bar at the top (to the right of Fx). How the heck can I get rid of that darn time stamp in there?

 

[barry houdini]

If you have a date/time in A1 this formula in b1 will extract the date only

=INT(A1)

 

[jbeaucaire]

In another cell on row 1 put this formula and copy it down:

=INT(M1)

Now format that cell as date and do your math on that column, or copy > edit > pastespecial > values that column back over the original column M to permanently remove the time data.

 

[jasonb75]

In answer to WHY??? When you change the cell format, you're only changing how the data in the cell is displayed, excel retains the original data in the background so that if you need to change it back to show the times again they will still be there.

How are the dates entered into the sheet? In most (if not all) cases it should be possible to correct this at the source without the need for extra columns.

 

[Lythande]

Thank you!

 

[siskomartel]

I come across this all the time at work. Another easy way to do this, format the field as date (without the time), open up notepad, copy and paste column data into notepad, copy and paste column data back into excel. Time data is gone from date.

Notepad can't hold hidden values, ends up very useful for these types of things (also copying tables into excel from other places).

 

[jbeaucaire]

I come across this all the time at work. Another easy way to do this, format the field as date (without the time), open up notepad, copy and paste column data into notepad, copy and paste column data back into excel. Time data is gone from date.

Notepad can't hold hidden values, ends up very useful for these types of things (also copying tables into excel from other places).

Sisko, you don't have to leave Excel to accomplish the same thing.

Highlight the cells currently displaying data the way you want.
Press CTRL-C to copy it
Click on EDIT > PASTE SPECIAL > VALUES
Formulas and hidden values gone.

 

[siskomartel]

Hi jbeaucaire,

I wish that was the case. Unfortunately, since the date/time is a value of whole number + decimal, when you paste values only, you still get the decimal. In order to get rid of the decimal, you'd have to run a trunc function, do some text-to-columns magic, or set up a macro. The easy way to do this is to go to notepad - notepad doesn't look at the actual value, it just takes what's displayed.

I also use this to remove hyperlinks - when you paste values the hyperlink still stays, but if you go to notepad and back, it goes away. Notepad is a lifesaver for these little workarounds.