Determine a date 1 month from the start date

Tavasan65
Tavasan65 used Ask the Experts™
on
Hello Experts,

What I am trying to do is enter a start date and based on that start date the formula should provide me a date that is 1 month out and pick the Thursday that is closest to that 1 month window.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Muhammad Ahmad ImranDatabase Developer

Commented:
this will be giving you the date after a month

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Muhammad Ahmad ImranDatabase Developer

Commented:
for the second part of your query see the attached file

=B1+12-WEEKDAY(B1)
Next-Thursday.xlsx
Most Valuable Expert 2013
Commented:
Hello leommad, surely 13th October isn't the closest Thursday to 3rd October?

I think you can do this all in one fomula, using EDATE to add 1 month and then an adjustment to get the Thursday. If you really mean the nearest Thursday (so you could get less than a month) try this formula

=EDATE(A1,1)+3-WEEKDAY(EDATE(A1,1)-2)

.....so if A1 is 31st August 2011 then EDATE adds a month to get Friday 30th Sept. The nearest Thursday to that is the day before, 29th Sept 2011 so the formula returns that date.

If you always want to move forwards to the next Thursday after adding 1 month then try this variation....

=EDATE(A1,1)-WEEKDAY(EDATE(A1,1)+2)+7

.....so in my example above the result would be a week later, 6th October

See attached example with random dates - press F9 to re-generate new dates...

regards, barry
27289916.xlsx
Most Valuable Expert 2013
Commented:
...actually, I made a mistake with that first formula. The nearest Thursday to a Monday would, of course be the Thursday after (not before), so to get that date the first formula needs a small amendment, i.e. this version:

=EDATE(A2,1)+4-WEEKDAY(EDATE(A2,1)-1)

regards, barry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial