Rental Days

[Papi]

I am working on a workbook that looks at rental days. If there is an end date the formulas needs to show how many days for the particular month i.e. if Jan 4 is the end date then January would show 4 days and none for February, March etc.

Where I am stumped is shutting down the days when there is no end date and the rental is outstanding and keeps counting. So, if it is February 20th, without an end date, then there should be 20 days for that month and none in March etc. On Februuary 21 it would show 21 days and again, none for March etc.

Any help would be appreciated.

Rental Days.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	StartDate	EndDate	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr
2	22-Aug-10		22	30	31	30	31	31	28	31	30
3	22-Aug-10	4-Jan-11	22	30	31	30	31	4	-	-	-
4	16-Aug-10	20-Feb-11	16	30	31	30	31	31	20	-	-
Strad

 

[shg]

See the example on the second tab of http://www.box.net/shared/7xm2kt408n

 

[Papi]

Hello ssg4421,

Wow, thanks for that. I have to say that I am very impressed with how clean and mean your formulas are but I do not know them and therefore am not sure how to change them to reflect the needed changes as noted below.

The only thing that it needs to do is not show data past today(). That is to say that anything past TODAY and or next month (if there is no end date) does not show until that day comes into play. I changed the date in your cell F2 to reflect January 1, 2011. Cell F5 would show 31 and If today was February 20th then I would look for 20 in cell G5 and zero in cell H5, I5, J5 etc.

 

[shg]

I added another example that may do what you want -- same link: Allocate by Month

The formulas just use named ranges to make them more understandable (to me, anyway). Do Insert > Name > Define (or use Name Manager) to see how they are defined.

 

[Papi]

Hello shg4421,

That is a brilliant worksheet and hits the nail on the head. I agree with naming ranges in order to help them become more understandable. That also works for me. However, how you do it is slightly different than how I do that so will study your style as it works great!

I will continue to study your workbook as it approaches how I have done this type of work for many years at a whole new angle. Much shorter, yet it appears it will do things in a far better way. If you do not mind, I may return to ask a few questions, if I cannot figure it out for myself.

Thanks for taking your time to solve this matter. I really appreciate this!

Best wishes,

Alan

 

[shg]

You're welcome, Alan.