Leading the way in Microsoft Office Development

Excel

Volatile Functions

The NOW() and the TODAY() are examples of volatile functions.

A volatile function is a function whose value is subject to change every time it is calculated.

Every time these functions are calculated the value returned could be different, this is equivalent to manually making a change in the workbook and you will be prompted to save the changes.

Adding and Subtracting Times

You can add and subtract times, although there are complications - what are they ??

Automatic Number Formatting

If you create a formula that refers to a cell formatted as a date or time, then the number format of the cell containing the formula will be automatically adjusted to a date or time format.

Negative Dates and Times

If the result of a date or time calculation is negative then the cell will be displayed as "#####". You can often work around this by converting the result to elapsed hours. Multiply the result by 24 and display it with a number format as opposed to date or time.

A time without a date has a date serial number of 0 and therefore a negative time results in a negative serial number which is not allowed.

It is important to remember that any negative times are displayed as "#########"

It is possible to subtract times that cross midnight, for example 10pm to 3am.

You can use the MOD() function "=MOD(latesttime - earlier time, 1)

The functions and formulas convert every date and time to serial values, which can then be added or subtracted before being converted back into a recognisable date or time.

Subtracting one date from another involves subtracting one serial value from another and then converting the result back into a date.

Displaying total hours greater than 24

If you are adding up hours and minutes the number will never exceed 24 unless you use the following custom number format "[h]:mm".

How can I format times greater than 24 hours to display the full number of hours? - [h]:mm:ss (or enter it as a custom format).

If you are entering times into cells you can use the custom format 00";"00 to save you entering the colon every time. If you only want the hour displayed as single digit then use 0":"00.

Searching for Dates

It is possible to use the (Edit > Find) dialog box to search for particular dates.

You must however enter the date in the correct format. The only correct date format that can be used is your "short" date style.