Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Determine a date 1 month from the start date

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.
0
Tavasan65
Asked:
Tavasan65
  • 2
  • 2
2 Solutions
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
this will be giving you the date after a month

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
for the second part of your query see the attached file

=B1+12-WEEKDAY(B1)
Next-Thursday.xlsx
0
 
barry houdiniCommented:
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
0
 
barry houdiniCommented:
...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
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now