Calculating time

[Erica]

I am working on a spread sheet and I need it to calculate the hours worked
based on the clock in/out time... This is the formula that I have used:
=IF(((I9-H9)*24)>=5,I9-(H9+TIME(0,30,0)),I9-H9)
and it works...
But now I need to add the time worked for all 7 days (2 of which are zero
but have the formula in the cell). I tried =TIME(j9+m9,"h") and it will work
for only 2 cells at a time, however I need to calculate the entire week (7
cells)...
Please help...
THANK YOU!

 

[T. Valko]

Just use the SUM function:

=SUM(J9,M9,P9,S9,V9,Y9,AB9)

Format as [h]:mm

 

[Erica]

This worked... THANK YOU!!!!!

Just use the SUM function:

=SUM(J9,M9,P9,S9,V9,Y9,AB9)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

I am working on a spread sheet and I need it to calculate the hours worked
based on the clock in/out time... This is the formula that I have used:
=IF(((I9-H9)*24)>=5,I9-(H9+TIME(0,30,0)),I9-H9)
and it works...
But now I need to add the time worked for all 7 days (2 of which are zero
but have the formula in the cell). I tried =TIME(j9+m9,"h") and it will
work
for only 2 cells at a time, however I need to calculate the entire week (7
cells)...
Please help...
THANK YOU!

 

[David Biddulph]

Please don't retype a working formula into the newsgroup, as you are likely
to make mistakes. Copy from the formula bar and paste here.
=TIME(j9+m9,"h") would have been rejected with invalid syntax. Did you
actually use =TEXT(j9+m9,"h")?

I don't understand why you say that "it will work for only 2 cells at a
time". What is to stop you adding more than just j9+m9? If you are getting
a different result from what you expected, then it is better if you tell us
what result you got, what result you expected, what formula you used, and
what values were in the cells leading into that formula. If your problem
was that the time wrapped round when you got beyond 24 hours, then perhaps
you intended to use "[h]"", rather than "h" in your TEXT function?

But did you really want the TEXT function? You could format =J9+M9 to
display as [h], or as [h]:mm. If you want to convert from Excel time to
decimal hours, then use =(J9+M9)*24 and format as general or number.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

This worked... THANK YOU!!!!!

Just use the SUM function:

=SUM(J9,M9,P9,S9,V9,Y9,AB9)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

I am working on a spread sheet and I need it to calculate the hours
worked
based on the clock in/out time... This is the formula that I have used:
=IF(((I9-H9)*24)>=5,I9-(H9+TIME(0,30,0)),I9-H9)
and it works...
But now I need to add the time worked for all 7 days (2 of which are
zero
but have the formula in the cell). I tried =TIME(j9+m9,"h") and it will
work
for only 2 cells at a time, however I need to calculate the entire week
(7
cells)...
Please help...
THANK YOU!