formatting cells for dates

  • Thread starter Thread starter gordon_conroy
  • Start date Start date
G

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
 
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.
 
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).
 
If you can drop the seconds, why not right at the beginning:

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