**Question description:**

*This user has given permission to use the problem statement for this
blog.*

I need a forumula that picks between 3 amounts, based on if it is before a set start date, after a set end date, or between the start and end date

Solved by D. W. in 39 mins

This is the chat thread from the real Excelchat help session. It contains no private user
information.

Excelchat Expert
01/08/2018 - 08:14

Hi

Excelchat Expert
01/08/2018 - 08:15

Could you please share the data, if possible

User
01/08/2018 - 08:15

unfortunately I cant

Excelchat Expert
01/08/2018 - 08:15

Okay

User
01/08/2018 - 08:15

I'm trying to create a table based on the revenue a tenant brings in per month

User
01/08/2018 - 08:15

they have a lease start date and a lease end date

Excelchat Expert
01/08/2018 - 08:16

okay

User
01/08/2018 - 08:16

both the start and end date are in the middle of the month, so the rent they would pay would be prorated and not the full monthly amount

User
01/08/2018 - 08:16

the table lists every month for the term. Below the month it needs to return the revenue for that month

Excelchat Expert
01/08/2018 - 08:17

I did not follow completely

User
01/08/2018 - 08:17

all but the 1st and last month would have the full rent, while the the 1st and last would have the pro rated rent

Excelchat Expert
01/08/2018 - 08:17

Could you explain through dummy data in the sheet on th eirght

Excelchat Expert
01/08/2018 - 08:17

*the right

User
01/08/2018 - 08:20

i need to table to look like that, but I need it to calculate those amount based on a formula and not an input

User
01/08/2018 - 08:20

make sense?

Excelchat Expert
01/08/2018 - 08:20

So you want the output as in row 5 and 6

Excelchat Expert
01/08/2018 - 08:21

is my understanding correct?

User
01/08/2018 - 08:21

yes

Excelchat Expert
01/08/2018 - 08:22

give me sometime to work on it

User
01/08/2018 - 08:22

I want it setup to where when someone enters the rent for that unit and the dates of the term, that it calculates the revenue for each month of the term

User
01/08/2018 - 08:24

would it be some sort of choose function, where if you said if the date is <= start date, it returns the 1st month rent, if its between the start and end date it returns the full months rent, if its >= the last month, the if returns the last months rent

Excelchat Expert
01/08/2018 - 08:25

sort of

Excelchat Expert
01/08/2018 - 08:25

Is there a max date and min date for the timeframe that can be entered in Lease start dat e and lease end date

Excelchat Expert
01/08/2018 - 08:26

First of all, need to figure out to get the months between the dates automatically and then if conditions would be used

User
01/08/2018 - 08:27

the min date would be the start date

User
01/08/2018 - 08:27

the max date would be the end date

Excelchat Expert
01/08/2018 - 08:27

okay

Excelchat Expert
01/08/2018 - 08:28

it will take more time and might have to extend the session by 20 min

User
01/08/2018 - 08:29

what is the charge on that?

Excelchat Expert
01/08/2018 - 08:30

nothing

Excelchat Expert
01/08/2018 - 08:30

for complicated problems the session can be extended upto 60 mins

User
01/08/2018 - 08:38

any updates?

Excelchat Expert
01/08/2018 - 08:39

Yeah... almost done got the excel to create automatic months

Excelchat Expert
01/08/2018 - 08:40

now computing rents

Excelchat Expert
01/08/2018 - 08:40

will take 2-3 minutes more

User
01/08/2018 - 08:46

thanks

Excelchat Expert
01/08/2018 - 08:50

Here you go

[Uploaded an Excel file]

Excelchat Expert
01/08/2018 - 08:52

Let me know if this solves your problem

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user
information.*