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:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
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:

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
[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:

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
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
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
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:

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,288
Messages
5,485,909
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top