Excel and Keeping Track of Time and Dates 7 Popular Formulas

Microsoft Excel can be used in a lot of different ways. Among the most popular are storing data, doing mathematical calculations, and generating reports.

If your work with Excel involves tracking the change of meaningful data such as business performance (revenue/profits/units sold), weather temperatures, or money spent over time, you will want to use Excel s date and time functions.

Excel and Keeping Track of Time and Dates

Here are 7 of the most popular formulas and techniques for keeping track of time and dates in Excel:

1. Insert the current time: To insert the current time into any cell of a worksheet, use this formula:
=NOW()

2. Insert the current date: Similarly, to insert the current date into any cell, use this formulas:
=TODAY()

3. Format time and date values: For any situation whereby you will be displaying date and/or time information, you have a very large range of options of how that data is formatted for display. Specifically, the options vary along the dimensions of: a. time vs. date vs. time and date together; b. standard format for your region or country; c. degree of precision of the date or time being displayed.

To access all possible formatting options for your project:

a. Highlight the range of cells in question.

b. Press Ctrl + 1 to open the Format Cells dialog box.

c. From the Category list on left, choose Date or Time.

d. From the Type list on right, select the desired format.

4. Calculate the total elapsed time between two dates/times: For example, let s say you want to calculate the difference between these two dates/times: July 12, 2011 2:58 p.m. and August 18 2011 at 8:52 p.m.

Note: you have two options concerning how you display the result: showing the difference between the time portion only, or in terms of actual total time elapsed while taking the dates into account, as well.

In both cases, you would want to subtract the earlier date/time from the later date/time. So:

a. Enter the earlier time into cell A1 as 7/12/11 14:58.

b. Enter the later time into cell A2 as 8/18/11 20:52.

c. Now, into cell A3 enter this formula:
=(A2 A1)

Lastly, format cell A3 according to how you want the result displayed:

* If you want to display the elapsed time only (but ignoring the elapsed days in between the two dates), use this format found in the Type = Time formatting selection list: (h:mm:ss), which is displayed in the formatting selection list as: 1:30:55. (Your result in this case should be: 5:54:00, or 5 hours, 54 minutes).

* If you want to display the total elapsed time in a way that reflects the difference between the calendar days represented as hours as well as the time itself, use the ([h]:mm:ss) formula, which is displayed in the formatting selection list as: 37:30:55. (Your result should be: 893:54:00, which means 893 hours, 54 minutes).

5. Calculate the number of weeks between two dates: Start by entering the dates in question into two different cells and subtract the earlier date from the later date (see above example). Be sure to format the results cell as Category = General (or Number). Then, divide the result by 7. For example, if the dates in question are in cells A1 and A2, then in A3 enter this formula:
=(A2 A1)/7

6. AutoFill dates across a range of cells: If you have entered a certain day, month, or year into a cell, you can drag the contents of that cell across or down your spreadsheet by simply grabbing and dragging the cell by its fill handle (bottom, right square of a highlighted cell). Excel will automatically extend the times or dates incrementally, cell by cell.

7. Calculate the weekday: This one is pretty straightforward. Just enter the following formula into a cell, whereby A1 contains a date:
=WEEKDAY(A1)

The result will be a number from 1 to 7, whereby 1 = Sunday, 2 = Monday, etc.

As you can see, Excel is very versatile with its calculation and display of dates and times. Author Resource:- Get more FREE MS Excel tips at: http://www.Untangle-Your-Thoughts.com.