Ok, I apologize profusely for my absence and making you wait for a reply. I am truly sorry but due to circumstances beyond my control I and family obligations I was delayed in replying to this query. Hopefully I will make up for it by giving you good advice.

Ok, here we go:

First, if you want to use real time values in your calculations you need to know the "magic" numbers to keep in mind. These are:

60 (Sixty Minutes)
60 (Sixty Seconds)
3600 (60 Secs * 60 Mins)
24 (Twenty Four Hours)
1440 ( 60 Mins * 24 Hours)
86400 (25 Hours * 60 Mins * 60 Secs)

Now that you know these numbers and the information above you should no longer have problems with the manipulation of time and dates in Excel. You must be certain that your times are valid though.

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.

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

[b:17cs4ccz]Adding/Subtracting Excel Dates & Times.[/b:17cs4ccz]
As Excel Dates & Times are seen as Serial Values (Dates) and Decimal Fractions (Times) we can easily add or subtract them like below;

=A1-A2

=A1+A2 or =SUM(A1:A2) See Also: Adding Excel Times Past 24 Hours

When subtracting dates we naturally want to subtract the lesser date from the greater date. If you are unsure which cell will house which you can use;

=MAX(A1:A2)-Min(A1:A2)

[b:17cs4ccz]Subtracting Times That Span Past Midnight Into a New Day[/b:17cs4ccz]

Let's say A1 hasthe Start time of 6:00PM and A2 the end time of 2:00AM. If we need to work out the hours worked between 6:00PM of 1 day and 2:00AM of the next day we cannot simply use;

=A2-A1

As Excel does not like negative times

Instead, we should use;

=A2+(A1>A2)-A1

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.

Add/Subtract Days to a Date

To add/subtract, say 2 days to a date, we can simply use;

=A1+2

=A1-2

Calculate the Difference Between 2 Dates

To find out the difference in 2 dates use:

=DATEDIF(A1,A2,"d")

Where A1 houses the earliest date. This will result in the number of days between 2 dates.

If, when working with dates and times we cannot know in advance which date or time is the earliest we can use the MIN and MAX functions, for example we could use:

=DATEDIF(MIN(A1:A2),MAX(A1:A2),"d")

The syntax for the DATEDIF function is;

DATEDIF(Start _Date,End_Date,Unit)

Valid Units are any one of the formats below

"M", "D", "Y", "YM" (The months and years of both dates are ignored) ,"YD" (The days and years of both dates are ignored) and "MD" (The years of both dates are ignored).

Convert From Decimal Time

If you have the number 5.50 and you really want 5:30 or 5:30AM use:

=A1/24 and format as needed.

If it should be 17:00 or 5:30PM use:

=(A1/24)+0.5

Convert To Decimal Time

To get the opposite, that is a decimal time from a true time, use

=A1*24

Extract Date Only From Date & Time

If a cell has both the true date and true time (e.g. 22/Jan/02 15:36) and we only want the Date, use:

=INT(A1)

Extract Time Only From Date & Time

To get the time only use:

=MOD(A1,1) and format as needed.

Negative Times

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.

I hope you find this information useful!