£0.00 @

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 … Read More

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

Tags:

Options

Best Answer

Hey, not an excel specialist, but was intrigued with this puzzle, so thought i'd give it a go. So apologies if the formula is a mess.

=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 on Aug 16, 2016 23:33: .*

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

## All Responses

(33) Jump to unreadPost an answerA1 contains 01/03/2013

and the result is 01/09/2016

Edited By: dai007uk on Aug 16, 2016 18:246 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?

6 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 on Aug 16, 2016 18:31and the result is 01/09/2016

Looking through it now as I speak.

Thanks

Pops

and the result is 01/09/2016

Looking through it now as I speak.

Thanks

Pops

Pops

and the result is 01/09/2016

Looking through it now as I speak.

Thanks

Pops

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

and the result is 01/09/2016

Looking through it now as I speak.

Thanks

Pops

Pops

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

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 on Aug 16, 2016 19:06and the result is 01/09/2016

Looking through it now as I speak.

Thanks

Pops

Pops

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

Cheers

Pops

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/2016as it should.Start Date: 01/12/2016, Interval 3 months, gave 01/03/2017

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

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 ?

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

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

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

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 on Aug 16, 2016 20:18Pops

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?

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?

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 on Aug 16, 2016 21:40(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

Dim Today, StartDate, EndDate, NewDate As Date

StartDate = CDate(Range("A1").Value)

EndDate = CDate(Range("A2").Value)

NewDate = StartDate

Today = Date

Do While NewDate < EndDate

NewDate = DateAdd("m", 6, NewDate)

If (NewDate > EndDate) Then

Range("B1").Value = "Unable to give notice"

Exit Do

Else

If (NewDate > Today) Then

Range("B1").Value = NewDate

Exit Do

End If

End If

Loop

End Sub

Edited By: dai007uk on Aug 16, 2016 21:45Pops

so 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?

Pops

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

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

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?

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

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.

Pops

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

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

Pops

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.

(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

Dim Today, StartDate, EndDate, NewDate As Date

StartDate = CDate(Range("A1").Value)

EndDate = CDate(Range("A2").Value)

NewDate = StartDate

Today = Date

Do While NewDate < EndDate

NewDate = DateAdd("m", 6, NewDate)

If (NewDate > EndDate) Then

Range("B1").Value = "Unable to give notice"

Exit Do

Else

If (NewDate > Today) Then

Range("B1").Value = NewDate

Exit Do

End If

End If

Loop

End Sub

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

Pops

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

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

Pops

So depressing!:D

Pops

Pops

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

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

Pops

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.

Pops

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

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

Pops

So depressing!:D

Pops

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

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.

=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 on Aug 16, 2016 23:33: .So depressing!:D

Pops

Haha :D. mug51 that looks like it would, needs to consider the end date though

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

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