date formula with a condition or rule

cgreen

Active Member
Joined
Aug 14, 2002
Messages
293
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi,
in A2 type =IFERROR(IF(A1+1>EOMONTH($A$1,0),"",A1+1),"") copied down till needed
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top