+ Reply to Thread
Results 1 to 7 of 7

#NUM! Error when date is derived from reference

  1. #1
    Registered User
    Join Date
    04-04-2019
    Location
    Oklahoma City, Oklahoma, USA
    MS-Off Ver
    365 @home / Pro Plus 2016 @work
    Posts
    3

    #NUM! Error when date is derived from reference

    Hello.

    I am working with relatively simple formulas to determine holidays based on the year.

    When I type the year into the cell, the formula works fine, but, when I enter a reference into the cell to derive the year, the formula results with a #NUM! error.

    EXAMPLE-1: When I type the year (e.g. 2019) into $Y$3, the formula '=DATE(Y3,1,1)' in $Z$3 returns 'January 1, 2019'. Works fine.

    EXAMPLE-2: '=MONDAY!$H$1' in $Y$3 returns the year based on the date in the referenced cell. However, now '=DATE(Y3,1,1)' in $Z$3 results with a #NUM! error.

    I'm stumped and any help would be greatly appreciated.

    Thanks!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,721

    Re: #NUM! Error when date is derived from reference

    what is in =MONDAY!$H$1? when I use your formula and refer it to another tab that has 2019 in a cell it still returns what you want.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: #NUM! Error when date is derived from reference

    Whats in MONDAY!H1

    Specifically what value?

    The value in MONDAY!H1 could be too large, or too small.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,223

    Re: #NUM! Error when date is derived from reference

    Quote Originally Posted by PerryPhrases View Post
    EXAMPLE-2: '=MONDAY!$H$1' in $Y$3 returns the year based on the date in the referenced cell. However, now '=DATE(Y3,1,1)' in $Z$3 results with a #NUM! error.
    I am going to guess that MONDAY!$H$1 is a date, and Y3 displays a year but the actual value is a full date. When you select MONDAY!$H$1, what do you see in the formula bar? If the cell shows 2019, but the formula bar shows 4/5/2019, then your formula needs to be

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  5. #5
    Registered User
    Join Date
    04-04-2019
    Location
    Oklahoma City, Oklahoma, USA
    MS-Off Ver
    365 @home / Pro Plus 2016 @work
    Posts
    3

    Re: #NUM! Error when date is derived from reference

    The value in MONDAY!$H$1 is a date e.g. '04-01-2019'

  6. #6
    Registered User
    Join Date
    04-04-2019
    Location
    Oklahoma City, Oklahoma, USA
    MS-Off Ver
    365 @home / Pro Plus 2016 @work
    Posts
    3

    Re: #NUM! Error when date is derived from reference

    That's it! Thank you 6StringJazzer! (great handle btw)

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,223

    Re: #NUM! Error when date is derived from reference

    Glad to help, and thanks for the rep!

    I am a jazz guitarist who masquerades as an IT manager so I can pay the bills.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. unable to get match property error in userform derived index match
    By alexcrofut in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2015, 09:21 PM
  2. Macro to derived results separately for two separate reference
    By sakib in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2015, 02:28 PM
  3. Can cell reference inside formula be derived from another cell?
    By kmarie630 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-07-2014, 01:52 PM
  4. VBA Date Reference to Date Then Insert Row Based On Cell Reference
    By surfstar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2014, 11:21 AM
  5. [SOLVED] Getting #Value! Error rather than value in cells derived from formula includng blank cell
    By Securitysports in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-03-2013, 12:21 AM
  6. Replies: 1
    Last Post: 04-30-2012, 12:07 PM
  7. conditional sum derived from a date interval
    By tghcogo in forum Excel General
    Replies: 12
    Last Post: 02-22-2010, 03:16 PM

Tags for this Thread

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