You can create a useful workbook that helps you keep track of your schedule, using only built-in Excel functions! No VBA macros are required. I created this workbook that can be used to track daily activities. For example, if you have to keep track of time spent on a project (for billing purposes), or tell your boss what you were up to all day.
This workbook uses Data Validation, Dynamically Named Ranges, Custom Cell Formatting and Array Formulas to calculate elapsed work times.
First, name your worksheet ‘Time Log’ by double-clicking the sheet tab name.
Next, the following named ranges should be created (Ctrl+F3 or Insert » Name » Define):
- Name: Activity
- Refers to: ='Time Log'!$B$6:$B$30
- Name: Duration
- Refers to: ='Time Log'!$E$6:$E$30
- Name: End
- Refers to: ='Time Log'!$D$6:$D$30
- Name: ListRange
- Refers to: =OFFSET('Time Log'!$L$2,0,0,COUNTA('Time Log'!$L:$L),1)
- Name: Start
- Refers to: ='Time Log'!$C$6:$C$30
Now place a list of work activities in column L, starting in cell L2:
i.e. whatever you might work on during any given day.
Highlight B6:B30 and click on Data » Validation. Select List in the Allow: dropdown, for
the Source, enter =ListRange.
This will tie your activity list in column B to the list you created in column L, so you can select activities from a dropdown box instead of typing them (and potentially mistyping and causing errors in the calculation formulas). You can add/remove entries in column L and they will automatically be added to the validation dropdown in cells B6:B30.
Highlight cells C6:D30 and format as Time. Then place this formula in cell E6:
Format cell as Custom, enter h "hours, " mm "minutes" in the Type box.
Cell E6 should be filled down to E30. Now in another cell (I chose H9) place the following formula:
="Total time logged today: "&TEXT(SUM(Duration),"h:mm")&" hours"
This formula will auto-total the entries in column E to give you a sum of how much you have logged. To get a running total of the time spent on each activity, use the following formula:
If you create a separate array formula for each activity, you can get a running total of each activity, for example see H12:I23 in the sample Daily Time Log Workbook. As you add and repeat activities at different times during the day, these formulas calculate running totals.
If you are satisfied with all of the entries in column L, you can safely hide this column (select a cell in the column, go to Format » Column » Hide). If you ever need to edit this list, just unhide the column.
To wrap this up, select a cell at the bottom of the data area (I chose A33) and go to Window>Freeze Panes. This keeps the work area from being scrolled off the screen by mistake.
To use the workbook, simply select a cell in B6:B30 and click the dropdown box to select the activity you want to document. Enter the start and end times (Time values must be entered in 24 hour format, i.e. 15:00 for 3:00 PM). The duration and running total formulas will auto-calculate to show you totals, so all you have to do is enter the activity, start and end time.