Excel - IF Function Problem - Expert Solution

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
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
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
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc