daily activity time log

[Lesah]

I have been asked to account for how much time is spent each day in different
activities. This will be categorized by: customer service [phone calls and
visitors], data processing, etc. I need to enter the time of day each time I
answer the phone, and log out when hanging up, with a total time in a 3rd
column. (8:30 - 8:43 = 13 minutes). Same for visitors and start and stop
times for various tasks. At the end of the day I could then provide the total
time and percentages of time spent on each category. I am not a new user, but
have never advanced much past simple math calculations.

I would appreciate any help and thanks in advance.

Lesah

 

[T. Valko]

Try something like this:

Column A = task category
Column B = start time for task
Column C = end time for task
Column D = time for that task

Formula in column D: =(B2-A2)*1440

That will return the task time in *minutes*.

Copy the formula in column D down to the end of the task list.

To get the totals and summary of tasks list the unique categories in a range
of cells, say, F2:F6.

For the total time for each category enter this formula in G2 and copy down
to G6:

=SUMIF(A$2:A$20,F2,D$2$20)

For the total time for *all* tasks enter this formula in G7:

=SUM(G2:G6)

For the % of time for each category enter this formula H2 and copy down to
H6:

=G2/G$7

Format as PERCENTAGE (maybe 1 decimal place)

Then enter this formula in H7:

=SUM(H2:H6)

 

[Lesah]

Thanks! I tried all you said, formatted the start and end time as 24 hour
time, and the calculation cells as general. I get "0" for the total time. Is
there a problem with the way I am formatting the cells?

Try something like this:

Column A = task category
Column B = start time for task
Column C = end time for task
Column D = time for that task

Formula in column D: =(B2-A2)*1440

That will return the task time in *minutes*.

Copy the formula in column D down to the end of the task list.

To get the totals and summary of tasks list the unique categories in a range
of cells, say, F2:F6.

For the total time for each category enter this formula in G2 and copy down
to G6:

=SUMIF(A$2:A$20,F2,D$2$20)

For the total time for *all* tasks enter this formula in G7:

=SUM(G2:G6)

For the % of time for each category enter this formula H2 and copy down to
H6:

=G2/G$7

Format as PERCENTAGE (maybe 1 decimal place)

Then enter this formula in H7:

=SUM(H2:H6)



--
Biff
Microsoft Excel MVP

I have been asked to account for how much time is spent each day in
different
activities. This will be categorized by: customer service [phone calls and
visitors], data processing, etc. I need to enter the time of day each time
I
answer the phone, and log out when hanging up, with a total time in a 3rd
column. (8:30 - 8:43 = 13 minutes). Same for visitors and start and stop
times for various tasks. At the end of the day I could then provide the
total
time and percentages of time spent on each category. I am not a new user,
but
have never advanced much past simple math calculations.

I would appreciate any help and thanks in advance.

Lesah

 

[T. Valko]

Hmmm...

Everything should work.

Here's a small sample file that demonstrates this:

xTime.xls 17kb

http://cjoint.com/?fbgNSF8GHD

--
Biff
Microsoft Excel MVP

Thanks! I tried all you said, formatted the start and end time as 24 hour
time, and the calculation cells as general. I get "0" for the total time.
Is
there a problem with the way I am formatting the cells?

Try something like this:

Column A = task category
Column B = start time for task
Column C = end time for task
Column D = time for that task

Formula in column D: =(B2-A2)*1440

That will return the task time in *minutes*.

Copy the formula in column D down to the end of the task list.

To get the totals and summary of tasks list the unique categories in a
range
of cells, say, F2:F6.

For the total time for each category enter this formula in G2 and copy
down
to G6:

=SUMIF(A$2:A$20,F2,D$2$20)

For the total time for *all* tasks enter this formula in G7:

=SUM(G2:G6)

For the % of time for each category enter this formula H2 and copy down
to
H6:

=G2/G$7

Format as PERCENTAGE (maybe 1 decimal place)

Then enter this formula in H7:

=SUM(H2:H6)



--
Biff
Microsoft Excel MVP

I have been asked to account for how much time is spent each day in
different
activities. This will be categorized by: customer service [phone calls
and
visitors], data processing, etc. I need to enter the time of day each
time
I
answer the phone, and log out when hanging up, with a total time in a
3rd
column. (8:30 - 8:43 = 13 minutes). Same for visitors and start and
stop
times for various tasks. At the end of the day I could then provide the
total
time and percentages of time spent on each category. I am not a new
user,
but
have never advanced much past simple math calculations.

I would appreciate any help and thanks in advance.

Lesah

 

[Lesah]

Thanks for the sample. Instead of trying to find the difference with mine, I
think I will start with yours...OK? I think the difference was in the
formatting, because I had already figured out that it needed to be C - B. But
I had time columns formatted differently. But I think the small sample you
gave will get me started. Thanks!

Lesah

Hmmm...

Everything should work.

Here's a small sample file that demonstrates this:

xTime.xls 17kb

http://cjoint.com/?fbgNSF8GHD

--
Biff
Microsoft Excel MVP

Thanks! I tried all you said, formatted the start and end time as 24 hour
time, and the calculation cells as general. I get "0" for the total time.
Is
there a problem with the way I am formatting the cells?

Try something like this:

Column A = task category
Column B = start time for task
Column C = end time for task
Column D = time for that task

Formula in column D: =(B2-A2)*1440

That will return the task time in *minutes*.

Copy the formula in column D down to the end of the task list.

To get the totals and summary of tasks list the unique categories in a
range
of cells, say, F2:F6.

For the total time for each category enter this formula in G2 and copy
down
to G6:

=SUMIF(A$2:A$20,F2,D$2$20)

For the total time for *all* tasks enter this formula in G7:

=SUM(G2:G6)

For the % of time for each category enter this formula H2 and copy down
to
H6:

=G2/G$7

Format as PERCENTAGE (maybe 1 decimal place)

Then enter this formula in H7:

=SUM(H2:H6)



--
Biff
Microsoft Excel MVP


I have been asked to account for how much time is spent each day in
different
activities. This will be categorized by: customer service [phone calls
and
visitors], data processing, etc. I need to enter the time of day each
time
I
answer the phone, and log out when hanging up, with a total time in a
3rd
column. (8:30 - 8:43 = 13 minutes). Same for visitors and start and
stop
times for various tasks. At the end of the day I could then provide the
total
time and percentages of time spent on each category. I am not a new
user,
but
have never advanced much past simple math calculations.

I would appreciate any help and thanks in advance.

Lesah

 

[T. Valko]

I had already figured out that it needed to be C - B.

Yeah. Had you not figured that out (my bad!) you would have gotten #VALUE!
errors and not zeros.

Good luck. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks for the sample. Instead of trying to find the difference with mine,
I
think I will start with yours...OK? I think the difference was in the
formatting, because I had already figured out that it needed to be C - B.
But
I had time columns formatted differently. But I think the small sample you
gave will get me started. Thanks!

Lesah

Hmmm...

Everything should work.

Here's a small sample file that demonstrates this:

xTime.xls 17kb

http://cjoint.com/?fbgNSF8GHD

--
Biff
Microsoft Excel MVP

Thanks! I tried all you said, formatted the start and end time as 24
hour
time, and the calculation cells as general. I get "0" for the total
time.
Is
there a problem with the way I am formatting the cells?

:

Try something like this:

Column A = task category
Column B = start time for task
Column C = end time for task
Column D = time for that task

Formula in column D: =(B2-A2)*1440

That will return the task time in *minutes*.

Copy the formula in column D down to the end of the task list.

To get the totals and summary of tasks list the unique categories in a
range
of cells, say, F2:F6.

For the total time for each category enter this formula in G2 and copy
down
to G6:

=SUMIF(A$2:A$20,F2,D$2$20)

For the total time for *all* tasks enter this formula in G7:

=SUM(G2:G6)

For the % of time for each category enter this formula H2 and copy
down
to
H6:

=G2/G$7

Format as PERCENTAGE (maybe 1 decimal place)

Then enter this formula in H7:

=SUM(H2:H6)



--
Biff
Microsoft Excel MVP


I have been asked to account for how much time is spent each day in
different
activities. This will be categorized by: customer service [phone
calls
and
visitors], data processing, etc. I need to enter the time of day
each
time
I
answer the phone, and log out when hanging up, with a total time in
a
3rd
column. (8:30 - 8:43 = 13 minutes). Same for visitors and start and
stop
times for various tasks. At the end of the day I could then provide
the
total
time and percentages of time spent on each category. I am not a new
user,
but
have never advanced much past simple math calculations.

I would appreciate any help and thanks in advance.

Lesah