Solved

# excel vb macro, forecasting based on date per project

Posted on 2014-03-08
283 Views
Folks

I want to display projects in a sheet that happen on regular intervals over a calendar per work

i.e.

PROJECT      FREQUENCY      INTERVAL      START DATE      WEEK1      WEEK2      WEEK3
SERVICE CAR      14      DAYS      01/01/2014      X            X
CHECK OIL      7      DAYS      08/01/2014            X      X

how can we do this in excel?
PM-FORECASTER.xlsx
0
Question by:rutgermons
• 2

LVL 39

Expert Comment

I don't really understand what you're trying to achieve. Can you maybe provide a before / after tab on your excel file?

Thomas
0

LVL 80

Accepted Solution

byundt earned 500 total points
I can reproduce your table with this formula in cell E4 (copied across, up and down):
=IF(AND(\$D4<=(E\$1+6),MOD(E\$1-\$D4,\$B4)>=0,MOD(E\$1-\$D4+6,\$B4)<=6),"X","")
PM-FORECASTER-Q28383678.xlsx
0

LVL 80

Expert Comment

I should have noted that I changed Week1, Week2, Week3... to the dates that each week begins on.
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.