Calculate current month payment due date based on start date

ensee

New Member
Joined
Jul 28, 2010
Messages
8
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.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
=if(month(D1)>MONTH(TODAY()),"Not Due",some_due_date)
is it what you're looking for?
 

ensee

New Member
Joined
Jul 28, 2010
Messages
8
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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

some_due_date was left for you to decide what to put there :)
but after more information was given - replace it with:
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
Joined
Jul 28, 2010
Messages
8
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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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 :confused:

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
Joined
Jul 28, 2010
Messages
8
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
Joined
Jul 28, 2010
Messages
8
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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
glad to help :biggrin:
cheers
 
Master Excel Bundle

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.

Forum statistics

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

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