Calculate current month payment due date based on start date

ensee

New Member
I am trying to calculate a current month payment date based on a start date. The workbook contains 12 spreadsheets (one for each month Jan - Dec), in each sheet Coloumn D contains variable start dates approx 100 entries i.e.
1 Jan 2010
26 Jan 2010
3 Feb 2010
17 March 2010 etc....

Coloumn L needs to show the current month payment due date. For example based on the above data if in June spreadsheet payment dates would be
1 June 2010
26 June 2010
3 June 2010
17 June 2010 etc...

If a Start date falls after the current month then the formulae should return 'Not Due' i.e. if the month sheet is Feb but the start date is 17 March 'Not Due' is returned...

Any help would be greatly appreciated....

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

domex

Board Regular
Sorry, your explanation is not clear.

bobsan42

Well-known Member
=if(month(D1)>MONTH(TODAY()),"Not Due",some_due_date)
is it what you're looking for?

ensee

New Member
bobsan42....
returns ~Name?

presumably because of ,some_due_date

the 'value if false' should equal the day in the current month i.e.
if start date is '26 Feb' then due date in July will be '26 July'
if start date '17 March' then due date in July will '17 July'
if start date 4 Sept the due date in July 'Not Due'

bobsan42

Well-known Member

some_due_date was left for you to decide what to put there
DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(D1))

But be careful - each month has different number of days 28,29,30,31 - this will be a problem, but you have to decide what will be the decision:
for example: Start date: 31.05.2010 => Due date can't be 31.06.2010
this will raise an error. What must be the date then?

ensee

New Member
bobsan42...
Looks like we are almost there !

As per your example the date would revert to the last day of the current month so 31 june would actually be 30 June.

If I put the formulae into March sheet the date due is July ??? not March

Your help is really appreciated !

bobsan42

Well-known Member

OK:
the 'value if false' should equal the day in the current month i.e.
if start date is '26 Feb' then due date in July will be '26 July'
if start date '17 March' then due date in July will '17 July'
if start date 4 Sept the due date in July 'Not Due'
if we shouldn't use TODAY() (which returns the current date) then i assume you want the date corresponding to the month of the sheet - but then:
What tells us which sheet/month we are in?
i am a bit confused now

Suggestion: select a cell or cells (same place in each sheet) far from your data and put some necessary information in them to be used by the formulas:
- month number
- year number
- number of days in the month
- whatever else you think can be useful

Last edited:

ensee

New Member
bobsan42....

Yup thats it....providing I use a reference cell on the sheet it will work...

Thank you so much..... I'll mark this as solved.... !

ensee

New Member
Final formulae which works a treat

=IF(D2>J2,"NOT DUE",DATE(YEAR(D2),MONTH(J2),DAY(D2)))

where J2 = first day of current month

Thanks bobsan42

bobsan42

Well-known Member
cheers

Replies
12
Views
257
Replies
12
Views
379
Replies
5
Views
288
Replies
2
Views
128
Replies
9
Views
147

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,748
Messages
5,833,460
Members
430,209
Latest member

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.

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

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