I'm doing a timesheet for my niece and am having a problem (I'm not very
skilled at Excel - yet!). She needs to write down her entries in one cell,
like 9:30am-5:30pm, or 8:00pm-4:00am, with a total at the bottom of the
dcolumn showing that day's total hours worked by the various employees. How
do I go about it?

What she should do is enter the start time in one cell, the end time in
another, and than have a simple subtraction in a third to calculate the days
hours. Then just sum the days hours to get the weeks hours (and format as
[h]:mm so as not to cycle through 24 hours).

Don't enter both in one cell, it is just too convoluted to extract again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
I'm doing a timesheet for my niece and am having a problem (I'm not very
skilled at Excel - yet!). She needs to write down her entries in one
cell,
like 9:30am-5:30pm, or 8:00pm-4:00am, with a total at the bottom of the
dcolumn showing that day's total hours worked by the various employees.
How
do I go about it?

Thanks Bob! It worked on most of the problems, but not all. It didn't
funciton correctly on the type of problem where the employee started in the
evening and left work in the morning (night shift - 22:30 to 6:30). It came
out as a string of pound signs. How can I fix this problem. I tried some of
the ideans from the Excel BB area, but they didn't work either. Anything you
can suggest would be better than what I have (or haven't) come up with.
Thanks tons.
PJ

"Bob Phillips" wrote:

What she should do is enter the start time in one cell, the end time in
another, and than have a simple subtraction in a third to calculate the days
hours. Then just sum the days hours to get the weeks hours (and format as
[h]:mm so as not to cycle through 24 hours).

Don't enter both in one cell, it is just too convoluted to extract again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
I'm doing a timesheet for my niece and am having a problem (I'm not very
skilled at Excel - yet!). She needs to write down her entries in one
cell,
like 9:30am-5:30pm, or 8:00pm-4:00am, with a total at the bottom of the
dcolumn showing that day's total hours worked by the various employees.
How
do I go about it?

That is because when you subtract the end tine from the start time, you are
dealing with negative time (Excel stores time as a fraction of 1 day, so you
are subtracting 0.270833 from 0.9375, which is negative, and as time can't
be negative (at least in this universe), Excel objects as refuses to display
it.

There are two possible solutions:

- test if the start date is after the end date, =IF(A1B1,1-(A1-B1),B1-A1)

- use a more generic formula of =MOD(B1-A1,1)

You could actually switch to the 1904 date system
(ToolsOptionsCalculation1904 date system), which does allow negative
time, but you would still need a formula to transform the negative time
result, so I don't think that it is applicable here.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
Thanks Bob! It worked on most of the problems, but not all. It didn't
funciton correctly on the type of problem where the employee started in
the
evening and left work in the morning (night shift - 22:30 to 6:30). It
came
out as a string of pound signs. How can I fix this problem. I tried some
of
the ideans from the Excel BB area, but they didn't work either. Anything
you
can suggest would be better than what I have (or haven't) come up with.
Thanks tons.
PJ

"Bob Phillips" wrote:

What she should do is enter the start time in one cell, the end time in
another, and than have a simple subtraction in a third to calculate the
days
hours. Then just sum the days hours to get the weeks hours (and format
as
[h]:mm so as not to cycle through 24 hours).

Don't enter both in one cell, it is just too convoluted to extract
again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
I'm doing a timesheet for my niece and am having a problem (I'm not
very
skilled at Excel - yet!). She needs to write down her entries in one
cell,
like 9:30am-5:30pm, or 8:00pm-4:00am, with a total at the bottom of
the
dcolumn showing that day's total hours worked by the various
employees.
How
do I go about it?

I've been haunting other sites and found the two formulas you mentioned.
Neither worked, unfortunately, on both 6:30 to14:00 AND 22:30 to 6:30. I did
find the following forumla on another MS site and it worked just fine
=B1-A1+IF(A1B1,1). My NEW problem is that I want to now add up the
resulting columns of hours but Excel won't do it for me (probably because
there are formulas in those columns, and not 'real' numbers. Any more
advice? After sniffing around some of these sites, I realize what a real
beginner/dunce I am and I appreciate your help. Are all of you programers?

"Bob Phillips" wrote:

That is because when you subtract the end tine from the start time, you are
dealing with negative time (Excel stores time as a fraction of 1 day, so you
are subtracting 0.270833 from 0.9375, which is negative, and as time can't
be negative (at least in this universe), Excel objects as refuses to display
it.

There are two possible solutions:

- test if the start date is after the end date, =IF(A1B1,1-(A1-B1),B1-A1)

- use a more generic formula of =MOD(B1-A1,1)

You could actually switch to the 1904 date system
(ToolsOptionsCalculation1904 date system), which does allow negative
time, but you would still need a formula to transform the negative time
result, so I don't think that it is applicable here.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
Thanks Bob! It worked on most of the problems, but not all. It didn't
funciton correctly on the type of problem where the employee started in
the
evening and left work in the morning (night shift - 22:30 to 6:30). It
came
out as a string of pound signs. How can I fix this problem. I tried some
of
the ideans from the Excel BB area, but they didn't work either. Anything
you
can suggest would be better than what I have (or haven't) come up with.
Thanks tons.
PJ

"Bob Phillips" wrote:

What she should do is enter the start time in one cell, the end time in
another, and than have a simple subtraction in a third to calculate the
days
hours. Then just sum the days hours to get the weeks hours (and format
as
[h]:mm so as not to cycle through 24 hours).

Don't enter both in one cell, it is just too convoluted to extract
again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
I'm doing a timesheet for my niece and am having a problem (I'm not
very
skilled at Excel - yet!). She needs to write down her entries in one
cell,
like 9:30am-5:30pm, or 8:00pm-4:00am, with a total at the bottom of
the
dcolumn showing that day's total hours worked by the various
employees.
How
do I go about it?

What do you mean by Excel won't do it? It might just be a format issue,
because it goes above 24 hours. Try formatting as [h]:mm. Otherwise post
back with more details.

BTW I can't speak for all, but I call myself an IT consultant, I see myself
as more than just a programmer <g.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
I've been haunting other sites and found the two formulas you mentioned.
Neither worked, unfortunately, on both 6:30 to14:00 AND 22:30 to 6:30. I
did
find the following forumla on another MS site and it worked just fine
=B1-A1+IF(A1B1,1). My NEW problem is that I want to now add up the
resulting columns of hours but Excel won't do it for me (probably because
there are formulas in those columns, and not 'real' numbers. Any more
advice? After sniffing around some of these sites, I realize what a real
beginner/dunce I am and I appreciate your help. Are all of you
programers?

"Bob Phillips" wrote:

That is because when you subtract the end tine from the start time, you
are
dealing with negative time (Excel stores time as a fraction of 1 day, so
you
are subtracting 0.270833 from 0.9375, which is negative, and as time
can't
be negative (at least in this universe), Excel objects as refuses to
display
it.

There are two possible solutions:

- test if the start date is after the end date,
=IF(A1B1,1-(A1-B1),B1-A1)

- use a more generic formula of =MOD(B1-A1,1)

You could actually switch to the 1904 date system
(ToolsOptionsCalculation1904 date system), which does allow negative
time, but you would still need a formula to transform the negative time
result, so I don't think that it is applicable here.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
Thanks Bob! It worked on most of the problems, but not all. It
didn't
funciton correctly on the type of problem where the employee started
in
the
evening and left work in the morning (night shift - 22:30 to 6:30).
It
came
out as a string of pound signs. How can I fix this problem. I tried
some
of
the ideans from the Excel BB area, but they didn't work either.
Anything
you
can suggest would be better than what I have (or haven't) come up
with.
Thanks tons.
PJ

"Bob Phillips" wrote:

What she should do is enter the start time in one cell, the end time
in
another, and than have a simple subtraction in a third to calculate
the
days
hours. Then just sum the days hours to get the weeks hours (and
format
as
[h]:mm so as not to cycle through 24 hours).

Don't enter both in one cell, it is just too convoluted to extract
again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PJ" wrote in message
...
I'm doing a timesheet for my niece and am having a problem (I'm
not
very
skilled at Excel - yet!). She needs to write down her entries in
one
cell,
like 9:30am-5:30pm, or 8:00pm-4:00am, with a total at the bottom
of
the
dcolumn showing that day's total hours worked by the various
employees.
How
do I go about it?