John Walkenbach's Favorite Excel 2010 Tips and Tricks by John Walkenbach

Author:John Walkenbach
Language: eng
Format: epub, pdf
Publisher: Wiley
Published: 2010-06-09T00:00:00+00:00

Figure 93-1: To allow sorting by pre-1900 dates, enter the year, month, and day into separate cells.

To sort the presidents by birthday, first do an ascending sort on column D, an ascending sort on column C, and, finally, an ascending sort on column B. The result is shown in Figure 93-2.

Unfortunately, you can’t perform other date-related operations for these pre-1900 dates. For example, you cannot perform subtraction to determine ages.

Figure 93-2: The presidents sorted by birthday, after performing three sorts.

I created an Excel add-in, XDATE, which contains a number of functions that make it possible to work with pre-1900 dates. You can download a free copy from my Web site: http://spreadsheetpage.com.

Tip 94: Working with Negative Time Values

Because Excel stores dates and times as numeric values, you can add or subtract one from the other. Figure 94-1 shows a simple example that tracks the number of hours worked. The total is calculated in cell B8 by using this formula and formatting the cell using the [h]:mm number format (see Tip 59 for details):

=SUM(B2:B6)

Cell B9 uses the following formula to calculate the amount of time required to make up a 40-hour work week:

=(40/24)-B8

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.