 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
Mastering Dates in Excel
|
|
Dates give a lot of people problems in Excel. Dates are actually stored as the number of days since January 1, 1900. Here is a cool shortcut: Hit Ctrl+; to enter today's date in cell A1. 
You can create formulas for dates. The formula =A1+7 will find a date 7 days from today.
Here is one of the frustrations about dates. Sometimes if you enter a formula that should result in a date, Excel will show the answer as a number.
Use the Format - Cells dialog to format the cells as dates. There are many different date formats from which to choose.
The result:
Here is a cool trick for entering the days of the work week. Enter a date in a cell. Right-click the fill handle of the cell, drag, release the mouse, and choose Fill Weekdays.
Result - only Monday through Fridays are filled in.
Another Trick with the Fill Handle. Right-click the fill handle. Drag. Release. Choose Fill Series.
Time Sheets
There is a subtle problem when you are using a time sheet. If you attempt to total up times exceeding 24 hours, the answer will be wrong. This is because Excel only shows you the times in excess of a complete day.
To correct this, select Format - Cells. Choose the Custom number format. Instead of a format like h:mm, put the h in square brackets: [h]:mm
SummaryExcel's date calculations can be great, if you know the tricks and tips shown above. To get a thorough understanding of Excel, check out the self-paced training CD - Join the Excellers League. For the BEST TV show on technology, check out Call for Help. This tip was originally published on October 27, 2004. The permanent URL for this page is http://www.mrexcel.com/tip080.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
|
