Excel Forecasting Dates Out to the Day

F

FairInHite

Joined
May 3, 2017
Messages
2
Reaction score
0
I work for a university that is trying to figure out an easier way to forecast dates going forward instead of doing this manually.

Let's say that I have a class in 2016 that starts on Monday, February 1, 2016 and ends on Wednesday, February 24, 2016

I need to forcast this date so it's a year later, but that starts and ends on the say day (versus date). So, for example: starts Monday, January 30, 2017 and ends on Wednesday, February 22, 2017.

Is there a formula I could use?
 
What is the method behind working it out? eg does it always start on the 5th Monday in the year?
 
We have classes that can literally start ANY day during the semester, so it's not as cut and dry as I would have hoped. For example, some of my classes are Monday, Wednesday, Friday and I want them to remain on Monday, Wednesday and Friday the next year as well. I want to basically add 365 days and find the closest date that has the exact same start day.

I hope that makes sense, it is a very weird project.

I've been playing around and found that I can get to the same information I need by using one of two formulas:
=Workday(Edate(B2,12)-1, 1) or =Workday(Edate(B2,12)-2, 1) depending on the month of the course. I can probably use one column with formula1 and another column with formula2 and figure out how to do some conditional formatting to see which one of those formulas matches the day Im looking for, but I was hoping there would be a shortcut.
 
There's no easy solution I know of, but I'll have a think. Sometimes, when it comes to things like this, you can spend so long trying to figure out a formula that it would have been far easier to just look at a calendar ;)
 
Back
Top