Tips on entering dates and times

Dates and times are numbers Microsoft Excel treats dates and times as numbers. The way that a time or date is displayed on a worksheet depends on the number format applied to the cell. When you type a date or time that Excel recognizes, the cell's format changes from the General number format to a built-in date or time format. By default, dates and times are right-aligned in a cell. If Excel cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell.

Control Panel Options you select in the Regional Settings of Control Panel determine the default format for the current date and time and the characters recognized as date and time separators — for example, the slash (/) and dash (-) for dates and the colon (:) for times on United States-based systems.

Use a 4-digit year When entering dates such as Decemeber 01, Excel matches the day first and then the year. For instance, December 01 is entered as December 1 of the present year, not as December of the year 2001.

Entering dates and times together To type a date and time in the same cell, separate the date and time with a space.

The 12-hour or 24-hour clock To type a time based on the 12-hour clock, type a space followed by AM or PM (or A or P) after the time. Otherwise, Excel bases the time on the 24-hour clock. For example, if you type 3:00 instead of 3:00 PM, the time is stored as 3:00 AM.

Viewing the serial number behind a date or time Regardless of the format used to display a date or time, Excel stores all dates as serial numbers and stores all times as decimal fractions. To display a date as a serial number or display a time as a fraction, select the cells that contain the date or time. On the Format menu, click Cells, click the Number tab, and then click General in the Category box.

Calculating with dates and times Times and dates can be added, subtracted, and included in other calculations. To use a date or time in a formula, enter the date or time as text enclosed in quotation marks. For example, the following formula would display a difference of 68:

="5/12/2004"-"3/5/2004"

The 1904 date system In Excel for Windows (and Lotus 1-2-3), days are numbered from the beginning of the century; the date serial number 1 corresponds to the date January 1, 1900. Excel for the Macintosh uses the 1904 date system; the date serial number 1 corresponds to January 2, 1904. To change the date system for use in calculations, click Options on the Tools menu, and then click the Calculation tab. Under Workbook options, select the 1904 date system check box. Use the 1904 date system for a workbook if you use that workbook with other workbooks that use the 1904 date system.

If you open in Excel for Windows a file created in Excel version 2.0 or later for the Macintosh, Excel recognizes the file format and automatically changes dates to the 1900 date system. Similarly, if you open an Excel for Windows file on a Macintosh, Excel changes dates to the 1904 date system.

DATEDIF Functions

DATEDIF worksheet function

Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3.

Syntax :
DATEDIF(start_date,end_date,unit)

Start_date is a date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).
For more information about date serial numbers, see NOW.

End_date is a date that represents the last, or ending, date of the period.
Unit is the type of information you want returned.
Unit Returns
"Y" The number of complete years in the period.
"M" The number of complete months in the period.
"D" The number of days in the period.
"MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored.
"YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored.
"YD" The difference between the days of start_date and end_date. The years of the dates are ignored.

Remarks :

Microsoft Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900.
Learn more about how Microsoft Excel stores dates and times.
Excel for Windows and Excel for the Macintosh use different date systems as their default.
For more information, see NOW.

Examples :

DATEDIF("2001/1/1","2003/1/1","Y") equals 2, or two complete years in the period.

DATEDIF("2001/6/1","2002/8/15","D") equals 440, or 440 days between June 1, 2001, and August 15, 2002.

DATEDIF("2001/6/1","2002/8/15","YD") equals 75, or 75 days between June 1 and August 15, ignoring the years of the dates.

DATEDIF("2001/6/1","2002/8/15","MD") equals 14, or the difference between 1 and 15 — the day of start_date and the day of end_date — ignoring the months and the years of the dates.

Excel Basics

Excel Basics: "Just before getting started, a quick look at the essential constituents of Excel to have a basic understanding of what does what.Worksheet Menu Bar

The Worksheet Menu Bar is the basic navigation tool of Excel. This is a single menu from which all the Excel options could be activated. You can control the whole of Excel with this bar. Clicking on each menu item triggers an action or an another menu, which is a sub-menu of the original menu. You could see an Excel icon(a small picture) on the left of the worksheet menu bar from which you can do a few things(Try it out). On the right side is the help box. If you have any doubts, you could type here to find it out. On the extreme right is the minimize,maximize and close buttons. You could also find a few buttons in the bar which you would not notice
when you excel. This is due to customization which you would learn later.

Toolbars
Toolbars are nothing but shortcuts of Worksheet Menu Bar. If you want excel to perform an action, you should navigate through the worksheet menu bar. Location a button in t"
Technorati Profile

Getting Started with Ms. Excel

This section deals with the basic ingredients of excel.
When you open Microsoft Excel, you would have see a view similar to the picture given below

These are the essential ingredients of Microsoft Excel and we can see them in detail

Title Bar
The title shows the file you are working with.Normally when you open Excel, Excel automatically opens a new workbook by the name Book 1
Worksheet Menu Bar

This menu contains all excel commands available to work with.
Help
As the name suggests, you could use this bar to get instant help
Toolbars

Just like the worksheet menu bar, the toolbars contain excel commands.There a lot of toolbars available in Excel and you could even create and customize them. The difference between Worksheet Menu Bar and toolbar is that toolbars contain commands that could be activated directly when clicked while you should naviagate through the worksheet menu bar to activate commands.In simple words, toolbars are shortcuts to commands available in worksheet menu bar.
Formula Bar

This bar is extremely useful when working with formulas.This bar shows the content of the cell that is selected
Status Bar

As the name suggests, this bar shows the status of Microsoft Excel.You could see this bar would provide with meaningful message if you do something in Excel.You could always see the word Ready when you start Excel.This indicates that excel is ready to work on.Likewise the status bar provides you direction when working
Research Pane
Similar to the help bar, you could use this pane to get more detailed help.It is really useful for beginners as it shows a list of recently opened files.
Scroll Bars
The two scroll bars, horizontal and vertical helps you in navigating through the worksheet
Sheet Tabs
These tabs helps you in navigating through different worksheets in the workbook
Rows,Columns and Cells

By far the most common terminology used in Excel is that of Rows, Columns and Cells
In Excel, rows and columns means the same as used in general. There are 65536 rows starting from 1 and 256 columns starting from A in Microsoft Excel 2003. Rows are horizontal and columns are vertical. The numbers 1,2,3 upto 65536 are called row headers. Similarly the letters A,B,C upto IV are called column headers.Cell is the singlemost part that can be referred to in Excel. Cell is also refererred as activecell(activecell refers to the cell currently selected).
Each cell has an identity and it is referenced using that identity.The identity is the row and cell in which it is intersected. For example if you select a cell in the first row corresponding to the first column, then the identity of the cell is A1.This is referred to as the address of the cell.
To know the address of the cell, just consider Excel a big graph with no negative coordinates. Just like in the graph, where x and y coordinates meet at a point, similar is the case in Excel. Thus a cell in the third row and the sixth column has a cell address of F3, one in the tenth row and the eight column has an address of H10 and so on.Whenever you select a cell the corresponding row and column headers are highlighted. Thus if you select the cell B7 then the row header 7 and the column header B are highlighted in a different color(usually orange). If you have any trouble about the address of the cell, you can simply check it out in the box to the left of formula bar.But knowing its basics is necessary to use it in formulas.
Range
When you select a single area in Excel, it is called cell. When you select more than a cell then it is called Range.Normally you would select a range by using the colon: symbol.Thus if you want to select 10 rows and 10 columns at once, then you first cell would be A1(first row,first column) and your last cell would be J10(tenth row,tenth column). This would be specified in excel as A1:J10. You would learn a lot about this under the head Functions.
Worksheet,Workbook and Workspace
These terms are also popular in Excel. If you open Excel you would always see a workbook called Book1. Workbook is a file containing the data entered by you. It is just like a file where you keep your papers. Workbook is sometimes referred to as active workbook(active workbook actually refers the workbook you are currently working with).
Worksheets are separate sheets inside the workbook. Just like a file has different papers in it, a workbook has different worksheets in it. You could navigate through the worksheets using the sheet tabs. You can have upto 255 worksheets in a workbook.
Workspace is a collection of workbooks. Just think it of a file holder holding different files. For example you could be working on a sales forecast for which you would have files from different sales departments. You could save these files as a workspace so that whenever you open a file you could open all the related workbooks.This saves you time from searching and opening different workbooks.