Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Check if a valid date?

  1. #1
    Board Regular Guanjin, Peter's Avatar
    Join Date
    May 2008
    Location
    Island Of Singapore
    Posts
    429
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Check if a valid date?

    I'm currently using the below code to check if it's blank. Actually I wanted to check if it is a valid date. I can't really use:
    =IF(B1=dd-mm-yyyy,TEXT(WEEKDAY(B1),"dddd"),"")
    so any idea?

    Code:
    =IF(B1="","",TEXT(WEEKDAY(B1),"dddd"))
    Falling in love is the only thing that defies the law of gravity

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,923
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Check if a valid date?

    Dates are actually stored as Numbers in Excel (specifically, the number of days since 1/1/1900). So you can use the ISNUMBER function, i.e.

    =IF(ISNUMBER(B1),TEXT(WEEKDAY(B1),"dddd"),"")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular Guanjin, Peter's Avatar
    Join Date
    May 2008
    Location
    Island Of Singapore
    Posts
    429
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a valid date?

    there wasn't a need to specifcy true or false...that's strange.....

    well it works wonders! Thanks!

    It'll work for Vb code too?

    like:
    If Range("b1").Value = "ISNUMBER(B1)"
    Last edited by Guanjin, Peter; May 29th, 2008 at 11:31 PM.
    Falling in love is the only thing that defies the law of gravity

  4. #4
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a valid date?

    If it is a number it is presumed true and if it is not then it is presumed false.

    In if statements =TRUE and =FALSE are not really needed., but you could put:

    =IF(ISNUMBER(B1)=TRUE,TEXT(WEEKDAY(B1),"dddd"),"")

    But it is redundant.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,923
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Check if a valid date?

    It'll work for Vb code too?

    like:
    If Range("b1").Value = "ISNUMBER(B1)"
    Actually, VBA has an ISDATE function that works even better, as it differentiates numbers from dates (presumably by how they are formatted).

    So, in VBA, you can use:
    Code:
     If IsDate(Range("B1")) Then
    ...
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular Guanjin, Peter's Avatar
    Join Date
    May 2008
    Location
    Island Of Singapore
    Posts
    429
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a valid date?

    wow thanks!
    Falling in love is the only thing that defies the law of gravity

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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