# #NUM! Error when date is derived from reference

1. ## #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. ## 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.

3. ## 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.

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

Originally Posted by PerryPhrases
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:
`Please Login or Register  to view this content.`

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

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

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

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

7. ## 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.

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

#### 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