Since there are a few hot excel specialists here, maybe someone can give this a go following my years of struggles. I've always had difficulty creating a formula for a complicated date function: what formula (without any VBA ) calculates an interval date from 6mo after a start date but not before Today().

For example: -

Start date: 01/03/2013

End Date: 31/12/2020

Notice period: 6 months

Answer should be 01/09/2016

The trouble is how to increment the start date by the period interval enough times to tip it past today's date for it to be relevant.

Cheers

Pops

For example: -

Start date: 01/03/2013

End Date: 31/12/2020

Notice period: 6 months

Answer should be 01/09/2016

The trouble is how to increment the start date by the period interval enough times to tip it past today's date for it to be relevant.

Cheers

Pops

This /should/ work for your problem

A1 contains 01/03/2013

and the result is 01/09/2016

Edited by:"dai007uk" 16th Aug 20166 months from Today should be approx 16/02/2017

So

where is the formula collecting the "Today" ?

and

are your date cells formatted as Date?

He doesn't want 6 months from today, he needs 6 months from the start date (01/03/2013) but needs the date to be in the present not the past (so every 6 months from the start date, but he is only concerned about the dates that are in the present not the past)

Edited by:"dai007uk" 16th Aug 2016Original Poster

Incredible, in a single IF statement which I didn't think was possible. Problem i have is that the "if true" portion gives the same year as A1 and not in the present year.

Looking through it now as I speak.

Thanks

Pops

Original Poster

Sorry fella, I think I may be wrong (and your suggestion is right). Don't want you to scour through it again for nothing. One min....

Pops

I quickly tested it and it seemed to have worked with the test data given. I tried 01/03/2016 and it gives 01/09/2016

and I tried 01/09/2016 which gave 01/03/2017

Original Poster

It does for some, but not for others (perhaps when it rolls over a year and does not +1 to the year). For example, keeping the same dates but changing to a 3mo interval fails.

Start Date: 01/03/2013

Interval: 3 Months (changed the IF statement where 6 to 3)

Result: 01/06/2016

Changing the Start Date to 01/03/2016 gives 01/06/2016 as it should.

Start Date: 01/12/2016, Interval 3 months, gave 01/03/2017

Edited by:"dai007uk" 16th Aug 2016Original Poster

Another from live data is a start date of 01/10/2015. Result is 01/04/2016 when should be 01/10/2016 (on 6mo).

Cheers

Pops

Original Poster

Issue where highlighted. Result still needs to be after Today(). It appears to be why a few work and a few don't. I like how you tackled the formula tho.

Pops

the start date is the first of the month so the notice date is going to be the first of the month also. as it is well past 6 months from the start date, the notice date will always be the 1st of the next month from today so as we are in August 2016, your answer in your example is 01.09.2016

the end date, i assume is the end of the contract, after which you don't have to worry about notice period

If the start date is 01/06/2016 and its 6 months notice then he cannot simply add a month to today as it would need to be 01/12/2016 ?

If the start date is not fixed as in the original post then it will need 2 logic. If today is more than 6 months from the start date then it will be the 1st of the next month from today. If the start date is less than 6 months from today then it will be your simple start date + 6 months.

Original Poster

Correct.

Afraid not, no. The notice period is 6mo which is a legel way of saying once the notice is served, you have 6mo before it comes to an end. You are reading it as "6mo from the commencement date" which is not the case here. The 6mo period is an interval through the life of the agreement (and so between the start/end dates).

Yes but no - however it would be pointless serving notice 6mo before the end date

Pops

believepopolou wants a rolling 6 month notice period. So with your logic:Start: 01/01/2016, 6 months notice, it has been 7 months so far so we just get the 1st of the next month from today which is 01/09/2016. However the true 6 months would have been 01/07/2016.

If I am correct in the way popolou wants this, is that the correct date would be 01/01/2017.

Please can you clarify popolou

Original Poster

Hmm, perhaps the discussion has taken this on a tangent but admittedly I may have simplified it more than necessary.

dai007uk, you are largely correct in your interpretation. The 6mo notice period is indeed rolling but it is pegged to the anniversary of commencement date. It must also happen within the start/end dates with 6mo clear left on the term for it to be valid.So, taking our example as before: -

Start date: 01/03/2013

End Date: 31/12/2020

Notice period: 6 months

...means that there are 14 opportunities to serve the notice of which the next possible date is March 2017. Now, i know this is different to the date in my OP (01/09/2016) but this is because i chose not to unnecessarily complicate matters (and in any event it's a simple change to any formula to allow for the correct application).

Hope this helps.

Pops

Edited by:"popolou" 16th Aug 2016Original Poster

Commercial leases. I deal with this stuff daily but whereas there is (and i use) specialist software i have often tried to attempt this in Excel but failed.

Pops

I remember the days of ready reckoners - look up tables.

I wonder how such a business described would work if the computer(s) were not available for any length of time?

Original Poster

Oh how i wish it could be so much more straight forward. The problem is when any company relies on Excel to compute these lease critical event dates. It just doesn't/can never/utterly wrong and absolutely false practice to use excel for this purpose. Imagine my vein-bursting astonishment when i see corporates doing the same.

This fully explains why i manually enter the dates in my schedules. No matter how hard we try however, there are situations when regional teams rely on spreadsheets (and which they are fully conversant with like they were born with a copy of Lotus 1-2-3 in their arms) but woefully bad at dissemination of leases which has always had me wondering whether i can bridge the two.

I know it can be done in macro-enabled workbooks but I've wasted hours attempting this via formulas.

Anyone here from around the old Pentium P60 days when the blasted thing got its sums wrong? Yup....:D

Pops

Edited by:"popolou" 16th Aug 2016popolou, give this a try: might be something you can work with

(not looking for coding points here, this is prob a quick and nasty way of doing it :))

A1 start date

A2 end date

B1 will be populated with the 'notice' date

Edited by:"dai007uk" 16th Aug 2016so is it a periodic rolling 6 months contract as the rent is paid every 6 months? on a residential periodic, the tenant has to give 1 month notice on the rent payment date, so is this similar but instead of 1 month, we are talking about 6 months as rent is paid twice a year?

does the contract always start on the 1st of the month?

Original Poster

Not rent no. Termination dates. But just pare out the backstory and it's easier to get your mind around it as simply being a rolling date that has to occur on the same day at 6-monthly interval within the lease dates. The trick is to get excel to flag up the next interval that is after the current date.

Thinking about it, can't believe there was never a function designed for just this.

Pops

Trying to get different parts of the same business to operate the same business processes is difficult. Trying to get different business' to operate the same business processes is impossible.

It is a VERY long time ago when I did this sort of thing for a living and these days I use LibreOffice rather than Excel but the principles are the same. With the prospect of the Millennium bug in the 90s - lots of business spreadsheets with dates inside them had to be changed. I remember old Excel versions which had the ability to be configured to use the 1904 date system? Why? Because Excel stores dates as Integers and there is a huge difference between an integer not using the 1904 date system and one that does. I think all new versions of Excel cannot be configured to use any date sytem other than the 1904 date system.

So, enter any date into a cell in the way you normally would. Then go and format that entered date as a number(integer). In the cell next to it enter 183 and in the cell next to that enter a formulae that adds those 2 numbers together. Then format that cell as a Date. Magic! The date in the 3rd cell now shows a date 183 days after the date in the first cell.

I think using this knowledge a better solution to you problem can be devised.

i can see why this has been done in vba, as effectively it needs to calculates all the dates from the start date + 6 months increments then see where the next available date in that array is from today. this is done via a cycle function where it stops when it spots the next available date in the array.

Original Poster

You cheat.

That's pretty much it in a nutshell (i can see a few minor tweaks to the latter half needed) but it does read much simpler than excel could ever manage via these damned formulas.

Pops

Original Poster

Yup, bingo. Or merely feeding back into a variable n+1 times until it reaches a threshold then spit out the result.

So depressing!:D

Pops

it is not possible to do with one formula in excel. if i had to do this, i would do it in a few columns, where the first column derives all the relevant increment dates, the next column then determines if each of these dates are greater than today, then the next column filters out all the true and pick the first true to return the first relevant date.

Can you confirm whether leases always start on the first day in the month or can they start on any day in the month? Same question for end dates?

Is the end date the same day that the lease started or the day before that? E.G. If a lease starts on 01/09/2016 for say 10 years does it end on 01/09/2026 or 31/08/206.

When you say

Am I missing something or are there only 13 opportunities to serve the notice?

=EDATE(B1,6*(ROUNDUP(DATEDIF(B1,TODAY(),"m")/6,0)))

basically it works out the number of months between today and a previous date, divides it by 6 (notice period) to see how many multiples of the notice period have elapsed, gets rounded up to the nearest integer and multiplied by the notice period and that is the number of months to add to the start date, so the answer will always be after today and no later than 6 months from today.

that is of course i have understood your query correctly.

Edited by:"mug51" 16th Aug 2016Haha :D. mug51 that looks like it would, needs to consider the end date though

Original Poster

Beautifully simple. It's rekindled my appreciation of excel seeing this. Thank you and well done.

I merely added the notice period again into the EDATE to bring it into the next clear interval (ie a date that provides for at least the notice period) and so far it appears to behave itself. Incidentally, i was similarly targeting the multiples with EDATE but hadn't thought to round up to the nearest integer or how it all comes together. I suspect my trouble was with the failure to grasp the mathematical calculation/relationship of the data. This solution has eluded me for quite a while.

It appears to be holding up and I'm confident to give it a try on the live data as a check-column so to all that contributed, many thanks indeed. Learnt something new here.

Regards

Pops

However, the original formula does fail on any previous dates that are less than a month old (datedif function gives 0), I did edit my post to show an updated one last night with a max function in it but somehow the post still shows the old one.

The one with the max function is

=EDATE(B1,6*(ROUNDUP(MAX(DATEDIF(B1,TODAY(),"m")/6,1),0)))