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"
[email protected] 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?-
:
-
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"
[email protected] 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
:
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"
[email protected] 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?
-
--
