If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating time
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! |
#2
|
|||
|
|||
Calculating time
Just use the SUM function:
=SUM(J9,M9,P9,S9,V9,Y9,AB9) Format as [h]:mm -- Biff Microsoft Excel MVP "Erica" wrote in message ... 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! |
#3
|
|||
|
|||
Calculating time
This worked... THANK YOU!!!!!
"T. Valko" wrote: Just use the SUM function: =SUM(J9,M9,P9,S9,V9,Y9,AB9) Format as [h]:mm -- Biff Microsoft Excel MVP "Erica" wrote in message ... 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! |
#4
|
|||
|
|||
Calculating time
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. -- David Biddulph "Erica" wrote in message ... 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! |
#5
|
|||
|
|||
Calculating time
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Erica" wrote in message ... This worked... THANK YOU!!!!! "T. Valko" wrote: Just use the SUM function: =SUM(J9,M9,P9,S9,V9,Y9,AB9) Format as [h]:mm -- Biff Microsoft Excel MVP "Erica" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|