Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
I am trying to work out the "shift Gap"(Rest time) between two shifts which
are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
I get 9, but try
=IF('Week 17'!AB3=0,1,MOD(I3-'Week 17'!AB3,1))*24 -- __________________________________ HTH Bob "MBC" wrote in message ... I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
Thanks Bob that worked however where my start time cell ref I10 is empty it
returns 7 as the answer. Is there any way I can get it to return 24? "MBC" wrote: I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
Where does I10 come into it? AB3 is the start cell and if that is zero it
returns 24. -- __________________________________ HTH Bob "MBC" wrote in message ... Thanks Bob that worked however where my start time cell ref I10 is empty it returns 7 as the answer. Is there any way I can get it to return 24? "MBC" wrote: I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
Sorry Bob I should have said I3.Even then I still get a result of 7 if I3 is
left empty and ideally I would like it to read 24. "MBC" wrote: I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
It would be I3 which would be empty as this would signify a "rest period".
"MBC" wrote: I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
=IF(OR('Week 17'!AB3=0,'Week 18'!I3=0),1,MOD(I3-'Week 17'!AB3,1))*24
-- __________________________________ HTH Bob "MBC" wrote in message ... It would be I3 which would be empty as this would signify a "rest period". "MBC" wrote: I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times on different worksheets in same file
Thanks Bob, this worked. Much appreciated
"Bob Phillips" wrote: I get 9, but try =IF('Week 17'!AB3=0,1,MOD(I3-'Week 17'!AB3,1))*24 -- __________________________________ HTH Bob "MBC" wrote in message ... I am trying to work out the "shift Gap"(Rest time) between two shifts which are on different worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of 8:00. The answer is 15 but the formula I am using gives me an answer of 17?. Can't see where I am going wrong. =IF('Week 17'!AB3=0,24,IF('Week 17'!AB3I3,('Week 17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Times | Excel Worksheet Functions | |||
Adding Times | Excel Discussion (Misc queries) | |||
Help Please on adding times | Excel Discussion (Misc queries) | |||
adding times again | New Users to Excel | |||
adding times | Excel Discussion (Misc queries) |