Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times
I am using the following formula to add times (24 hour) in MS Excel 2003
=IF(J<I,J+1-I,J-I) . I is my start time J is my finish time. The formula works ok when a start and finish time are entered but if ONLY a start time or finish time are entered the "total hours" cell is populated with incorrect data. Is there any way I can say"if either I or J are empty then "total Hours = 0 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times
Try this:
=IF(OR(I="",J=""),0,IF(J<I,J+1-I,J-I)) Obviously, I and J need to be cell references (unless they are named ranges). Hope this helps. Pete "MBC" wrote in message ... I am using the following formula to add times (24 hour) in MS Excel 2003 =IF(J<I,J+1-I,J-I) . I is my start time J is my finish time. The formula works ok when a start and finish time are entered but if ONLY a start time or finish time are entered the "total hours" cell is populated with incorrect data. Is there any way I can say"if either I or J are empty then "total Hours = 0 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times
Let's begin by simplifying your formula to
=(J1-I1)+(J1I1) which is functional identical to your formula. The second term evaluates to 0 or 1 but when Excel does arithmetic on Boolean values it takes FALSE as 0 and TRUE as 1 Now we can test for cells with no data (blank cells are considered to equal zero): =IF(OR(I1=0,J1=0),0,(J1-I1)+(J1I1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MBC" wrote in message ... I am using the following formula to add times (24 hour) in MS Excel 2003 =IF(J<I,J+1-I,J-I) . I is my start time J is my finish time. The formula works ok when a start and finish time are entered but if ONLY a start time or finish time are entered the "total hours" cell is populated with incorrect data. Is there any way I can say"if either I or J are empty then "total Hours = 0 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding times
Thanks Bernard, that solved my initial problem. However 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?. Cant 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)) "Bernard Liengme" wrote: Let's begin by simplifying your formula to =(J1-I1)+(J1I1) which is functional identical to your formula. The second term evaluates to 0 or 1 but when Excel does arithmetic on Boolean values it takes FALSE as 0 and TRUE as 1 Now we can test for cells with no data (blank cells are considered to equal zero): =IF(OR(I1=0,J1=0),0,(J1-I1)+(J1I1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MBC" wrote in message ... I am using the following formula to add times (24 hour) in MS Excel 2003 =IF(J<I,J+1-I,J-I) . I is my start time J is my finish time. The formula works ok when a start and finish time are entered but if ONLY a start time or finish time are entered the "total hours" cell is populated with incorrect data. Is there any way I can say"if either I or J are empty then "total Hours = 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADDING TIMES | Excel Worksheet Functions | |||
Adding times | New Users to Excel | |||
adding times together | Excel Discussion (Misc queries) | |||
Adding up Times | Excel Worksheet Functions | |||
Adding times | Excel Worksheet Functions |