1. Hi,

Got an odd problem, that annoys me

Attached workbook.

I am trying to create a generic formula that I can just copy across columns and rows.

Need to check row 8 for its contents. Can be 1 of 3 (Date, Number, BLANK)

If
1. Date - return functiontype 2
2. Number - return functiontype 1
3. Blank - use only row 7 to retrieve value

Problem is the test to distinguish between Date and number. I've tried ISTEXT and ISNUMBER, but they return the same...
Tried date conversion - gives serial - ISNUMBER = TRUE, so that's no good either.

Problem is that:
col B will always be a specific date 'Today()-1'
C-E will be Years that change at the users discretion.
G-I this part requires no argument in row 8.

Any suggestions how to make this check??

2. [quote name='Henrik Ryberg' post='764793' date='11-Mar-2009 08:57']Hi,

Got an odd problem, that annoys me

Attached workbook.

I am trying to create a generic formula that I can just copy across columns and rows.

Need to check row 8 for its contents. Can be 1 of 3 (Date, Number, BLANK)

If
1. Date - return functiontype 2
2. Number - return functiontype 1
3. Blank - use only row 7 to retrieve value

Problem is the test to distinguish between Date and number. I've tried ISTEXT and ISNUMBER, but they return the same...
Tried date conversion - gives serial - ISNUMBER = TRUE, so that's no good either.
=
Problem is that:
col B will always be a specific date 'Today()-1'
C-E will be Years that change at the users discretion.
G-I this part requires no argument in row 8.

Any suggestions how to make this check??[/quote]

If you just want to determine if you have a number or date you could try the Cell function.
=Cell("format",A1) will retreive the format for the cell. As long as you workbook uses Date format for all the data that is a date and number formats for numbers (and if possible general for blank cells) Excel should be able to get the answer.
The down side to Cell is any data with the wrong format will cause your Cell formula to return an improper result.

Hope this Helps

Tom Duthie

Part of the problem is as you state that Cell gives an incorrect indication as the "number" items often will have the 'General' format applied. In this case I can't rely on the user to remember to set the proper format.

I'm considering all sorts of obscure workarounds, but trust that there must be a more efficient way round this kind of problem.

4. See the recent thread How do I check 4 a date?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•