Home —> Downloads —> White Papers —> Time Management Using Excel

Time Management Using Excel

This article is also available as a PDF, timemangementusingexcel.pdf, or as a Zipped Word Document, timemangementusingexcel_doc.zip. The Excel template is here: exceltemplates.zip.

JSM Software is dedicated to Total Quality Management (TQM). This includes the mundane details of keeping track of the time spend on a project. Major (1999) wrote, "You need to measure what you are doing to know if you are improving." Numerous vendors offer a variety of special purpose tools to track your time. Instead of using a special-purpose time management tool this paper discusses a different approach. Excel provides many of the tools needed to manage your time effectively. Most people have Excel on their PC. There is a version of Excel for the PocketPC.

Covey (1990) cited Goethe, "Things which matter most must never be at the mercy of things which matter least." (p. 146) Keeping track of your time must not be an onerous task that takes a lot of time. Schmidt, Kiemele, and Cheek (no date) cited Vince Lombardi, "If we're not keeping score, we're only practicing." If the tool is complicate to use, then no one will use the tool. Obviously, a pencil and a piece of paper is the simplest time tacking method. The biggest detriment to using just a pencil and paper is that at the end of the day you only have the pieces of paper. Doing an analysis over time of how your proposed schedule compared to the actual events is difficult without having a soft copy of the data.

Schmidt, Kiemele, and Cheek (no date) wrote, "First, a manager must learn where his/her people are spending their time, and then develop a way to increase the amount of time spent on fire prevention (quality improvement) and decrease the amount of time spent on fire fighting (waste management)." Even more importantly, a manager must know how he or she is spending his or her time. This paper describes a simple method of keeping track of time that takes advantages of the keyboard shortcuts in Excel.

Excel has a number of shortcut keys. Two important shortcuts allow entering the current date and the current time. Pressing the control key plus the semicolon enters the current date into the cell. Holding down the control and the shift key, then pressing the semicolon (colon) key, enters the current time into the cell.� This shortcut key makes time management in Excel simple and easy. The table below summarizes the keyboard shortcuts this article discusses.

Keyboard Short Cut	Function
Control + semicolon (";")	Enter the current date into the cell.
Control + shift + semicolon/colon (":")	Enter the current time into the cell.

The table below shows an Excel spreadsheet used for time management.

	A	B	C	D	E	F
1	Diary
2	Start	End	Task Description	Person	Account	Elapsed
3						0
4	0:00					0
5	0:00					0
6	0:00					0
7	0:00					0
8	0:00					0
9	0:00					0
10	0:00					0

The title of the spreadsheet (cells A1-B1) is Diary.� Keeping detailed time management notes provides a diary of the tasks completed.� The worksheet template leaves cell C1 as the active cell. After opening a new worksheet, hold down the control key and press the semi-colon key to enter the current date. Shown below is the format of cell C1:

That is, the format specifies the cell is a date, and has the format of "March 14, 1998". In other words, the format spells out the month, followed by the day, a comma, and the four-digit year.

The process on Pocket Excel is similar.� Below is a screen capture of using the process on a PocketPC.

The PocketPC also supports the Control+SemiColon to enter the current date. This is accessible through using the miniature on screen keyboard.

Unfortunately, the PocketPC does not support the Control+Shift+Colon/SemiColon entry of the current time. However, the keyboard from Targus that attaches to most PocketPC devices does support this functionality.

The next step is to enter the start of the workday in cell A3. After navigating to cell A3, the user presses and holds the control and shift keys, then presses the semi-colon/colon key. This enters the current date time. Each person ritual to start the workday is different. The author of this paper starts the day by logging into the network, fixing a pot of tea, downloading all the accumulated email (a.k.a. spam), and preparing a quick status report showing what was accomplished the previous day. The process starts by entering the first task of the day into cell C3. When a task is finished, the user navigates to cell B3, and again holds the control and shift keys, then presses the semi-colon/colon key entering in the ending date for the task.

Cell A4 has the formula "=B3"; Cell A5, has the formula "=B4" and so on. The start time of each task is the end time of the previous task. Although it is possible to multitasks, few people are skilled at working on multiple tasks at the same time. Simplicity is the goal.

Those rare people that can simultaneously execute multiple tasks require a different approach. After the first task, all other tasks only require entering the ending time via control+shift+colon. The picture below shows the format of the cells in columns A and B:

The cells use military time without seconds. Excel enters the current time down to the second. Few need that level of accuracy on the time. Military time has the advantage of requiring the fewest characters. This is important when using the worksheet on a PocketPC.

The title for Column D is "Person". This allows entering the names of the people associated with the task. The title for Column E is "Account". This approach allows entering the account number (or name) associated with the task. This permits tracking of time to the precision of the user. The title for Column F is "Elapsed". This cell calculates the elapsed time spent on the task. Cell F3 has the formula "=MINUTE(B3-A3) + (HOUR(B3-A3)*60)" The Excel function "MINUTE" returns "the minutes of a time value. The minute is given as an integer, ranging from 0 to 59." (Excel 2000 Help File). The "HOUR" function returns the hour of a time value. Subtracting A3 from B3 gives the elapsed time. The function converts the elapsed time into minutes. The worksheet automatically calculates the number of minutes spent on each task. Schmidt, Kiemele, and Cheek (no date) cited Motorola University, "If what we know about our processes cannot be expressed in numbers, we don't know much about them. If we don't know much about them, we can't control them. If we can't control them, we can't compete." Shown below is the format for column F:

Column F is a number and shows two decimal places.

Below is an actual example from April 2, 2002 of the use of the worksheet.

	A	B	C	D	E	F
1	Diary		April 2, 2002
2	Start	End	Task Description	Person	Account	Elapsed
3	7:30	8:22	Login, Email, Tea, Status		310	52
4	8:22	8:40	Status reports for LDI		3760	18
5	8:40	8:45	Voice mail.		310	5
6	8:45	8:46	Discussion of Near and Far	Melissa	310	1
7	8:46	8:50	Researching Near & Far		310	4
8	8:50	9:20	Daily Update	Clark	310	30
9	9:20	9:24	MBWA: discussing C&P and Information Today	Julia	3761	4
10	9:24	9:45	Hidra documentation for Juris		3677	21
11	9:45	10:00	LMP Status report		3807	15
12	10:00	10:20	MBWA: LMP Status	Joe	3807	20
13	10:20	10:44	MBWA: LMP Status	Lillie	3807	24
14	10:44	12:46	LMP Status report		3807	122
15	12:46	13:18	Lunch			32
16	13:18	14:18	Reading Websters Proposal and responding		510	60
17	14:18	14:30	Posting status messages		3760	12
18	14:30	14:34	Discussing typesetting	Mike	310	4
19	14:34	14:53	Discussing Websters Pricing	Clark	510	19
20	14:53	15:09	Discussing Compare and Typesetting of Websters	Melissa	510	16
21	15:09	15:35	MBWA	Joe & Lillie	3807	26
22	15:35	16:16	Websters Timeline		510	41
23	16:16	16:38	Discussing Webser Proposal	Clark	510	22
24	16:38	17:00	Reading Picatany RFP		510	22

Stephen Covey described the concept of putting first things first in your life in his book, "The 7 Habits of Highly Effective People". Covey (1990) produced a diagram, shown below, which divides tasks along two independent axes:� urgency and importance.

	Urgent	Not Urgent
Important	I Activities Crises Pressing problems Deadline-driven projects	II Activities Prevention, PC Activities Relationship building Recognizing new opportunities Planning, recreation
Not Important	III Activities Interruptions, some calls Some mail, some reports Some meetings Proximate, pressing matters Popular actives	IV Activities Trivia, busy work Some mail Some phone calls Time wasters Pleasant activities

Table from Covey (1990) (pg. 151)

Focusing on a long-term goal affects the short-term decisions you make. Decisions you make in the short-term should not be counter to your long-term goals. You should evaluate the impact of making a short-term decision on your long-term goals. One issue with Covey's approach is there is no feedback to see if your planned scheduling matches with what actually happened. Keeping a diary allows comparing what actually happened with the planned schedule. This provided a feedback loop in the process. The use a time management diary provides a method to evaluate how effective your scheduling is at keeping your daily activities in quadrant II of Covey's chart: the important but not urgent activities.

Bibliography

Covey, S. R. (1990). The 7 Habits of Highly Effective People. New York: Simon & Schuster.

Major, R. (1999). TQM 101. Retrieved November 11, 2004 from the World Wide Web: http://www.symetric.ca/tqm.htm

Schmidt, S. R, Kiemele, M, J., & Cheek, T. F. (no date). Don't Let TQM Drain You Dry Without Any ROI. Retrieved November 11, 2004 from the World Wide Web: http://www.airacad.com/tqmroi.htm

The author, Jeffrey McArthur, is President of JSM Software, a company focused on affordable business intelligence solution using Microsoft SQL Server. You can reach Jeffrey at jeffmcarthur@jsm-software.com. For more information on JSM Software, please see http://www.jsm-software.com or contact the sales department at 1-410-290-6958.