help for date ranges

richardcdahlgren

Board Regular
Joined
Oct 16, 2008
Messages
74
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
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:

jai9

Active Member
Joined
Jul 20, 2012
Messages
325
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

Board Regular
Joined
Oct 16, 2008
Messages
74
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

Active Member
Joined
Jul 20, 2012
Messages
325

ADVERTISEMENT

Maybe you can attach some data and desired output.

Jai
 

richardcdahlgren

Board Regular
Joined
Oct 16, 2008
Messages
74
Projects List tab has following data:
Column B Column C Column D Column E Column F Column G Column H
Project NumberProject NameLocationAssigned toStart DateProposed Finish DateOn Schedule
Z1000Field TestWOKR.Dahlgren1/3/20131/5/2013yes

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

jai9

Active Member
Joined
Jul 20, 2012
Messages
325

ADVERTISEMENT

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

Well-known Member
Joined
Mar 6, 2013
Messages
769
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

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
Top