SFCChase
Board Regular
- Joined
- Jun 25, 2013
- Messages
- 110
- Office Version
- 2016
- Platform
- Windows
I have a page of data that compiles an overall time from multiple events. I'm trying to set up a helper column with a TRUE/FALSE result for use in another formula but for some reason my TIME formula keeps giving FALSE results on every row except my top one.
My Total Time formula is designed to SUM event times if they are all entered.
Cell B2: =IF(AND(A2="",C2="",D2="",E2="",F2="",G2="",H2="",I2=""),"",SUM(C2:I2))
For some reason those with blank cells keeps defaulting to 00:00 instead of staying blank.
I want a TRUE result in the helper cell if the total time is under 30 min and there are no missing event times.
Cell J2: =AND(B2<TIME(0,30,0),C2<>"",D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"") gives me a TRUE result, however when i drag the formula down I get FALSE as a result in all the other cells.
Any suggestions would be appreciated
Name | Total Time | Event A | Event B | Event C | Event D | Event E | Event F | Event G | Helper |
JOE | 26.59 | 08:20 | 00:55 | 00:45 | 02:55 | 03:25 | 01:14 | 09:25 | TRUE |
MIKE | 26:41 | 06:35 | 01:22 | 01:10 | 05:22 | 00:55 | 01:22 | 09:55 | |
ROB | 35:45 | 10:25 | 00:50 | 00:58 | 10:21 | 02:10 | 02:01 | 09:00 | |
BILL | 00:00 |
My Total Time formula is designed to SUM event times if they are all entered.
Cell B2: =IF(AND(A2="",C2="",D2="",E2="",F2="",G2="",H2="",I2=""),"",SUM(C2:I2))
For some reason those with blank cells keeps defaulting to 00:00 instead of staying blank.
I want a TRUE result in the helper cell if the total time is under 30 min and there are no missing event times.
Cell J2: =AND(B2<TIME(0,30,0),C2<>"",D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"") gives me a TRUE result, however when i drag the formula down I get FALSE as a result in all the other cells.
Any suggestions would be appreciated