Calculate current month payment due date based on start date

[ensee]

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

 

[domex]

Sorry, your explanation is not clear.

 

[bobsan42]

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

 

[ensee]

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]

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]

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]

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

 

[ensee]

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]

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]

glad to help
cheers