Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 6 of 6 Posts

·
Registered
Joined
·
1 Posts
Discussion Starter · #1 ·
need help with a formula... I want the results to be in column AA

If V6 is blank I want the text in aa to read Booking? , if V6 has a date in it I want the text in aa to read Sailed? , if B1-V6>4 I want the text in aa to read Sailed??, if B1-V6>7 I want the text in aa to read Documents?


I have been trying to get this to work and I am not sure if it can be done

Thanks
Chris
 

·
Registered
Joined
·
563 Posts
hi cjclark,

First, you can't test for a cell holding a date in the spreedsheet, you have to add a Function in a VB module and then use that in your spreedsheet:-

Function DateTest(d As Range) As Boolean
DateTest = IsDate(d.Value)
End Function


Next, I'm not sure what you wanted if B1 did not contain a Date, so I've catered for that also,
and the result for the date difference >4 is the same as V6 = a date (i.e. "Sailed", so I've ignored that,
then there's a catch 22: you can't test for a Date Difference until you know you have a Date; if you test for the Date first it will always come out 'Sailed'.
I presume V6 could contain a value other than a date?
so here's my offering in AA:-

=IF(V6="","Booking?",IF(NOT(datetest(V6)),"NO V6 DATE",IF(NOT(datetest(B1)),"NO B1 DATE",IF((B1-V6)>7,"Documents",IF(datetest(V6),"Sailed?")))))


Notes: is V6 a fixed reference, i.e its the same for all rows down AA, if so then use $V$6 in the above instead.
V6="" is different to V6=" ", the former is an empty cell, the latter is blank.
 

·
Registered
Joined
·
82 Posts
You can test for a date in a roundabout manner using the CELL worksheet function.

=CELL("format",A1) will return "Dx" where x is a number from 1 to 5 if A1 is formatted using a built-in date format.

For example, a date format of "10-Sep-64" returns "D1". Whereas a date format "10-Sep" returns "D2".
 

·
Registered
Joined
·
82 Posts
So one way to accomplish your goal is a formula something like this:


=IF(ISBLANK(V6),"Booking?",IF(CELL("format",V6)="D2",IF(B1-V6>4,IF(B1-V6>7,"Documents?","Sailed??"),"Sailed?")))


This assumes a date in B1, A date (formatted like "10-Sep) or nothing in V6, and the formula itself in AA.

If you are using a different date format, use =CELL("format",V6) to identify the appropriate "D-Code" to use in the Format function above.
 

·
Registered
Joined
·
563 Posts
hi both,

using the 'Cell format' does not test if a cell contains a date; it tests if a cell is Formatted to display a Date.

So a cell so formatted but containing say 'XXX' will still return a D1 to D5 result.

The only way to test for an actual dates is as I said via a vb function
lol
Hew
 

·
Registered
Joined
·
82 Posts
Technically that's correct, and as always garbage-in, garbage-out.

However, even though "xxx" will still return a "D-value" (if the user puts garbage in) the evaluated result will be "#VALUE!" (unless an error handler is added as you appropriately included in your function). So we at least get an indication of GIGO, and do not get a false "Booking", "Documents", etc.


It's at least a work-around for people not comfortable creating new functions in VB modules.
 
1 - 6 of 6 Posts
Status
Not open for further replies.
Top