Assigned Specific Date

prestigemnl

New Member
Joined
Sep 3, 2014
Messages
5
I'm working on a Date of Payment Schedule but don't know the formula for this:

Reservation Date: Start of DP:
1st - 7th every 7th of the month
8th - 15th every 15th of the month
16th - 22nd every 22nd of the month
23rd - 30th/31st every 30th of the month (or 28th if February)

will refer as of =today()

Hope anyone can help me figure this out.

Many thanks. :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Latchmaker

Active Member
Joined
Sep 30, 2005
Messages
308
=if(day(today())<8,today()+sum(7-day(today())),if(day(today())<16,today()+sum(15-day(today())),if(day(today())<23,today()+sum(22-day(today())),if(month(today())=2,today()+sum(28-day(today())),today()+sum(30-day(today()))))))
 

Latchmaker

Active Member
Joined
Sep 30, 2005
Messages
308
You can add this formula in the cell below the one above and fill down to show the future schedule dates
You will need to change the reference of B2 to reference the cell that you place the formula from above in.
If you place the formula above in B2 then paste this one in B3 you will not need to change anything.
=DATEVALUE(CONCATENATE(IF(MONTH(B2)=12,1,MONTH(B2)+1),"/",DAY(B2),"/",IF(MONTH(B2)=12,YEAR(B2)+1,YEAR(B2))))
 

prestigemnl

New Member
Joined
Sep 3, 2014
Messages
5

ADVERTISEMENT

Another question.... for example i'd like to add how many months for the DP, what will i change in the formula?
 

prestigemnl

New Member
Joined
Sep 3, 2014
Messages
5
hi again!
i have a bit of a problem about the formula, whenever i input month of january the concatenate formula appears #Value. and also when i input month of february, the concatenate also follows the end day of february that is the 28th. it suppose to be march 30th. hope you could help me.... thanks =)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top