+ Reply to Thread
Results 1 to 3 of 3

Microsoft Date & Time Picker Control - Linked Cell Query - Can anyone help?

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Pretoria
    MS-Off Ver
    Excel Latest Version
    Posts
    11

    Microsoft Date & Time Picker Control - Linked Cell Query - Can anyone help?

    Hi All, I created a standard DatePicker control on a worksheet and used the “linked cell” attribute in properties to link the output to a specific cell.
    This works well but my problem is that the value returned to the cell is a “string” value and I need it to be a numeric date value.
    To explain what I am trying to do; with the DatePicker I pick a date and place the value in a cell with a label above it called “Expiry Date”. I want to compare the value in the linked cell to another cell that contains the “=TODAY()” value. If the value of the “Expiry Date” is bigger than the value of “=TODAY()” then do nothing but if the value is less than “=TODAY()” then pop up a message informing the user that the Items’ Expiry Date has been reached.
    Back to my problem, the DatePicker returns a string value that I cannot numerically compare with the “=TODAY()” value. – How can I solve this, any help will be appreciated.
    (PS. I can be low tech and just ask them to type in a date but what's the fun in that :-))

    Attached is a screen snippet of the worksheet:
    ExcelCapture001.PNG

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Microsoft Date & Time Picker Control - Linked Cell Query - Can anyone help?

    In your date comparison formula, you could use the DATEVALUE function to convert the string date to a serial date.

    Example:

    A1 has =TODAY()
    B1 has your text date

    =IF(A1<DATEVALUE(B1), "","Expired")

    A formula like this could be use with the Data Validation feature.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Pretoria
    MS-Off Ver
    Excel Latest Version
    Posts
    11

    Re: Microsoft Date & Time Picker Control - Linked Cell Query - Can anyone help?

    AlphaFrog you're a star, thanks a lot, works bril :-)
    As simple as that and I've been bashing my head over this for a couple of days :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1