Calculate Next Payment Date Problem

[benjhardie]

Hello!

I am trying to write a formula that calculates my next billing date for my mobile phone bill. I am charged on the 31st of each month (or the next working day where the 31st non-working day or where there aren't 31 days in the month). My formula (below) returns the correct date but if that date is today then I want it to return today's date and not jump to next month's date.

Please Login or Register  to view this content.

For example, today (21st August) this formula returns the 1st September (because, whilst August has 31 days, the 31st is a bank holiday here in the UK so the next working day is 1st September). However, on the 1st September, I still want this formula to return 1st September to highlight to me that my payment date is today. Then on 2nd September, as my payment has now passed, I want the formula to return the next date (which would be 1st October).

I can't for the life of me work out how to amend my current formula to do want I want so I'd appreciate any help I can get!

Thank you!

[Tsjallie]

May be this will work for you:

Please Login or Register  to view this content.

[benjhardie]

Thank you for this!

I'm afraid that this formula doesn't return the dates I'd expect. When today is a date in August, it returns 31st August but it should return 1st September as 31st August is a bank holiday. In fact, it looks like it's returning dates that are a day earlier than I'd expect for each month. I think it's close to being right though as when the calculated day is today it stays the same until the following day when it returns the next date.

[bebo021999]

I interprete it as following: If working day of "yesterday month-end" is today, this month, else next month

Please Login or Register  to view this content.

Or shorter:

Please Login or Register  to view this content.

[Root_]

See if the following formula works for you:

=WORKDAY(EOMONTH(TODAY(),-(WORKDAY(EOMONTH(TODAY(),-1)-1,1,Public_holidays) >=TODAY()))-1,1,Public_holidays)

^^^ this is a compacted version of a longer formula:

=IF(WORKDAY(EOMONTH(TODAY(),-1)-1,1,Public_holidays) >=TODAY(),WORKDAY(EOMONTH(TODAY(),-1)-1,1,Public_holidays),WORKDAY(EOMONTH(TODAY(),0)-1,1,Public_holidays))

[Tsjallie]

Underestimated some nasty constraints.
This should do yr trick:

Please Login or Register  to view this content.

See also attached workbook to see how the formula developes with different dates for today().