Formula for days lapsed in current month with other variables to consider.

[jondon]

Needed -
Column S will show how many days this month something was on rent starting with cell S4. Cell K4 is the rental start date. Cell M4 is rental stop date (which could have no data if unit is still on rent) If the start date is before the first of the current month, I want the formula to only figure from the 1st day of current month to the stop date (M4) or to (today) if it still on rent. I also want to keep the cells in column S blank until K4 has data.

Other info
Cell AH4 has begining of current month with this formula =TODAY()-DAY(TODAY())+1
Cell AI4 has this formula =TODAY()

Thanks for any help.

[zbor]

Do you mean this: =MIN(M4,TODAY())-DATE(YEAR(TODAY()),MONTH(TODAY()),1)

[Canuck Chuck]

I don't think that would account for rentals that started this month. Adding a modification for that:

=MIN(M4,TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),K4)

[jondon]

Almost, It didnt take into consideration that the star date (K4) came after the first of the month. Also I would like to drag the formula down but leave the cells empty until I put data in K column.

[jondon]

Th second formula worked great!!! Now can I leave cells blank.. THANKS

[Canuck Chuck]

Modified to only show if K has a value:

=IF(K4,MIN(M4,TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),K4),"")

[jondon]

Works Perfect!!! Thank you very much!

[jondon]

Need a little more help with this. Cell S4 is still calculating. I show -13 days on rent this month when the off rent day is 12/19/11. Is there a fix for that.

equipment sheet final.xls

[zbor]

Maybe this can solve it:

=IF(MONTH(K4)=MONTH(TODAY()), MIN(M4, TODAY())-MAX(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), K4), "")

But now is the question: can rents go in the next month or only in current month?

[jondon]

Almost there. When I drag it down, all the empty rows show rent. I can live with that but would like to leave all cells in a row blank until data is entered. I really appreciate the time you guys have invested in helping me.

If I put on rent 11/1/11, monthly revenue shows "#VALUE!"

[jondon]

Is there a way to make the row move to sheet 2 at the end of month if data is in column "O"? If so the previous formula would work fine.