formula

[sue.Caradonna]

I have a database that has 2 columns for "Start Date" and "Birth Date". These columns have been fomated to the date format.

I have a report with a box with the following formula entered

=IF(StartDate="","",VLOOKUP($S$1,Database,11,FALSE))

that puts the "start" date of a person when the user selects a person'a name from a drop down box that's linked to $S$1.

If there is a date in the database, the the date is shown O.K. However, if there is no date entered into the database, they the above formula returns "0-Jan-00" as a default. I've tried in the above formula to say that if there is nothing in the database then put nothing, but it doesn't seem to work as it always puts "0-Jan-00" default.

How can I fix this?

 

[tactps]

Change it to:
=IF(LEN(VLOOKUP(StartDate,G12:J22,3,FALSE)<2),"",VLOOKUP(StartDate,G12:J22,3,FALSE))
It is finding a blank with the lookup and then returning "" in date format which is 0-Jan-00.

Alternatively,change the format of the cell to:
[>0]d-mm-yy;

 

[Brian from Maui]

I have a database that has 2 columns for "Start Date" and "Birth Date". These columns have been fomated to the date format.

I have a report with a box with the following formula entered

=IF(StartDate="","",VLOOKUP($S$1,Database,11,FALSE))

that puts the "start" date of a person when the user selects a person'a name from a drop down box that's linked to $S$1.

If there is a date in the database, the the date is shown O.K. However, if there is no date entered into the database, they the above formula returns "0-Jan-00" as a default. I've tried in the above formula to say that if there is nothing in the database then put nothing, but it doesn't seem to work as it always puts "0-Jan-00" default.

How can I fix this?

A simple fix would be to enter some text in the empty cell in the Birth Date column signifying no Birth date has been entered.