I am trying to create a timesheet for my students and am running into issue I
am not sure how to solve. Any suggestions would be greatly appreciated.

The student are to work on a series of projects that take anywhere from 20
minutes to ca. 6 hours during a class period of 83 minutes. I want to be able
to keep track of two things. First, the time sheet needs to keeps track of
the total time spent on a particular project (possibly across more one day).
This total will be compared with a percentage of the time I think is
appropriate for that project and a then a grade is assigned. Second, is to
keep track of the total amount of time that is used toward projects (possibly
more than one in a single class period) and then compare this total with the
total number of minutes in the class period (i.e., 83) to determine "wasted"
time.

I have tried setting this time sheet up two different ways: "project based"
and "date based". In other words, I have been able to do each of these
procedures independently. However, I am having a mental block as to how to
set this up so that both would occur at the same time.

Please help with any ideas.

I'm not sure that I'm following you correctly but what I would do is have
the following set up:

Row 1 Labels:
A: Date
B: Project One
D: Project Two
F: Project Three

Highlight B1:C1 and then select: Format Cells Alignment Horizontal
"Center Across Selection" to centre the label between columns B & C

then do the same for Project Two & Three

Row 2:
B: Start
C: End

then do the same for Project Two & Three

Total Time used on Project One:

=SUM((C3:C32-B3:B32))

Entered as an array formula with Control + Shift + Enter not just Enter

Time wasted in Row 3:
=ROUND(TIME(1,23,0),9)-ROUND((SUM(C3,E3,G3)-SUM(B3,D3,F3)),9)

All time cells formatted as "hh:mm" (without the quotes)

The rounding is because I found in a test that TIME(1,23,0) was -3.16414E-15
smaller than
SUM("9:00","11:00","12:00")-sum("10:00","11:15","12:08")


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"quattda" wrote in message
...
I am trying to create a timesheet for my students and am running into issue
I
am not sure how to solve. Any suggestions would be greatly appreciated.

The student are to work on a series of projects that take anywhere from 20
minutes to ca. 6 hours during a class period of 83 minutes. I want to be
able
to keep track of two things. First, the time sheet needs to keeps track of
the total time spent on a particular project (possibly across more one
day).
This total will be compared with a percentage of the time I think is
appropriate for that project and a then a grade is assigned. Second, is to
keep track of the total amount of time that is used toward projects
(possibly
more than one in a single class period) and then compare this total with
the
total number of minutes in the class period (i.e., 83) to determine
"wasted"
time.

I have tried setting this time sheet up two different ways: "project
based"
and "date based". In other words, I have been able to do each of these
procedures independently. However, I am having a mental block as to how to
set this up so that both would occur at the same time.

Please help with any ideas.

That does not do what I am looking for. Maybe if I can clarify it would help.

The student will be working on one or more projects during a given class
period of 83 minutes. Each project has a set amount of time that I think it
should take. The student would enter a beginning time and an end time for
each project. I would have the projects and their associated times set up
already (exactly how depends on whether I create this time sheet has the
project as the base or the date that the work is being done).

I would like to be able to calculate the amount of time that the student is
not using during a given class period (during a single day) for one or more
projects they worked on during that period. I also want to be able to
calculate the total number of minutes used for each individual roject (that
may extend across more than a single day).

As I mentioned in first posting, I can do these indvidually but cannot
figure out how to do these on a single worksheet at the same time. I have
been bouncing back and forth between using Excel or Access. However, I know
Excel better and people I have talked to about this say I "should" be able to
do this in Excel but nobody can suggest a specific set of procedures to do
it. I believe I might have to set up some sort of search procedure which I am
not completely comfortable with.

If this is something that really cannot be done in Excel, I would greatly
appreciate being told that too.


"Sandy Mann" wrote:

I'm not sure that I'm following you correctly but what I would do is have
the following set up:

Row 1 Labels:
A: Date
B: Project One
D: Project Two
F: Project Three

Highlight B1:C1 and then select: Format Cells Alignment Horizontal
"Center Across Selection" to centre the label between columns B & C

then do the same for Project Two & Three

Row 2:
B: Start
C: End

then do the same for Project Two & Three

Total Time used on Project One:

=SUM((C3:C32-B3:B32))

Entered as an array formula with Control + Shift + Enter not just Enter

Time wasted in Row 3:
=ROUND(TIME(1,23,0),9)-ROUND((SUM(C3,E3,G3)-SUM(B3,D3,F3)),9)

All time cells formatted as "hh:mm" (without the quotes)

The rounding is because I found in a test that TIME(1,23,0) was -3.16414E-15
smaller than
SUM("9:00","11:00","12:00")-sum("10:00","11:15","12:08")


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"quattda" wrote in message
...
I am trying to create a timesheet for my students and am running into issue
I
am not sure how to solve. Any suggestions would be greatly appreciated.

The student are to work on a series of projects that take anywhere from 20
minutes to ca. 6 hours during a class period of 83 minutes. I want to be
able
to keep track of two things. First, the time sheet needs to keeps track of
the total time spent on a particular project (possibly across more one
day).
This total will be compared with a percentage of the time I think is
appropriate for that project and a then a grade is assigned. Second, is to
keep track of the total amount of time that is used toward projects
(possibly
more than one in a single class period) and then compare this total with
the
total number of minutes in the class period (i.e., 83) to determine
"wasted"
time.

I have tried setting this time sheet up two different ways: "project
based"
and "date based". In other words, I have been able to do each of these
procedures independently. However, I am having a mental block as to how to
set this up so that both would occur at the same time.

Please help with any ideas.

As I read it, that is more or less what I did. Do you want to send me a
sample sheet directly to see exactly what it is you want to do and how you
want the data laid out?

Replace the part of my address after the @ sign as it says in my signature
below.

Note that "mailinator.com" is a spam trap that you do not have to sign up
to. An account is created for you automatically when a e-mail arrives and
is deleted "after some hours." It is useful for things like creating a
Google account where you need a valid e-mail address to activate it but
although Google munge the address in Google groups, the address is shown in
full in the newsgroups. Within the "few hours" you can retrieve you e-mail
and thus set up the Google account without having to disclose your real
address.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"quattda" wrote in message
...
That does not do what I am looking for. Maybe if I can clarify it would
help.

The student will be working on one or more projects during a given class
period of 83 minutes. Each project has a set amount of time that I think
it
should take. The student would enter a beginning time and an end time for
each project. I would have the projects and their associated times set up
already (exactly how depends on whether I create this time sheet has the
project as the base or the date that the work is being done).

I would like to be able to calculate the amount of time that the student
is
not using during a given class period (during a single day) for one or
more
projects they worked on during that period. I also want to be able to
calculate the total number of minutes used for each individual roject
(that
may extend across more than a single day).

As I mentioned in first posting, I can do these indvidually but cannot
figure out how to do these on a single worksheet at the same time. I have
been bouncing back and forth between using Excel or Access. However, I
know
Excel better and people I have talked to about this say I "should" be able
to
do this in Excel but nobody can suggest a specific set of procedures to do
it. I believe I might have to set up some sort of search procedure which I
am
not completely comfortable with.

If this is something that really cannot be done in Excel, I would greatly
appreciate being told that too.


"Sandy Mann" wrote:

I'm not sure that I'm following you correctly but what I would do is have
the following set up:

Row 1 Labels:
A: Date
B: Project One
D: Project Two
F: Project Three

Highlight B1:C1 and then select: Format Cells Alignment Horizontal

"Center Across Selection" to centre the label between columns B & C

then do the same for Project Two & Three

Row 2:
B: Start
C: End

then do the same for Project Two & Three

Total Time used on Project One:

=SUM((C3:C32-B3:B32))

Entered as an array formula with Control + Shift + Enter not just Enter

Time wasted in Row 3:
=ROUND(TIME(1,23,0),9)-ROUND((SUM(C3,E3,G3)-SUM(B3,D3,F3)),9)

All time cells formatted as "hh:mm" (without the quotes)

The rounding is because I found in a test that TIME(1,23,0)
was -3.16414E-15
smaller than
SUM("9:00","11:00","12:00")-sum("10:00","11:15","12:08")


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"quattda" wrote in message
...
I am trying to create a timesheet for my students and am running into
issue
I
am not sure how to solve. Any suggestions would be greatly appreciated.

The student are to work on a series of projects that take anywhere from
20
minutes to ca. 6 hours during a class period of 83 minutes. I want to
be
able
to keep track of two things. First, the time sheet needs to keeps track
of
the total time spent on a particular project (possibly across more one
day).
This total will be compared with a percentage of the time I think is
appropriate for that project and a then a grade is assigned. Second, is
to
keep track of the total amount of time that is used toward projects
(possibly
more than one in a single class period) and then compare this total
with
the
total number of minutes in the class period (i.e., 83) to determine
"wasted"
time.

I have tried setting this time sheet up two different ways: "project
based"
and "date based". In other words, I have been able to do each of these
procedures independently. However, I am having a mental block as to how
to
set this up so that both would occur at the same time.

Please help with any ideas.