Formula to get the upcoming pay date

[lucid65]

Hi,

We are trying to get the upcoming payment date of our client.

We only have their 1st payment date and the frequency of their payments.

Can anyone help me get a formula to detect the upcoming payment date for them?

For example, we have a client who's pay date was on the 1st of March 2020 and he's getting paid every 2 weeks. We would like to know when would be his next pay for the month of May 2021.

Thank you so much in advance to anyone who can help me.

[bebo021999]

With A1 is 1st payment date
the 1st pay ment in May 2021 is:

=A1+ROUNDUP((DATE(2021,5,1)-A1)/14,0)*14

[lucid65]

Thank you so much!!

[lucid65]

With A1 is 1st payment date
the 1st pay ment in May 2021 is:

=A1+ROUNDUP((DATE(2021,5,1)-A1)/14,0)*14

Hi, how about if I need to get the same exact date for a client who's paying monthly?

For example we have a data of a client who gets paid every 15th of each month, what formula should I use to get the 15th of this month?

Thank you and sorry I just don't get how to do formulas in excel.

[JohnTopley]

With date in A1 e.g 15/05/2021

then for next month

=DATE(YEAR(A1),MONTH(A1)+1,15)

[bebo021999]

This month (current month of today's date)

=eomonth(today(),-1)+15

Next month:

=eomonth(today(),0)+15

[lucid65]

With date in A1 e.g 15/05/2021

then for next month

=DATE(YEAR(A1),MONTH(A1)+1,15)

Thank you!

What if the date I have is 15/03/2021, how should I use that formula?

[lucid65]

This month (current month of today's date)

=eomonth(today(),-1)+15

Next month:

=eomonth(today(),0)+15

Thank you! so if the client's pay date is every 20th of each month, I just have to change the +15 to +20, is that right?

[JohnTopley]

Yes - just replace 15 with required day of month.

[bebo021999]

Thank you! so if the client's pay date is every 20th of each month, I just have to change the +15 to +20, is that right?

Surely.
BTW, if today is 20th and you want to get today:
=eomonth(today()-(day(today())=20),-1)+20
If you want to get 20th of next month:
=eomonth(today(),-1)+20