Question description:
This user has given permission to use the problem statement for this
blog.
Example: If a loan closes between October 1st & 15th, the first mortgage payment is due November 1st. If it closes between October 16th & 31st, the first payment is due December 1st.
Solved by S. S. in 15 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
20/09/2018 - 01:02
Hi, I understand that you need help in setting up the due date depending on when the loan closes, right?
User
20/09/2018 - 01:03
yes
Excelchat Expert
20/09/2018 - 01:03
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
20/09/2018 - 01:03
I may have a few quick question to help me fully understand your requirement.
Excelchat Expert
20/09/2018 - 01:03
Are you able to share your file so we can work on it?
User
20/09/2018 - 01:04
any excel sheet would work
Excelchat Expert
20/09/2018 - 01:05
So I can use any columns an any cell?
User
20/09/2018 - 01:05
sure
Excelchat Expert
20/09/2018 - 01:06
Alright, I'll be working on this locally on my Excel, I will then forward the result to you. Please give me a few minutes.
User
20/09/2018 - 01:06
sure
Excelchat Expert
20/09/2018 - 01:08
Alright, I'll be using a combination of IF and DATE formula for this. I'm nearly done.
User
20/09/2018 - 01:08
i know that is why i am so confused
Excelchat Expert
20/09/2018 - 01:09
Here's the file with the formula in column B.
[Uploaded an Excel file]
Excelchat Expert
20/09/2018 - 01:09
=IF(DAY(A2)<=15,DATE(YEAR(A2),MONTH(A2)+1,1),DATE(YEAR(A2),MONTH(A2)+2,1))
Excelchat Expert
20/09/2018 - 01:09
So basically, the formula is checking the day of the date, if it is less than 16 then the due date is the first of the next month.
Excelchat Expert
20/09/2018 - 01:10
Otherwise, it's the first of the second month.
User
20/09/2018 - 01:10
correct
Excelchat Expert
20/09/2018 - 01:10
Alright, if that's the case then the formula in the file I uploaded should be what you need.
User
20/09/2018 - 01:12
not up loading?
User
20/09/2018 - 01:12
can u email?
Excelchat Expert
20/09/2018 - 01:13
I'm afraid that is against policy but let's try something else.
User
20/09/2018 - 01:13
ok
Excelchat Expert
20/09/2018 - 01:13
Please open your Excel.
Excelchat Expert
20/09/2018 - 01:13
Then type a date in cell A2.
Excelchat Expert
20/09/2018 - 01:13
Then paste this formula in cell B2
Excelchat Expert
20/09/2018 - 01:13
=IF(DAY(A2)<=15,DATE(YEAR(A2),MONTH(A2)+1,1),DATE(YEAR(A2),MONTH(A2)+2,1))
User
20/09/2018 - 01:15
there is a number there 43405
Excelchat Expert
20/09/2018 - 01:15
Yes, we just need to format column B to date. Please select column B then change the formatting to date.
Excelchat Expert
20/09/2018 - 01:15
You can do this by selecting column B then pressing CTRL+1 then select date.
User
20/09/2018 - 01:16
got it it works! Awesome!
User
20/09/2018 - 01:16
u r the best. !
Excelchat Expert
20/09/2018 - 01:16
Glad to have helped!
Excelchat Expert
20/09/2018 - 01:16
Would there be anything else that I can help you with regards to the original question?
User
20/09/2018 - 01:16
no! thx U
Excelchat Expert
20/09/2018 - 01:16
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
User
20/09/2018 - 01:16
THX
Excelchat Expert
20/09/2018 - 01:17
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
20/09/2018 - 01:17
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
20/09/2018 - 01:17
yes!
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.
