Results 1 to 4 of 4

Thread: ISDATE?

  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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??
    Attached Files Attached Files
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Appreciate the reply.

    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.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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
  •