understanding serial number for time and dates

  • Thread starter Thread starter zerocool
  • Start date Start date
Z

zerocool

when I put in the time and date for "12/3/2004 5:45:45 PM", i know that
excel sees it as a serial number " 38324.7401041667 ". That part i
get, but here is the part that throughs me for a lop. How do excel now
that i am refuring to the 12 month of the 3 day for the year 2004 at
the 5th hour and 45 mintes and 45 sec in the after noon? what does
each set of number repsent??

i just do not see how it tures the serial number into a time and date
and the other way around??

thanks for any help
Turbo
 
zerocool said:
when I put in the time and date for "12/3/2004 5:45:45 PM", i know that
excel sees it as a serial number " 38324.7401041667 ". That part i
get, but here is the part that throughs me for a lop. How do excel now
that i am refuring to the 12 month of the 3 day for the year 2004 at
the 5th hour and 45 mintes and 45 sec in the after noon? what does
each set of number repsent??

i just do not see how it tures the serial number into a time and date
and the other way around??

thanks for any help
Turbo
Click to expand...

That's why you have to type dates and times in a manner that Excel can
recognise. This means including the separators "/" or "-" and ":", or using
letters (as in 9 Dec 2003). If you were just to type 9122003, Excel would
not know that this is meant to be a date; it would simply be a number (9
million.....).

As for display, that's simply a matter of how the cell is formatted. Suppose
you type in the number 37964.525 and then format the cell as dd/mm/yyyy
hh:mm. You will see 09/12/2003 12:36. Change the formatting to General and
you will see 37964.525 again.
 
38324 days after 31 Dec 1899
0.7401041667 = 17/24 + 45/(24*60) + 45/(24*60*60) into the day

Jerry
 
Hi Turbo,

Serial number is just as it says, a serial number. It starts counting time
from midnight 1.1.1900. Each whole number represent one day and the decimal
part is a part of a day.

So 38324.7401041667, means that this is the 38324. day since 1.1.1900 and
the time is about 3/4 of the day (just before 6 pm).

Hope this helps,

Asser
 
(jerry)
thanks for breaking it down for me, i think i am starting to get th
time part. it is broken down into sec?!? I am just makeing sure tha
I am understanding the forumla.

but the dates are still throwing me for a lup. you are saying tha
38324 days after 31 Dec 1899 . I know that I can go into options an
changs the date format. how do i use that to me benfit?? what is th
differents if my wb date starts on 12/31/1899 or on 1904 date system.
their are to many options i do not know which one will work best fo
me?
 
Jerry W. Lewis said:
38324 days after 31 Dec 1899
Click to expand...

Well that's true, because Excel has an intentional bug written into it's
code. The year 1900 should be a leap year, but Lotus 1-2-3, an early
spreadsheet program, had a bug in it. For compatibility reasons the same bug
is intentionally written in Excel too.
 
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: 69-56-172-122.theplanet.com 69.56.172.122
Path: internal1.nntp.ash.giganews.com!border2.nntp.ash.giganews.com!border1.nntp.ash.giganews.com!firehose2!nntp4!intern1.nntp.aus1.giganews.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!newsfeed.tpinternet.pl!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Lines: 1
Xref: intern1.nntp.aus1.giganews.com microsoft.public.excel.misc:330560


oh ok it is all making since to me know. so you are saying that i soul
just leave it the way it is and not use 1904 date system and leave i
at 12.31.1899
 
Back
Top