Combining Cdate with Iif in calculated query

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
this is a great board for answers

How would I incorporate CDate into the below query string in order to calculate the “stringed” date value ?

Code:
 Status: IIf( ([Status Cd]<>"COMP",[Status Cd],IIf([expires]< Date(),"Pass Due",IIf([Expires]-Date()<=30,"30 Days",
IIf([Expires]-Date()<=60,"60 Days","IIf([Expires]-Date()<=90,"90 Days","Planned"))))


The “Status column” is a text field
Values are
COMP
Date values from any date calculated from Date()

Resulting into a new column or the existing column
Output desired in the same / new column

Comp
Pass Due
30 Days
60 Days
90 Days
Planned

and of course, thank you
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Normally datevalue() but that can cause problems depending on the type of strings that might be in the field. Is [expires] a date field or a string field? If a string field, what values can it hold besides dates? From your post description I'd say you are using dates in the [status] field but your actual code uses dates in the [expires] field, so it's unclear what's going on.
 
Last edited:
Upvote 0
[PHP messed up the original code

should have been Status field which is a string field

but I cant get the full query statement to post

"Status: IIf([Status]="COMP",[Status],IIf([Status]
<Date(),"R",IIf([Status]-Date()<=30,"30 Days",
IIf([Status]-Date()<=60,"60 Days","90 Days"))))"
 
Last edited:
Upvote 0
Let me re-ask the question

How would I incorporate CDate into the below query string in order to calculate the “stringed” date value ?

Currently Status is a "text field' in order to capture the text COMP and date strings

Code:
[SIZE=1]Status: IIf( ([Status]="COMP","Comp",
IIf([Status]< Date(),"Pass Due",
IIf([Status]-Date()<=30,"30 Days",
IIf([Status]-Date()<=60,"60 Days",
IIf([Status]-Date()<=90,"90 Days","Planned"))))





[/SIZE]

The “Status column” is a text field
Values are
COMP
along with Date values

Resulting into a new column or the existing column
Output desired in the same / new column
with the new calculated values

Comp
Pass Due
30 Days
60 Days
90 Days
Planned

and of course, thank you
 
Upvote 0
This seems to work:
Code:
SELECT IIF(IsDate(Status),Switch(DateValue(Status)<Date(),"Pass Due",DateValue(Status)-Date()<=30,"30 Days",DateValue(Status)-Date()<=60,"60 Days",DateValue(Status)-Date()<=90,"90 Days",1=1,"Planned"),"COMP") as Status
FROM Table11;

The above assumes everything not a date is "COMP" (since that is the only value besides dates).
If you want to guarantee that the value is COMP and not some other invalid value such as COPM then:
Code:
SELECT IIF(IsDate(Status),Switch(DateValue(Status)<Date(),"Pass Due",DateValue(Status)-Date()<=30,"30 Days",DateValue(Status)-Date()<=60,"60 Days",DateValue(Status)-Date()<=90,"90 Days",1=1,"Planned"),IIF(Status="COMP","COMP","ERROR")) as Status
FROM Table11;
 
Last edited:
Upvote 0
xnou your 2nd code will work great for the immediate need -- thank you

just curious how would I insert other "date criteria's" into the code?

Code:
Chart_Status: IIf(IsDate([Status]),Switch(DateValue([Status])<Date(),"Pass Due",
DateValue([Status])-Date()<=30,"30 Days",
DateValue([Status])-Date()<=60,"60 Days",
DateValue([Status])-Date()<=90,"90 Days",
DateValue([Status])-Date()<=120,"120 Days",1=1,"Planned"),IIf([Status]="COMP","COMP","ERROR"))

Never mind -- I got it to work :)

thank you again
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top