| Microsoft Excel can be used in a lot of different | | | | time into cell A1 as 7/12/11 14:58b. Enter the later |
| ways. Among the most popular are storing data, | | | | time into cell A2 as 8/18/11 20:52c. Now, into cell |
| doing mathematical calculations, and generating | | | | A3 enter this formula: =(A2-A1) |
| reports. If your work with Excel involves tracking | | | | Lastly, format cell A3 according to how you want |
| the change of meaningful data such as business | | | | the result displayed: |
| performance (revenue/profits/units sold), weather | | | | * If you want to display the elapsed time only |
| temperatures, or money spent over time, you will | | | | (but ignoring the elapsed days in between the two |
| want to use Excel's date and time functions. | | | | dates), use this format found in the Type = Time |
| Excel and Keeping Track of Time and Dates | | | | formatting selection list: (h:mm:ss), which is |
| Here are 7 of the most popular formulas and | | | | displayed in the formatting selection list as: 1:30:55. |
| techniques for keeping track of time and dates in | | | | (Your result in this case should be: 5:54:00, or 5 |
| Excel: | | | | hours, 54 minutes). |
| 1. Insert the current time: To insert the current | | | | * If you want to display the total elapsed time in |
| time into any cell of a worksheet, use this | | | | a way that reflects the difference between the |
| formula: =NOW() | | | | calendar days represented as hours as well as the |
| 2. Insert the current date: Similarly, to insert the | | | | time itself, use the ([h]:mm:ss) formula, which is |
| current date into any cell, use this formulas: | | | | displayed in the formatting selection list as: |
| =TODAY() | | | | 37:30:55. (Your result should be: 893:54:00, which |
| 3. Format time and date values: For any situation | | | | means 893 hours, 54 minutes). |
| whereby you will be displaying date and/or time | | | | 5. Calculate the number of weeks between two |
| information, you have a very large range of | | | | dates: Start by entering the dates in question into |
| options of how that data is formatted for display. | | | | two different cells and subtract the earlier date |
| Specifically, the options vary along the dimensions | | | | from the later date (see above example). Be sure |
| of: a. time vs. date vs. time and date together; b. | | | | to format the results cell as Category = General |
| standard format for your region or country; c. | | | | (or Number). Then, divide the result by 7. For |
| degree of precision of the date or time being | | | | example, if the dates in question are in cells A1 |
| displayed. | | | | and A2, then in A3 enter this formula:=(A2-A1)/7 |
| To access all possible formatting options for your | | | | 6. AutoFill dates across a range of cells: If you |
| project:a. Highlight the range of cells in questionb. | | | | have entered a certain day, month, or year into a |
| Press Ctrl + 1 to open the Format Cells dialog | | | | cell, you can drag the contents of that cell across |
| boxc. From the Category list on left, choose Date | | | | or down your spreadsheet by simply grabbing and |
| or Timed. From the Type list on right, select the | | | | dragging the cell by its "fill handle" (bottom, right |
| desired format | | | | square of a highlighted cell). Excel will automatically |
| 4. Calculate the total elapsed time between two | | | | extend the times or dates incrementally, cell by |
| dates/times: For example, let's say you want to | | | | cell. |
| calculate the difference between these two dates | | | | 7. Calculate the weekday: This one is pretty |
| times: July 12, 2011 2:58 p.m. and August 18 2011 | | | | straightforward. Just enter the following formula |
| at 8:52 p.m. Note: you have two options | | | | into a cell, whereby A1 contains a |
| concerning how you display the result: showing | | | | date:=WEEKDAY(A1) |
| the difference between the time portion only, or | | | | The result will be a number from 1 to 7, whereby |
| in terms of actual total time elapsed while taking | | | | 1 = Sunday, 2 = Monday, etc. As you can see, |
| the dates into account, as well. In both cases, you | | | | Excel is very versatile with its calculation and |
| would want to subtract the earlier date/time | | | | display of dates and times. |
| from the later date/time. So:a. Enter the earlier | | | | |