How do I sum up a column hours, like 9:30am - 5:30pm, in Excel?

[Guest]

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?

 

[Bob Phillips]

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)

 

[Guest]

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

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)

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?

 

[Bob Phillips]

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(A1>B1,1-(A1-B1),B1-A1)

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

You could actually switch to the 1904 date system
(Tools>Options>Calculation>1904 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)

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

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)

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?

 

[Guest]

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(A1>B1,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?

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(A1>B1,1-(A1-B1),B1-A1)

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

You could actually switch to the 1904 date system
(Tools>Options>Calculation>1904 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)

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

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)

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?

 

[Bob Phillips]

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)

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(A1>B1,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?

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(A1>B1,1-(A1-B1),B1-A1)

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

You could actually switch to the 1904 date system
(Tools>Options>Calculation>1904 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)

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

:

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)

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?