excel formula help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

cjclark

Thread Starter
Joined
Sep 30, 2008
Messages
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
 
Joined
Dec 9, 2003
Messages
563
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.
 

Gombot

It's My Birthday!
Joined
Mar 11, 2008
Messages
82
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".
 

Gombot

It's My Birthday!
Joined
Mar 11, 2008
Messages
82
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.
 
Joined
Dec 9, 2003
Messages
563
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
 

Gombot

It's My Birthday!
Joined
Mar 11, 2008
Messages
82
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top