Checking date ranges

[gezzer1520]

I am trying to create a spreadsheet which contains a list of bills, such as Gas, Electric, telephone etc., each of which is payable on a set day each month. The spreadsheet lists these bills in the first column, and then has a column for each week of the year, with Monday as the first day of the week. I now want to take the list of due dates e.g. Gas due 4th each month, electric due 20th each month etc and use this to populate the sheet showing what bills are due in which weeks. It seems simple, but I am running into problems. I put the due dates (which is just a number) in a table on sheet 2, and use VLOOKUP to match the bill description from the main spreadsheet to find the relevant payment in the table. Within the VLOOKUP it takes the start day of the week (Monday) and uses this date and this date plus 6 days, to give Monday to Sunday, and checks to see if the due date is between these two dates. So, for example, due date of 5 falls between week start and end dates of 4th and 10th so the payment amount would populate the sheet correctly. The problem occurs when Monday to the following Sunday falls over a month end. In the above example, 5 is greater than 4 AND less than 10 so it works. However with Monday 28th as a start date, we get something like 28th and 4th as the week start and end dates. In that case 30th, which is between those two dates would fail, as 30 is greater than 28 but also needs to be less than 4, which it is not. I have tried different date formats etc., but it seems that the due date (just a number)

 

[123rickfear]

formula doesn't want to display. I'll pm you.

 

[gezzer1520]

Thanks Rick, but it only works if the due date is a proper date, so needs to be 8/1/5, 8/2/15, 8/3/15 etc. If I list the date individually in each month, like that, it works fine. I want to just list the payment amount and 8 to signify the 8th of each month. That means comparing a numeric (the due date) to two actual dates, which is where the problem is. Using DAY function, I can extract the day value from the two dates, so that I am now comparing just numbers, but at month end, the end date is lower than the start date, e.g. 28 and 4 whereas during the month, its the other way round e.g. 20 and 26. Hence the > and < doesn't work at any month end.

 

[123rickfear]

how about this?

=IF(AND((MONTH(C$1)>MONTH(B$1)),$A2>DAY(B$1)),$A2,IF(AND(DAY(B$1)<$A2,DAY(C$1)>$A2),$A2,""))

 

[gezzer1520]

That's very close. It sorts out the end of the month, where it works perfectly, but not in the first week of the month, where it produces no results. I think I am maybe asking too much here. This formula is going to get overly complicated especially when I add in the VLOOKUP. I think I may have to concede defeat and do it manually. I do really appreciate your help though.

how about this?

=IF(AND((MONTH(C$1)>MONTH(B$1)),$A2>DAY(B$1)),$A2,IF(AND(DAY(B$1)<$A2,DAY(C$1)>$A2),$A2,""))

 

[123rickfear]

=IF(DAY(B$1)=$A2,$A2,IF(AND((MONTH(C$1)>MONTH(B$1)),$A2>DAY(B$1)),$A2,IF(AND((MONTH(C$1)>MONTH(B$1)),$A2<DAY(C$1)),$A2,IF(AND(DAY(B$1)<$A2,DAY(C$1)>$A2),$A2,""))))

one last shot!

 

[gezzer1520]

<day(c$1)),$a2,if(and(day(b$1)<$a2,day(c$1)>Thanks for that, but I'm afraid it doesn't work properly, the results seem pretty random. I think I will have to think of an entirely different way of approaching this. It' frustrating that it seems simple on the face of it, but clearly isn't. Thanks again for your help. </day(c$1)),$a2,if(and(day(b$1)<$a2,day(c$1)>

 

[gezzer1520]

Just for info, I did finally manage to get this working. It's along the lines suggested by 123rickfear. =IF(AND((MONTH(C$1)>MONTH(B$1)),$A2<=DAY(C$1)),$A2,IF(AND((MONTH(C$1)>MONTH(B$1)),$A2>=DAY(B$1)),$A2,IF(AND(DAY(B$1)<=$A2,DAY(C$1)>$A2),$A2,"")))
Bit convoluted, but it does the trick. Thanks again.