Specific due dates formula

tgpstyx

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hello Everyone,

I am new here and I really need some help,

I believe my problem is issue but i really dont know the formula,

just need the result

IF date is between 1-15th of the month = result 1st date of the following month
IF date is between 16th-31st of the month = result 16th date of the following month

sample:
(PAYMENT) Date 08/05/2020 = result 09/01/2020
(PAYMENT) Date 08/17/2020 = result 09/16/2020


I have attached all the screenshots.

[IMG alt="Name: 2020-09-10_15h38_49.png
Views: 0
Size: 10.0 KB"]Excel Help Forumsample.xlsx
[IMG alt="Name: 2020-09-10_15h39_56.png
Views: 0
Size: 13.5 KB"]Excel Help Forum
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

tgpstyx

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hello Alany,

Thank you very much! it actually worked!

my next question is (using the example on C1 you provided)

if C1 result is 01/10/2020 then it should also give a value of the day "01" under the column of October.

Meaning, number result of 01 or 16 will appear on the column of said result month.

Value will base on what month then which day of that month

sample:
(PAYMENT) Date 01/09/2020 = result 01/10/2020 = 01 (under month October)
(PAYMENT) Date 16/10/2020 = result 16/11/2020 = 16 (under month of November)




see screenshot for reference

1599758602022.png




Apologies, Ihope you get my question here since i cant really explain it very clearly and seems very complicated,

Thank you very much
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Column C has the month hidden behind, if you don't need the month at all can use the simpler formula ib Column D

Book1
ABCD
101/09/202011
220/08/20201616
325/08/20201616
420/08/20201616
529/08/20201616
603/09/202011
724/08/20201616
801/09/202011
926/08/20201616
1002/09/202011
Sheet1
Cell Formulas
RangeFormula
C1:C10C1=DAY(IF(DAY(A1)>15,EOMONTH(A1,0)+16,EOMONTH(A1,0)+1))
D1:D10D1=IF(DAY(A1)<16,1,16)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Cross posted
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Please supply links to any other site where you have asked this question.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,114,419
Messages
5,547,821
Members
410,813
Latest member
Vhinzvirgo
Top