# help for date ranges

#### richardcdahlgren

##### Board Regular
I am trying to use an IF formula to return a 1 or 0 if the date ranges are between given dates. I have project name (B3), start date (b7), end date (b8) (5 and 6 on the vlookup) on a separate Projects List tab. I have tried:

=IF(VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$H\$5000,5,FALSE)>=A5<=VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$H\$5000,6,FALSE),1,0)

B2 is the project name on the current tab and I have dates listed in column A from 1/1/13 to 12/31/13. A5 is the start date of this project and A7 is the end date. In Column B I am trying to get a 1 for the dates in between and a 0 for dates before and after.

Any help would be greatly appreciated.

Thank you.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I would try =IF(AND(VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$B\$5000,5,0)>=\$A\$5,VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$B\$5000,6,0)<=\$A\$5),1,0)

Last edited:
Hi,

Try

=IF(AND(VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$H\$5000,5,FALSE)>=A5,VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$H\$5000,6,FALSE)<=A7),1,0)

Jai

These are all very good, but I guess I didn't explain fully. In Column B I want a 1 for every date that is between the dates on the project list. So B5 is the start date and B7 is the end date. I need a 1 for B5, B6 and B7.

Does that make sense? Thanks for the help so far.

Maybe you can attach some data and desired output.

Jai

Projects List tab has following data:
Column B Column C Column D Column E Column F Column G Column H
 Project Number Project Name Location Assigned to Start Date Proposed Finish Date On Schedule Z1000 Field Test WOK R.Dahlgren 1/3/2013 1/5/2013 yes

<tbody>
</tbody>

On my Calendar tab I have dates from 1/1/13 thru 12/31/13 listed vertically in Column A. Column B is for the projects. B2 is Project name. In column B I would like a 1 to show for B5, A5 is 1/3/13, a 1 to show for A6 and A7. A7 is 1/5/13. And zero's for all the other cells in column B starting with B3 (1/1/13)

Does that help?

Last edited:
I am still not clear.

You say column B is for projects. And then in B5 you would like to show 1.

???

Also, what is in column F of tab Project List? Dates?

Jai

I am trying to use an IF formula to return a 1 or 0 if the date ranges are between given dates. I have project name (B3), start date (b7), end date (b8) (5 and 6 on the vlookup) on a separate Projects List tab. I have tried:

=IF(VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$H\$5000,5,FALSE)>=A5<=VLOOKUP(\$B\$2,'Project List'!\$B\$3:\$H\$5000,6,FALSE),1,0)

B2 is the project name on the current tab and I have dates listed in column A from 1/1/13 to 12/31/13. A5 is the start date of this project and A7 is the end date. In Column B I am trying to get a 1 for the dates in between and a 0 for dates before and after.

Any help would be greatly appreciated.

Thank you.
1.Select the range H3:h5000 with the active sell being h3
2. copy and paste this:
=IF(AND(\$F3<=\$A\$5,\$G3>=\$A\$5),1,0)
3.Ctrl + Enter

1.Select the range H3:h5000 with the active sell being h3
2. copy and paste this:
=IF(AND(\$F3<=\$A\$5,\$G3>=\$A\$5),1,0)
please let me know if this works

Replies
8
Views
85
Replies
3
Views
179
Replies
7
Views
142
Replies
3
Views
76
Replies
0
Views
213

1,203,030
Messages
6,053,129
Members
444,640
Latest member
Dramonzo

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