Topic: Project Management Tips & Techniques

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers

In this article we’ll examine how Excel keeps track of Dates and Times.
Dates and times are simply numbers to excel, ranging from 1, which represents
1/1/1900, to 2,958,465, which represents 12/31/9999. Actually, 0 is also valid
for a date, and represents 1/0/1900, or 12/31/1899. (1/0/1900 is the day before
1/1/900, which is also 12/31/1899). Strangely, however, Excel won’t accept
12/31/1899, yet it does accept 1/0/1900! So any number in that range, formatted
as a date, will be treated as a date. If today were June 1, 2003, that date
would be stored internally as 37773, because it’s 37,773 days after January
0, 1900.

The number 37773 is called the serial number (when it’s used as a date).
So Excel can easily subtract dates. If you were born on May 6, 1973, for example,
put 5/6/73 in a cell, say A1, and in cell A2 you can put the formula =TODAY()-A1.
You would see 10965, meaning you’re 10,965 days old. (The TODAY() function
returns today’s date).

What? You tried it and got 1/7/1930 0:00? Ah, Excel formatted the cell for
you! If you simply use Format/Cells and click on General (in the Number tab),
you’ll see the 10965. Yes, I think Excel should be “smart” enough
to see you’re subtracting dates, and should therefore not format the
cell, but it does!

So what’s 45 days from now? =TODAY()+45. 30 days ago? =TODAY()-30. Pretty
easy stuff.

OK, so how does Excel keep track of times? Noon is stored as 0.5. Half a day.
6PM is .75; 6AM is .25. As I’m writing this article, it’s 11:12:48PM.
Excel stores that number as 0.967222222222222, but formats it as a time. But
you have to enter it as a time. Excel has specific formats for times, and 11:12:48PM
isn’t one of them. A space is needed before the “P”. Enter
11:12:48PM and you’ll see it’s left-aligned in the cell, indicating
text. Put that space in and you’ll see it’s right-aligned, indicating
numeric. The colons are required as well. If you leave off the PM or AM, Excel
will assume military time, or 24-hour time. So if you enter 11:12:48 Excel
will put in the AM for you! If you enter 23:12:48 you will see 23:12:48 in
the cell, but in the formula bar you’ll see 11:12:48 PM.

In addition to the =TODAY() function, there’s the =NOW()
function. This returns today and the time. If you enter =NOW() in a cell, you’d
see something like 5/14/2003 23:17 displayed. So there are some assumptions,
like leaving off the seconds, and displaying in military time. All this can
be re-formatted by Format/Cells and choose from among the date and time categories,
then choose from the Type column:

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers

The Project Management Tip of the Day is sponsored by Wiley Publishers