Excel Formula Needed: Dates - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Excel Formula Needed: Dates

£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
popolou Avatar
9m, 1w agoPosted 9 months, 1 week ago
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
popolou Avatar
9m, 1w agoPosted 9 months, 1 week ago
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: .

All Responses

(33) Jump to unreadPost an answer
Responses/page:
#1
This /should/ work for your problem
=IF(A1-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),(6-DATEDIF(A1,TODAY(),"ym")))),MONTH(EOMONTH(TODAY(),6-DATEDIF(A1,TODAY(),"ym"))),DAY(A1)),EDATE(A1,6))

A1 contains 01/03/2013
and the result is 01/09/2016

Edited By: dai007uk on Aug 16, 2016 18:24
#2
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?
#3
philphil61
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?

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:31
#4
dai007uk
This /should/ work for your problem
=IF(A1-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),(6-DATEDIF(A1,TODAY(),"ym")))),MONTH(EOMONTH(TODAY(),6-DATEDIF(A1,TODAY(),"ym"))),DAY(A1)),EDATE(A1,6))
A1 contains 01/03/2013
and 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
#5
popolou
dai007uk
This /should/ work for your problem
=IF(A1-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),(6-DATEDIF(A1,TODAY(),"ym")))),MONTH(EOMONTH(TODAY(),6-DATEDIF(A1,TODAY(),"ym"))),DAY(A1)),EDATE(A1,6))
A1 contains 01/03/2013
and 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
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
#6
popolou
popolou
dai007uk
This /should/ work for your problem
=IF(A1-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),(6-DATEDIF(A1,TODAY(),"ym")))),MONTH(EOMONTH(TODAY(),6-DATEDIF(A1,TODAY(),"ym"))),DAY(A1)),EDATE(A1,6))
A1 contains 01/03/2013
and 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
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
#7
dai007uk
popolou
popolou
dai007uk
This /should/ work for your problem
=IF(A1-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),(6-DATEDIF(A1,TODAY(),"ym")))),MONTH(EOMONTH(TODAY(),6-DATEDIF(A1,TODAY(),"ym"))),DAY(A1)),EDATE(A1,6))
A1 contains 01/03/2013
and 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
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
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.
#8
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 on Aug 16, 2016 19:06
#9
popolou
dai007uk
popolou
popolou
dai007uk
This /should/ work for your problem
=IF(A1-TODAY()<1,DATE(YEAR(EOMONTH(TODAY(),(6-DATEDIF(A1,TODAY(),"ym")))),MONTH(EOMONTH(TODAY(),6-DATEDIF(A1,TODAY(),"ym"))),DAY(A1)),EDATE(A1,6))
A1 contains 01/03/2013
and 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
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
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
#10
dai007uk
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
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
#11
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 :)
#12
mutley1
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 :)

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 ?
#13
dai007uk
mutley1
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 :)
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.
#14
mutley1
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 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 have to worry about notice period :)
Yes but no - however it would be pointless serving notice 6mo before the end date ;)

Pops
#15
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
#16
OP. in what context are you looking at? if you explain what the real situation is then we may be able to understand better.
#17
dai007uk
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 on Aug 16, 2016 20:18
#18
mutley1
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
#19
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?
#20
tardytortoise
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 on Aug 16, 2016 21:40
#21
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 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:45
#22
popolou
mutley1
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

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?
#23
mutley1
popolou
mutley1
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
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
#24
popolou
tardytortoise
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
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.
#25
popolou
mutley1
popolou
mutley1
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
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

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.
#26
dai007uk
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 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
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
#27
mutley1
popolou
mutley1
popolou
mutley1
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
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
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
#28
popolou
mutley1
popolou
mutley1
popolou
mutley1
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
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
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

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.
#29
popolou
mutley1
popolou
mutley1
popolou
mutley1
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
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
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

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/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.
Am I missing something or are there only 13 opportunities to serve the notice?
#30
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: .
#31
popolou
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

Haha :D. mug51 that looks like it would, needs to consider the end date though
#32
mug51
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
#33
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 an Answer

You don't need an account to leave a response. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!