Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Test cell content for data-type!! (Excel 2000>)

    Hi,
    In VBA there is a function called ISDATE(). This is not available in Excel!!!

    I have been playing with a spreadsheet to test for data-type. This query originated from a sheet containing a INDEX(MATCH(),MATCH()) type nested function to do a two way lookup of data on the sheet. Based on criteria, the resulting value returned could be a text value, or a Salary or a Date. I was trying to determine the type so I could format the cell as needed to show Salary or the dd-mmm-yyyy date format if a date!!

    I have a measure of success, except for different date types!

    Is there a better (more efficient way) of dealing with this~!!

    Steve - How about a custom function!!! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test cell content for data-type!! (Excel 2000>)

    A very simple custom function:

    Public Function IsADate(val) As Boolean
    IsADate = IsDate(val)
    End Function

    Use like this:

    =IsADate(A5)

    or

    =IF(IsADate(A5),"Yes! It's a date!","No date!")

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test cell content for data-type!! (Excel 2000>)

    Tx!! (As simple as that eh!!)

    I customised the rest of the function to do the job!!! It works like a dream!:
    =IF(IsADate(C2),"Date",IF(AND(ISNUMBER(C2),NOT(IsA Date(C2))),"Salary","Text"))

    Tx
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test cell content for data-type!! (Excel 2000>)

    One "ptential problem" is that a "date" is only a date if the cell is formatted as a date.

    Any valid number in a cell formatted as a date will be considered a date. That same number in a cell not formatted as a date, will not be considered a date.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test cell content for data-type!! (Excel 2000>)

    Thats ok! Where I'm building the calc it will not have formatting!
    Tx for the warning though!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test cell content for data-type!! (Excel 2000>)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>If it will not have formatting, it will not be a date...

    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test cell content for data-type!! (Excel 2000>)

    I see your point!

    Can you help Steve!
    Regards,
    Rudi

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test cell content for data-type!! (Excel 2000>)

    How about in L4:
    <pre>=IF(ISNUMBER(SEARCH("date",K3)),"Date",IF(K3= "Salary","Salary","Text"))</pre>


    If you use in K4:

    =TEXT(INDEX(A1:H22,MATCH(K2,A:A,0),MATCH(K3,1:1,0) ),IF(ISNUMBER(SEARCH("date",K3)),"m/d/yyyy",IF(K3="Salary","R #,##0","@")))

    You can format the cell as desired....


    Steve

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test cell content for data-type!! (Excel 2000>)

    I see your approach now...

    I think I got tunnel vision with my attempts to use the CELL() function to return details of the data type of a cell.
    Thx for your help and solution!
    Cheers
    Regards,
    Rudi

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test cell content for data-type!! (Excel 2000>)

    Glad I could help. The real "flaw" in your "logic" was that, unlike a program like access, a date in excel is just a number. Any number from 0 to 2958465 (12/31/9999) will be considered a date if the cell is formatted as a "date".
    If the cell is formatted as date but contains text, an error, or a number is outside this range it is not a date.
    If the cell is not formatted as a date, no matter what it contains, it is not a date.

    My "logic" just used what the the column heading chosen was to define what the cell is.

    [An alternate approach (perhaps more general) is to add a row and for each column, list what format you want to use. Then instead of the IF construction, read the "format" with the index/match or vlookup. Then you just have to change the "format text" in the table to redefine the display, without having to change your formula. It is also not limited to 7 different numeric formats as the IFs will be...]

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test cell content for data-type!! (Excel 2000>)

    Tx Steve. Some nice advice in there!
    Cheers for the solution!
    Regards,
    Rudi

Posting Permissions

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