Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date & value (2007)

    I have a named range that I am trying to join to a piece of text that do a simple calculation using =VALUE. However, I can't get it to work. Any advice - see attached.
    Attached Files Attached Files

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

    Re: Date & value (2007)

    Perhaps you meant =VALUE("1/2/"&(targetyear-21)). You'll have to format the result as a date.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    I wish. When I do this, it produces #VALUE! so there is a mismatch somewhere. I attached the result
    Attached Files Attached Files

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

    Re: Date & value (2007)

    If I select cell A2, press F2 then Enter, the value is recalculated correctly.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    Hmmmm. Well, it does not work for me. Wish I knew where, in Excel 2007, to find add-ins that are loaded - I may be missing some component that allows this to work.

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

    Re: Date & value (2007)

    I don't have Excel 2007 yet, but according to Excel COM add-ins and Automation add-ins you can use Office button > Excel Options > Add-Ins > Manage > Excel Add-Ins or COM Add-Ins > Go to see which add-ins you have.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    Don - I have 2007. The recalculate didn't work for me either. However, I selected A2 as if to edit and then reentered the formula, it worked. Why? I have no idea.

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    Interesting. I have tried the same to no avail.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    Edited by HansV to shrink screenshot - please don't post images larger than 640x480.

    here is a screen shot of my add-ins. The picture shows all add-ins. Looks complete to me... but what do I know?
    Attached Images Attached Images

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

    Re: Date & value (2007)

    I don't see any suspect ones among them.

    I noticed one odd thing in your workbook: the date of birth is left-aligned although the number format is a date format and the horizontal alignment is general. Do you have any idea why that is? Was the worksheet originally created in or by another application, or was the date of birth pasted into the worksheet from another application?

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

    Re: Date & value (2007)

    What happens if you use

    =DATE(targetyear-21,1,2)

    as formula in cell A2?

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    I have no idea. This workbook is something I created for the purpose of uploading to the Lounge. Nothing copied or pasted.

  13. #13
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    Yes, this works perfectly.

  14. #14
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & value (2007)

    I did encounter something interesting that may be related. I downloaded and attempted to install an add-in (Excel Password Remover 2008) and i was notified that "This workbook has lost its VBA project, ActiveX controls and any other programmability-related features."

    I have been trying to learn how to fix this and have found a couple of MS articles (Excel features unavailable if VBA disabled andSecurity policies and settings in the 2007 Office system) but still can't figure out how to apply the information. Specifically, how to enable VBA.

Posting Permissions

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