Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring total # of hours between multiple times
I am creating a work schedule template that will total the number of hours an
individual is scheduled to work for the week. The template will reflect the work schedule for two weeks for a number of individuals. I have entered a formula that will calculate the total if the cell contents are all time values. My problem is that any given day can have "OFF", "VAC" or "HOL" in one of the cells. This returns a "#VALUE!" error. Is there any way to imbed an additional formula to ignore any cells with text or even with the specific values listed above. Here is a copy of the information I am working with: 15-Jul 16-Jul 17-Jul Hours 06:00 16:00 07:00 16:00 OFF #VALUE! The formula enter is: =((K3-J3)*24)+((M3-L3)*24)+((O3-N3)*24) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring total # of hours between multiple times
Provided that none of the time ranges span past midnight. For example:
5:00 PM - 1:00 AM =(SUM(O3,M3,K3)-SUM(N3,L3,J3))*24 Format as General or Number -- Biff Microsoft Excel MVP "R P Wilson" <R P wrote in message ... I am creating a work schedule template that will total the number of hours an individual is scheduled to work for the week. The template will reflect the work schedule for two weeks for a number of individuals. I have entered a formula that will calculate the total if the cell contents are all time values. My problem is that any given day can have "OFF", "VAC" or "HOL" in one of the cells. This returns a "#VALUE!" error. Is there any way to imbed an additional formula to ignore any cells with text or even with the specific values listed above. Here is a copy of the information I am working with: 15-Jul 16-Jul 17-Jul Hours 06:00 16:00 07:00 16:00 OFF #VALUE! The formula enter is: =((K3-J3)*24)+((M3-L3)*24)+((O3-N3)*24) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring total # of hours between multiple times
The time ranges DO span past midnight.
"T. Valko" wrote: Provided that none of the time ranges span past midnight. For example: 5:00 PM - 1:00 AM =(SUM(O3,M3,K3)-SUM(N3,L3,J3))*24 Format as General or Number -- Biff Microsoft Excel MVP "R P Wilson" <R P wrote in message ... I am creating a work schedule template that will total the number of hours an individual is scheduled to work for the week. The template will reflect the work schedule for two weeks for a number of individuals. I have entered a formula that will calculate the total if the cell contents are all time values. My problem is that any given day can have "OFF", "VAC" or "HOL" in one of the cells. This returns a "#VALUE!" error. Is there any way to imbed an additional formula to ignore any cells with text or even with the specific values listed above. Here is a copy of the information I am working with: 15-Jul 16-Jul 17-Jul Hours 06:00 16:00 07:00 16:00 OFF #VALUE! The formula enter is: =((K3-J3)*24)+((M3-L3)*24)+((O3-N3)*24) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring total # of hours between multiple times
Thanks for the reply; however, the formula returns the value "0" if any of
the cells are blank or contain text. "Per Jessen" wrote: Hi If text is only entered in K3, M3 or O3, try this: =IF(ISNUMBER(K3),K3-J3,0)+IF(ISNUMBER(M3),M3-L3,0)+IF(ISNUMBER(O3),O3-N3,0) Regards, Per "R P Wilson" <R P skrev i meddelelsen ... I am creating a work schedule template that will total the number of hours an individual is scheduled to work for the week. The template will reflect the work schedule for two weeks for a number of individuals. I have entered a formula that will calculate the total if the cell contents are all time values. My problem is that any given day can have "OFF", "VAC" or "HOL" in one of the cells. This returns a "#VALUE!" error. Is there any way to imbed an additional formula to ignore any cells with text or even with the specific values listed above. Here is a copy of the information I am working with: 15-Jul 16-Jul 17-Jul Hours 06:00 16:00 07:00 16:00 OFF #VALUE! The formula enter is: =((K3-J3)*24)+((M3-L3)*24)+((O3-N3)*24) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figuring total # of hours between multiple times
The time ranges DO span past midnight.
In that case, I think you'd be better off doing a daily total and then getting the weekly/bi-weekly total from the daily total. If you wnat to use a single formula that accounts for both text entries like Vac and has to account for times past midnight, that formula would be long and somewhat complex. For example, array entered** : =IF(MOD(COUNT(J3:Q3),2)=1,"",(SUM(IF(ISNUMBER(K3:Q 3),IF(MOD(COLUMN(K3:Q3),2)=1,IF(K3:Q3<J3:P3,K3:Q3+ 1,K3:Q3))))-SUM(IF(ISNUMBER(J3:P3),IF(MOD(COLUMN(J3:P3),2)=0,J 3:P3))))*24) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Not real pretty, is it? <g To get the daily total accounting for text entries and times past midnight: J3 = 7:00 PM K3 = 3:00 AM =IF(COUNT(J3:K3)=2,MOD(K3-J3,1)*24,"") Then the weekly/bi-weekly total would be a simple SUM formula: =SUM(daily_total_range) -- Biff Microsoft Excel MVP "R P Wilson" wrote in message ... The time ranges DO span past midnight. "T. Valko" wrote: Provided that none of the time ranges span past midnight. For example: 5:00 PM - 1:00 AM =(SUM(O3,M3,K3)-SUM(N3,L3,J3))*24 Format as General or Number -- Biff Microsoft Excel MVP "R P Wilson" <R P wrote in message ... I am creating a work schedule template that will total the number of hours an individual is scheduled to work for the week. The template will reflect the work schedule for two weeks for a number of individuals. I have entered a formula that will calculate the total if the cell contents are all time values. My problem is that any given day can have "OFF", "VAC" or "HOL" in one of the cells. This returns a "#VALUE!" error. Is there any way to imbed an additional formula to ignore any cells with text or even with the specific values listed above. Here is a copy of the information I am working with: 15-Jul 16-Jul 17-Jul Hours 06:00 16:00 07:00 16:00 OFF #VALUE! The formula enter is: =((K3-J3)*24)+((M3-L3)*24)+((O3-N3)*24) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate total pay from pay rate times hours and minutes | Excel Discussion (Misc queries) | |||
In Excell how can i get a total hours worked value from 2 times? | Excel Worksheet Functions | |||
multiply hours/minutes times dollars for total dollar amount | Excel Discussion (Misc queries) | |||
Figuring hours | Excel Worksheet Functions | |||
Figuring military time for total hours | Excel Worksheet Functions |