If we wish to use these real time values in other calculations there are a few 'magic'
numbers to keep in mind.

60 (sixty minutes)

60 (sixty seconds)

3600 (60 secs * 60 mins)

24 (twenty four hours)

1440 (60 mins * 24 hours)

86400 (24 hours * 60 mins * 60 secs)

Once we are armed with these magic numbers and the information above,
the manipulation of times and dates is no longer a problem. That is of course
providing your dates and/or times are valid.

Entering Valid Excel Dates & Times

To enter a valid date in Excel, use a slash mark or a hyphen to separate
the parts of a date; for example, type 9/5/2002 or 5-Sep-2002. If you leave off
the year portion (9/5), Excel will default it to the current year.

Tip: To enter the current date, press CTRL+; (semicolon)

To enter a time that is based on the 12-hour clock, type a space, and then type
"a" or "p" after the time; for example, 9:00 p. Otherwise, Excel enters the time
as AM. To Enter a time based on a 24-hour clock (military time) enter as 21:00,
11:00 etc.

Tip: To enter the current time, press CTRL+SHIFT+; (semicolon).

Adding/Subtracting Excel Dates & Times.

As Excel Dates & Timesare
seen as Serial Values (Dates) and Decimal Fractions (Times) we can easily add or
subtract them like below;

This will then add 1 (1 day) to A2 if the time in A1 is of a higher
value than the time in A2. The expression (A1>A2) will equate to either TRUE
or FALSE. TRUE has a value of 1 and FALSE a value of zero.

By default, excel cannot calculate or show true negative times, instead
you will see ############. This can be overcome by switching Excel to the 1904 Date
System via Tools>Options - Calculations and check the 1904
Date System checkbox. The 1904 date system changes the
Starting date from which all dates are calculated from January 1, 1900, to January
2, 1904. This is VERY IMPORTANT as, if you are using other Date calculations
in the same Workbook they will end up returning erroneous results.

Add/Subtract Months or Years to a Date

We can also easily tell Excel to add to any date any amount of days,
months or years. Here is how:

=DATE(YEAR(A1)+value1,MONTH(A1)+value2,DAY(A1)+value3)

So to add 1 month to a date in cell A1 we could use:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

to add 1 year to a date in cell A1 we could use:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

There are however some other Date and Time functions Excel has that
are part of the Analysis ToolPak Click Add-Ins on the Tools menu. Click to select
the Analysis ToolPak check box, and then click Yes if you are asked if you want
to install it.

You will then have functions such as:

EDATE

EOMONTH

NETWORKDAYS

WEEKNUM

All of these will be found under the Date & Time category of the Paste
Function dialog, Function Wizard. These are very easy to use and the help in Excel
explains these very well. I also have some examples
here: