# date formula with a condition or rule

#### cgreen

##### Active Member
Example of formula:
Sheet1 A1=DATE(Sheet2!H6,Sheet2!H5,1)

Sheet 2 H6=2016 (or any given year I need to work with)
Sheet 2 H5=2 (or any given month I need to work with)

Issue:
I have the formula copied down 31 lines, I do not want the date populated if it is outside the month on "sheet 2 H5".

Needed:
How do I get the cells to be blank or have N/A for the dates outside the month range from sheet 2 cell H5?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### cyrilbrd

##### Well-known Member
Hi,
in A2 type =IFERROR(IF(A1+1>EOMONTH(\$A\$1,0),"",A1+1),"") copied down till needed

#### skywriter

##### Well-known Member
Example of formula:
Sheet1 A1=DATE(Sheet2!H6,Sheet2!H5,1)

Sheet 2 H6=2016 (or any given year I need to work with)
Sheet 2 H5=2 (or any given month I need to work with)

Issue:
I have the formula copied down 31 lines, I do not want the date populated if it is outside the month on "sheet 2 H5".

Needed:
How do I get the cells to be blank or have N/A for the dates outside the month range from sheet 2 cell H5?

Your post is confusing. If you have the formula in A1 as you show and you copy it down as you say, the formula copied down one cell would be A2=DATE(Sheet2!H7,Sheet2!H6,1) and you said H6 contains 2016, which means that number would be in the formula where the argument for the month would go? What am I missing here?

Replies
3
Views
211
Replies
6
Views
325
Replies
1
Views
225
Replies
3
Views
166
Replies
5
Views
163

1,195,949
Messages
6,012,481
Members
441,701
Latest member
vnkendijs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back