Hi Tim
try
=INT(cell_reference)
and format as date

--
Regards
Frank Kabel
Frankfurt, Germany

Tim wrote:
If a cell says 2.8 for instance and i format that cell to be a date
with format 03/04/97and it comes out showing in the cell 01/03/00 but
the formula and value actually are 01/03/1900 12:05 . How do i make
it so the time is not there( 12:05)

Tried paste special value. did not work

Hi Tim
in which code ? you haven't provided any :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Tim wrote:
Write this in the code frank, i'm not to sure as a beginner.

I aws not sure what to do with the info you gave me, if that was a option on the toolbar or was i supose to place this in one of my macros somehow.

Hi Tim
if you post the code of yur existing macro I'll incorporate the
function for you. Though in VBA you have to use
CInt(value) instead of INT (INT is a worksheet function)

--
Regards
Frank Kabel
Frankfurt, Germany

Tim wrote:
I aws not sure what to do with the info you gave me, if that was a
option on the toolbar or was i supose to place this in one of my
macros somehow.

Hi
without changing your code in total just add the following lines:
1. At the beginning:
Dim cell as range
dim rng as range

2. And the following before you format the cells
Set rng = RANGE(Cells(1,2),Cells(Rows.Count, "B").End(xlUp))
for each cell in rng
if isnumeric(cell.value) then
cell.value = CInt(cell.value)
end if
next
'do your formating

One note: you probably should replace all Selects with a range
reference (as shown above).


--
Regards
Frank Kabel
Frankfurt, Germany

Tim wrote:
Thanks Frank, the part of the code that formats is at the bottom but
it leaves in that time factor that i talked about.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 2/24/2004 by Husky User
'
Dim z As Range
Sheets("Eclipse").Select
Range("A1").Select
Cells.Find(What:="time", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _ :=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _ False).Activate

Set z = ActiveCell
Cells(z.Row + 3, "B").Select

x = ActiveCell.Address
Selection.End(xlDown).Select
y = ActiveCell.Address
Range(x, y).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Sheet1").Select
Range("B2").Select
ActiveSheet.Paste
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(cLastRow + 1, "B").Select
ActiveSheet.Paste
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(cLastRow + 1, "B").Select
ActiveSheet.Paste

Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(cLastRow + 1, "B").Select
ActiveSheet.Paste
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(cLastRow + 1, "B").Select
ActiveSheet.Paste
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(cLastRow + 1, "B").Select
ActiveSheet.Paste
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(cLastRow + 1, "B").Select
ActiveSheet.Paste

Sheets("Sheet2").Select
Columns("B:B").Select
Selection.NumberFormat = "mm/dd/yy"

Sheets("Sheet1").Select
Columns("B:B").Select
Selection.NumberFormat = "mm/dd/yy"


End Sub