Query Date calculation with is error?

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Oct 19, 2004

• #2

Hi,

try changing this formula

Ticker: DateDiff("n",[Drop Time],[text78])/60

Click to expand...

to this :

Ticker: Iif ( IsNull ([Drop Time]), 0, DateDiff("n",[Drop Time],[text78])/60 )

I have assumed you want the "ticker" value to be zero if there is no "Drop Time" - if this is not the case, then you can enter the value you want in place of the zero in the middle of the formula.

Please note that I have only solved the issue that you raised in your post and I have not included any tests (e.g. isnull) for the other variables in your formula.

HTH, Andrew.

 

Upvote 0

A

afs24

Board Regular

Joined

Sep 26, 2002

Messages

237

• Oct 22, 2004

• #3

Thanks.
I'm still getting #Error. Below is an example of the calculation you gave me and what is showing up.

Ticker: IIf(IsNull([Drop Time]),0,DateDiff("n",[Drop Time],[text78])/60)

Drop Time text 78 ticker
10/18/2004 11:55:00 PM 10/21/2004 9:21:19 PM 69.43
10/21/2004 9:21:19 PM #Error

In the ticker feild for the 2nd row there should be a 0.

 

Upvote 0

A

afs24

Board Regular

Joined

Sep 26, 2002

Messages

237

• Oct 22, 2004

• #4

Sorry, the example got messed up when I posted. Here is the example in two different formats. I'm sure you get the picture.

Drop Time text 78 ticker
10/18/2004 11:55:00 PM 10/21/2004 9:21:19 PM 69.43
"this is were it's null" 10/21/2004 9:21:19 PM #Error

Drop Time
10/18/2004 11:55:00 PM
"this is were it's null

text 78
10/21/2004 9:21:19 PM
10/21/2004 9:21:19 PM #Error

ticker
69.43
#Error

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Oct 22, 2004

• #5

Hi, I tested the formula using the data you provided and I didn't get the error. Can you check the line that is giving the trouble (by looking at the data in the table) and confirm that [Drop Time] is in fact null and does not contain something else? What is the default value for this variable in the table design?

Andrew,

 

Upvote 0

A

afs24

Board Regular

Joined

Sep 26, 2002

Messages

237

• Oct 22, 2004

• #6

Yes, the field is actually null for Drop Time in the table. The default value is blank in the table. Could th reason why I'm getting the error be that we're changing the null feilds to 0 and then it's trying to do a calculation with a date? But then again, you said you were not getting an error when you tested this. Any other suggestions? thanks

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Oct 22, 2004

• #7

Hi, in this formula :

Ticker: IIf(IsNull([Drop Time]),0,DateDiff("n",[Drop Time],[text78])/60)

... we are testing for the null value outside the datediff calculation. If [Drop Time] is null, it doesn't get into the datediff calculation and instead creates a ticker value of 0. This way, the datediff is only calculated if there is a [Drop Time] value - so I can't see how you would get an error unless something else is causing it.

Disregarding the error for the moment, what is the format of the [text78] variable? Mine was also formatted to "Date/Time" - is yours text or date/time?

HTH, Andrew.

 

Upvote 0

A

afs24

Board Regular

Joined

Sep 26, 2002

Messages

237

• Nov 8, 2004

• #8

This calculation is working for me

IIf(([Drop Time])="","",DateDiff("n",[Drop Time],[PutTime])/60)

However, some fo the calculations are 0 or negative numbers. I don't want any of the zero's or negative numbers to show for my results. For some reason the negative numbers still show? thanks

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 9, 2004

• #9

Hi, in the criteria part of the query, under this particular column, enter the following :

Is Not Null And >0

HTH, Andrew.

 

Upvote 0