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

    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

    33 Comments

    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.
    Please can you clarify popolou

    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)))
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Best Samsung mid-range mobile phone?11
      2. Ripped off by GearBest, VERY ANGRY!99
      3. HELP - am i being scammed on gum tree1113
      4. What kind of workstation desk should I buy for my office / music room ?35

      See more discussions