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....
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
=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,343

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,343

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,662
Messages
5,512,680
Members
408,909
Latest member
Burnrose

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top