formatting cells for dates

[gordon_conroy]

1041908351 is the numer of seconds of my record since 1970
using the formula = DATE(1970,1,1) + A1/86400
this converts it to 7/01/2003 2:59:11 AM -which is correct

if I select this cell and copy,then paste special values I get
7/01/2003 2:59:11 AM

what I want to do is to format the cell so just the 7/01/2003 is
returned.I know I can delete the 2:59:11 AM from the cell and this will
fix it,but I have a lot more cells to do


if I select format cell and use the date/custom of dd-mm-yyyy it
appears ok but when I double click on the cell it still appears as
7/01/2003 2:59:11 AM

Any help appreciated,the reason for all this is I want to get all the
records created on a particular date and graph them.As each one is done
at different time in the day it makes them all unique

 

[Norman Harker]

Hi Gordon!

You could use:

Tools > Options > Edit
Remove check from "Edit directly in cell"

That prevents your double click from revealing the time element but
I'm not sure if this achieves what you need.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[PegL]

You could also try using the DATE function instead of or
in addition to doing the paste values. =date(year
(a1),month(a1),day(a1).

 

[Dave Peterson]

If you can drop the seconds, why not right at the beginning:

=INT(DATE(1970,1,1) + A1/86400)