help for date ranges

[richardcdahlgren]

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.

 

[jamtay317]

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)

 

[jai9]

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

 

[richardcdahlgren]

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.

 

[jai9]

Maybe you can attach some data and desired output.

Jai

 

[richardcdahlgren]

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?

 

[jai9]

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

 

[jamtay317]

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

 

[jamtay317]

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