JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 519
- Office Version
- 365
- Platform
- Windows
Hi All,
I am stumped by this one. In the attached minisheet, there is a default time/date value that gets calculated in cell D13.
The issue that this default value doesn't mean anything, and else where in the workbook, there is a formula "=D13".
This useless result is only calculated when I am dealing with historical data. When I am using future data, the value in D13 is a real time in 24hr format and is required. No problem.
What I was trying to do is something simple like if(d13<01-01-2000,"",D13"), but all I get is the #Value error.
Typically, when dealing with historical data, Excel will put something like "08-01-1900 12:00:00 AM" in D13.
How do I test for this?
Thanks in advance.
I am stumped by this one. In the attached minisheet, there is a default time/date value that gets calculated in cell D13.
The issue that this default value doesn't mean anything, and else where in the workbook, there is a formula "=D13".
This useless result is only calculated when I am dealing with historical data. When I am using future data, the value in D13 is a real time in 24hr format and is required. No problem.
What I was trying to do is something simple like if(d13<01-01-2000,"",D13"), but all I get is the #Value error.
Typically, when dealing with historical data, Excel will put something like "08-01-1900 12:00:00 AM" in D13.
How do I test for this?
Thanks in advance.
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
2 | ||||||||||||
3 | ||||||||||||
4 | ||||||||||||
5 | ||||||||||||
6 | ||||||||||||
7 | ||||||||||||
8 | Riverton | Track | RIVERTON | |||||||||
9 | Condition | Heavy | ||||||||||
10 | Riverton | Distance (m) | 1200 | |||||||||
11 | Heavy | Race Number | 7 | |||||||||
12 | Class | Class | ||||||||||
13 | 1 | Time | 12:00 AM | |||||||||
14 | ||||||||||||
15 | 2 | |||||||||||
16 | ||||||||||||
17 | 3 | |||||||||||
18 | ||||||||||||
19 | 4 | |||||||||||
20 | ||||||||||||
21 | 5 | |||||||||||
22 | ||||||||||||
23 | 6 | |||||||||||
24 | ||||||||||||
25 | 7 | |||||||||||
26 | ||||||||||||
27 | 0:00 | |||||||||||
28 | 1200m R7 The Red Door Mdn | |||||||||||
29 | Final Results | |||||||||||
30 | ||||||||||||
31 | ||||||||||||
32 | ||||||||||||
33 | ||||||||||||
34 | ||||||||||||
35 | ||||||||||||
36 | ||||||||||||
37 | ||||||||||||
38 | ||||||||||||
39 | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D8 | D8 | =UPPER(IF(B8="",B9,B8)) |
D9 | D9 | =IF(B11="",B10,IF(B9="",B11,"No Condition")) |
D10 | D10 | =LET(s,FILTER(B8:B41,LEN(B8:B41)=MAX(LEN(B8:B41))),LEFT(s,FIND("m",s)-1)+0) |
D11 | D11 | =LET(s,FILTER(B9:B41,LEN(B9:B41)=MAX(LEN(B8:B41))),MID(s,SEARCH("R",s,1)+1,2)+0) |
D12 | D12 | =IF(ISERROR(F30),IF(ISERROR(G33="MDN HCP"),IF(ISNUMBER(G37),G33,IF(LEFT(G37,2)="BM",G37,IF(J37=1,G37,"BM" &G37))),G33),"Class "&G37) |
D13 | D13 | =MIN(FILTER(B15:B41,ISNUMBER(B15:B41))) |