Groups

# Excel Formula Needed: Dates

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

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

=IF(A1-TODAY()

A1 contains 01/03/2013
and the result is 01/09/2016
Edited by: "dai007uk" 16th Aug 2016

Why would the answer be 01/09/2016?
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?

philphil61

Why would the answer be 01/09/2016?6 months from Today should be approx … Why would the answer be 01/09/2016?6 months from Today should be approx 16/02/2017Sowhere is the formula collecting the "Today" ?andare 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 2016

Original Poster

dai007uk

This /should/ work for your problemA1 contains 01/03/2013and the result … This /should/ work for your problemA1 contains 01/03/2013and the result is 01/09/2016

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

popolou

Incredible, in a single IF statement which I didn't think was possible. … 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.ThanksPops

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

popolou

Sorry fella, I think I may be wrong (and your suggestion is right). Don't … 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

dai007uk

I quickly tested it and it seemed to have worked with the test data … 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/2016and I tried 01/09/2016 which gave 01/03/2017

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.

Give me an example, I just tried:
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 2016

Original Poster

popolou

It does for some, but not for others (perhaps when it rolls over a year … 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.

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

dai007uk

Give me an example, I just tried:Start Date: 01/03/2013Interval: 3 Months … Give me an example, I just tried:Start Date: 01/03/2013Interval: 3 Months (changed the IF statement where 6 to 3)Result: 01/06/2016Changing 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

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

this is not an excel formula problem but a logic problem surely?

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

mutley1

this is not an excel formula problem but a logic problem surely?the start … this is not an excel formula problem but a logic problem surely?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.2016the 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 ?

dai007uk

If the start date is 01/06/2016 and its 6 months notice then he cannot … 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

mutley1

the start date is the first of the month so the notice date is going to … the start date is the first of the month so the notice date is going to be the first of the month also.

Correct.

mutley1

as it is well past 6 months from the start date, the notice date will … 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.

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

mutley1

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

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

Pops

mutley1 that works fine in that case but I believe popolou 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.

OP. in what context are you looking at? if you explain what the real situation is then we may be able to understand better.

Original Poster

dai007uk

mutley1 that works fine in that case but I believe popolou wants a … mutley1 that works fine in that case but I believe popolou 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

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 2016

Original Poster

mutley1

OP. in what context are you looking at? if you explain what the real … OP. in what context are you looking at? if you explain what the real situation is then we may be able to understand better.

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 mean this in all seriousness dont forget to take leap years into account and do please take fully into account right down to the second or less. Every day has 24 hours in it, and every hour 60 minutes and every minute has 60 seconds. And every week has 7 days but not every month is constant in terms of days. Only 1 month has a variable number of days. All these facts are relevant when determining intervals in legal lease terms. Believe me you do not need software to help with these sort of business rules. What you need to do is change the business rules. So instead of a 6 month lease, replace the term with a 183 day lease. Calculate the end date manually and change the dates in the agreement to absolute dates - rather than leaving it to be determined ambiguously. Sometimes, a low tech solution is the best.
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

tardytortoise

I mean this in all seriousness dont forget to take leap years into … I mean this in all seriousness dont forget to take leap years into account and do please take fully into account right down to the second or less. Every day has 24 hours in it, and every hour 60 minutes and every minute has 60 seconds. And every week has 7 days but not every month is constant in terms of days. Only 1 month has a variable number of days. All these facts are relevant when determining intervals in legal lease terms. Believe me you do not need software to help with these sort of business rules. What you need to do is change the business rules. So instead of a 6 month lease, replace the term with a 183 day lease. Calculate the end date manually and change the dates in the agreement to absolute dates - rather than leaving it to be determined ambiguously. Sometimes, a low tech solution is the best.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?

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 2016

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

Sub Test()Dim Today, StartDate, EndDate, NewDate As DateStartDate = … Sub Test()Dim Today, StartDate, EndDate, NewDate As DateStartDate = CDate(Range("A1").Value)EndDate = CDate(Range("A2").Value)NewDate = StartDateToday = DateDo While NewDate 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 IfLoopEnd Sub

Edited by: "dai007uk" 16th Aug 2016

popolou

Commercial leases. I deal with this stuff daily but whereas there is (and … 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

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?

Original Poster

mutley1

so is it a periodic rolling 6 months contract as the rent is paid every 6 … 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?

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

popolou

Oh how i wish it could be so much more straight forward. The problem is … 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....:DPops

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.

popolou

Not rent no. Termination dates. But just pare out the backstory and it's … 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

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

dai007uk

popolou, give this a try: might be something you can work with(not … popolou, 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 dateA2 end dateB1 will be populated with the 'notice' date

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

mutley1

i can see why this has been done in vba, as effectively it needs to … 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.

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

popolou

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

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.

popolou

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

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

So, taking our example as before: -Start date: 01/03/2013End Date: … So, taking our example as before: -Start date: 01/03/2013End Date: 31/12/2020Notice period: 6 months...means that there are 14 opportunities to serve the notice of which the next possible date is March 2017.

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

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" 16th Aug 2016

popolou

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

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

Original Poster

mug51

Hey, not an excel specialist, but was intrigued with this puzzle, so … 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.

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

No problem, happy it works for you.

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