Forum Discussion

Brian Thornton's avatar
Brian Thornton
Copper Contributor
Feb 20, 2018

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, and end up with a total cost (which factored in the varying seasonal rates).  The seasonal rates are fairly simple as they are based by the month.  Any feedback or direction is greatly appreciated. 

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Brian,

     

    Please provide us with more info about what you are trying to do!

    What're the seasonal rates?

  • 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

Resources