Forum Discussion
Brian Thornton
Feb 20, 2018Copper Contributor
Date Calculator / Seasonal rates
Hello. I am trying to figure out how to make a simple calculator for my apartment that I rent out. The rent amount is seasonal. I would like to be able to just enter a start date and an end date, a...
vijaykumar shetye
Aug 14, 2018Brass Contributor
Spreadsheet Excellence
Dear Brian Thornton,
You may use the formula given below to calculate the total rent.
=SUM(IFERROR(LOOKUP(IF(E2-D2+1>=ROW(1:1000),ROW(1:1000)+D2-1,0),$A$2:$A$13,$B$2:$B$13),0))
The formula is to be entered as an Array Formula, using Control + Shift + Enter, instead of Enter.
The Names of the Month with Year are in cells A2 to A13.
The corresponding data for Price per day is in cells B2 to B13.
The Start Date is in cell D2.
The End Date is in Cell E2.
The Formula is presently in cell F2.
You may change the cell references as required.
Do let me know if this is what you want to do.
Vijaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa, India