Return 1 or 0 based on Date Range

Originally Posted by MarvinP

Look at the attached with a smaller formula than 6SJ but I needed a helper column. See if this works for you in the attached. I did Column B. I wonder if someone can come up with a shorter formula for Column C?

Clever solution. You really don't need the helper column, though; the references to J can refer to the dates one row higher in B (although the reference to J1 might be an issue).

Oooh, sorry Marvin, I have to take it back. In column B, your solution marks only April and November for 2007, but all the months in between have to be marked too. You have to do some date arithmetic for this problem because of the need to analyze the span for each pair of start and end dates.

6StringJazzer,

Thanks for you help! After I finished modifying the sheet you gave me I noticed I needed more granularity, weeks vice months. I attempted to modify what you did to work with weeks and it looks like everything worked with one exception. When the last day of the week conicides with the first day of use I get a 0 instead of a 1. I tried to figure out why the error was occurring but my attempts to modify the formula create either an error or the same result. In the attached file on the "weeks covered" sheet, row 390 for asset A is an example of there being no tally, 0 vice 1. I am guessing this is something similar to counting the number of days between 2 dates including the start and end date. The only way I could get an accurate number was to add 1 every time. Any help you can give me is greatly appreciated!

Thanks again!

In my monthly version, I used only the date for the first day of the month for all calculations. In the weekly version, you added a column for Week End Date and tried to modify your formula to take that into account. I didn't try to figure out what was wrong with your formula. It was easier to just copy over the formula used for months. My column is the one highlighted in yellow.

If it works for you, just delete your own column for A, then copy over my column. But.

One problem with this kind of copying is that the source data has data in every other column, but your formulas are in adjacent columns. So you can't just copy the formulas into the next column. One trick for doing this kind of copying is to insert a blank column between every column where you are going to put the formulas, then copy the column for A into the desired columns. They will refer to the correct source columns. Now you can delete the extra blank columns and your formulas will still point to the right place.