Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions |