add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
How Excel Calculates Date and Time
Excel handles dates and times numerically. The numbers for dates range from 1 to 2958465. The number 1 indicates the date January 1, 1900, and the number 2958465 indicates the date December 31, 9999.
Enter any number in a cell, press Ctrl+Shift+# and see the date for the number you entered.
Because of this numeric method, you can treat dates as values and perform calculations such as subtraction or totaling dates. The results of subtracting an earlier date from a later date are displayed as a number, which (as it happens) is also the number of days between the two dates.
Time, on the other hand, ranges from 0 to 1 and indicates precise time intervals of seconds:
Midnight = 0, noon = 0.5, and 14:09:03 = 0.589618.
Entering the Date in a Cell
Excel recognizes a number as a date by the date format, which uses a slash (/) as a separator.
An example of the date format in Excel: 7/25/2001.
Some users prefer to use a period (.) as a separator, instead of a slash (/).
If you are one of those people and would like to change the default setting for the date format, perform the following steps: From Windows, choose Start, Settings, Control Panel, Regional Options.
Select the Date tab. In the Date separator box, change the slash (/) to a period (.). Click Apply and OK.
Choose a Date Format with a Different Structure
In the Format Cells dialog box (Ctrl+1), the Number tab shows date formats with a period as a separator instead of a slash.
New
In 2002
Easily entering the date in a cell
Use the slash separator (/) on the right side of the keyboard above the number 8 to enter the date. Using this key ensures the correct date format.
Tip – Did you enter a date in a cell, but the cell is formatted as a number?
Press Ctrl+Shift+#, and change the formatting to a date format.
Shortcuts for entering current date and time
To enter the current date in a cell, press Ctrl+;.
To enter the current time in a cell, press Ctrl+Shift+;.
Typing dates in cells quickly
Typing a large amount of data in cells in the sheet can be tiring if it includes a series of dates. Minimize the work by typing the day of the current month and adding the following formula to insert the month and year.
=DATE(YEAR(TODAY()),MONTH(TODAY()),A1)
Type a full number in the cell and change it to a date by using the formula below. For example, type 10222002. The result is 10/22/2002.
=DATEVALUE(LEFT(A1,2)&”/”&MID(A1,3,2)&”/”RIGHT(A1,4))
Date and time – displaying the number behind the format
Press Ctrl+~. This shortcut can also be used to display the syntax of formulas in cells.
Press Ctrl+~ again to restore the sheet to its normal appearance.
To change a date or time to a number, press Ctrl+Shift+~ (be careful, as this change is permanent).
New
In 2002
Entering Dates Automatically
The Smart Tag lets you enter a series of dates automatically. You can enter an ascending series according to days, months and years.
1. Select Cell A1, and press Ctrl+;.
2. Click the Fill handle at the bottom right edge of Cell A1, and drag it to several cells in the column.
Do not cancel the selection of the range of dates. Excel creates a series according to days.
3. Click the arrow in the Smart Tag.
4. Select Fill Months.
Using the series dialog box to enter a series of dates
1. Select Cell A1, and press Ctrl+;.
2. Select Cells A1 through A10.
3. From the Edit menu, select Fill and then Series.
4. In the Series dialog box, select Date and Day.
5. Click OK.
Using the shortcut menu to enter a series of dates
1. Select Cell A1, and press Ctrl+;.
2. Select the Fill handle in the bottom right corner of the cell. The cursor changes to a plus sign (+).
3. Right-click.
4. Drag vertically, and release the mouse.
5. From the shortcut menu, select the series you need.
Automatically Entering Time Data
Entering a time series in intervals of one minute
1. Select Cell A1, and press Ctrl+Shift+;.
2. Select Cells A1 through A10.
3. From the Edit menu, choose Fill, Series.
4. In the Series dialog box under Type, select Linear, and in the Step value box, enter 0.000694.
5. Click OK.
Entering a time series in intervals of one hour
1. Select Cell A1, and press Ctrl+Shift+;.
2. Click the Fill handle in the lower right-hand corner of Cell A1.
3. Drag and release the mouse button.
Custom Dates
Customizing the date
1. Select Cell A1 in the sheet, and press Ctrl+;.
2. Select Cell A1, and press Ctrl+1. Select the Number tab, and then select Custom.
3. Clear the Type box.
In the dialog box, note the sample that appears above the Type box. Enter the date format into the box, according to the list of symbols in the date format table.
Date format table
m
Month. The number of the month, without 0 if the number is lower than 10.
mm
Month. The number of the month, including 0 if the number is lower than 10.
mmm
Month. The first three letters of the name of the month are displayed.
mmmm
Month. The full name of the month is displayed.
d
Day. The number of the day of the month, without a 0 if the number is lower than 10.
dd
Day. The number of the day of the month, including 0 if the number is lower than 10.
ddd
Day, as three-character text. For example, Thursday is displayed as Thu.
dddd
Day, as complete text. For example, Thursday is displayed as Thursday.
yy or y
Year. Two digits. For example, 1997 is displayed as 97.
yyy or yyyy
Year. The full number of the year is displayed. For example, 1997 is displayed as 1997.
Date – Formulas and Calculations
To select a date formula, select Insert and then Function. In the Paste Function box, select Date & Time.
Note
It is important to install the add-in Analysis ToolPak. The add-in contains many formulas for calculating dates.
To install the Add-In, select Add-Ins from the Tools menu. Then select Analysis ToolPak, and click OK.
Calculating the difference between dates
Use the formula DATEDIF to calculate the difference between dates. The results of the calculation are displayed as days, full months, and full years. The formula is not located in Paste Function in the Date & Time category. You must enter the formula manually.
Calculating the number of the week
To calculate the number of a week, use the Weeknum function. This function is included in the Analysis ToolPak add-in.
Calculating a quarter number
This section, including the figure below, explains how to calculate the quarter of a calendar year and of fiscal years that begin in July or October.
To calculate a quarter for a calendar year, see the syntax of the formula for Cell B2 as shown in Cell B15.
To calculate a quarter for a fiscal year that begins in October, see the syntax of the formula for Cell B2 as shown in Cell B17.
To calculate a quarter for a fiscal year that begins in July, see the syntax of the formula for Cell D2 as shown in Cell B19.
The formula INT rounds the result of the calculation to a whole number, which represents the quarter.
Inserting a function for calculating the number of a quarter in VBA
For an explanation of the technique for creating a customized function and inserting it into the Excel Paste Functions Wizard, see Chapter 7, Formulas.
Use the DatePart VBA function.
Custom Formatting for Time
Time values exceeding 24 hours
The serial value for time, as explained at the beginning of the chapter, is between 0 and 1.
The time format for a full 24-hour day is HH:MM:SS.
Example: the time 14 hours and 56 minutes is displayed as 14:56:00.
Problem
The default time format does not allow a time value to exceed 24 hours. In a cell, type a value larger than 24 hours, such as 28:56:00. The result is 04:56:00 (the number minus 24 hours).
Solution
Change the format of the cell, and place brackets around the hour. The format is displayed as [HH]:MM:SS, and the result is displayed as 28:56:00.
Time format table
h
Hour. The number of hours, without 0 if the number is lower than 10.
hh
Hour. The number of hours, including 0 if the number is lower than 10.
m
Minute. The number of minutes, without 0 if the number is lower than 10.
mm
Minute. The number of minutes, including 0 if the number is lower than 10.
s
Second. One digit per second, without 0 if the number of seconds is lower than 10.
ss
Second. The number of seconds, including 0 if the number is lower than 10.
[ ]
Brackets around the hour, to display a time value that exceeds 24 hours.
Time – Formulas and Calculations
See the formulas for calculating time in the Date & Time section of the Paste Function box.
Converting hours to decimals
Use the formulas HOUR and MINUTE to convert a time value to a decimal.
Calculating the difference between hours
Calculate an employee’s working hours. In the figure below, notice the format in Cells E4:E8 is hh:mm. The number 1 in the formula IF represents a time value that exceeds 24 hours in a day.
See line 5 in the example. An employee named Mark arrived at work at 23:00 in the evening and finished work at 7:00 the next morning. The result is calculated as 8:00 working hours.
Rounding hours up
In the figure below, note the use of the CEILING formula for rounding working hours up. The number 0.04167 is a decimal value of 1/24.
Converting a number to a time value
Excel’s ability to convert numbers to time values means you can enter a time value quickly by typing four digits.
Example: Type 2330 for 23:30 in column A. Type the formula shown in row 9 below in column B. Format the cells in column B with the format hh:mm.
Calculating time differences between regions of the world
The format in the formula cells is [hh]:mm:ss.