How do I test for a default time value which is a result of a formula?

[JeffGrant]

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.

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)))

 

[Michael M]

firstly you have surrounded a cell reference in Quotes, so it will only return the text "D13".
But do you want

=IF(D13<"08-01-1900 12:00:00 AM","",D13)

 

[JeffGrant]

Thanks for that Michael, doing too many things at once and typing too fast. The quotes are not present in the actual formula. So I will stand corrected. Cheers.

This formula =IF(D13<"08-01-1900 12:00:00 AM","",D13), continues to return the the 12:00:00AM, when it would be great if I could get a blank in that space.

 

[Michael M]

Ok, why are you using a specific date AND time ??
Could you not restrict cell D13 ro either date OR time ??
Being so specific, I believe will cause a few errors

 

[JeffGrant]

Hi Michael, I am not using a specific date and time. It is an assumed value by Excel that is being pasted from the clipboard. Everything in the range B8:B37 is being pasted in one go.

Then the formula in D13, is creating this default time.

If there is an actual time in the clipboard, as there is future data, the formula simply extracts the time and plonks it in D13. No Drama

If there is no actual time in the clipboard , as there is in past results, the formula is creating this default time of 12:00 AM.

Elsewhere in the workbook, I would prefer not see this 12:00 AM at all, hence the reason, why I am trying to test for it, so I can eliminate it.

Also, I don't think the 12:00 AM is an actual time. I suspect it is being treated as something else

Thanks for your help.

 

[MARK858]

Maybe

=IF(D13=INT(D13),"",D13)

 

[JeffGrant]

Hi Mark 858. That is perfect Hit the nail right on the head.

Thank you

 

[MARK858]

You're welcome.
Just remember that a time is just the decimal part of a number formatted as time, your formula results in the Integer 2 or 2.00 if formatted to 2 decimal places.

The .00 formatted as time would produce 00:00:00 or 12:00:00 AM.
All the formula does is test if D13 is an Integer.