Networkdays & future months

[Guest]

This looks like a great discussion group and I'm hoping someone out there
will be able to help me. I'm setting up a spreadsheet for 2006 with Jan, Feb,
Mar, etc out to YTD column headers and productivity items down the rows. I
will be tracking each item for actual quantity and percent of monthly
objective achieved.

I have a holiday table set up on a separate sheet for productive days and
have used the following to get the productive days for each month (using June
as an example):

=IF('sheet1'!$i$2="Jun",NETWORKDAYS(g28,$c$2,holidays),NETWORKDAYS(g28,g29,holidays))

If report month is June, then show June networdays to report data date,
otherwise show June total month networkdays

Where:
sheet1 i2 = Report Month (in this case June)
g28 = June start day (06/01/06)
c2 = report data date (06/16/06 as an example)
g29 = June end day (06/30/06)

This works fine for June and all previous months but I need to show future
months as blank to prevent summing to YTD to prevent distorting averages. I
know I can nest up to 7 IF statements but I haven't been able to get this
working. Any suggestions are much appreciated!

 

[Arvi Laanemets]

Hi


My advice is to use different setup. Here is one I use myself for a quite
similar task:

On network resource, available for all users, is a workbook Calendar with 2
worksheets - Calendar and Weeks
On sheet Calendar is a table with columns: Date, Weekday (in format dddd),
DayType (workday, weekend, state holiday, pre-holiday), NormativeHours,
WeekNo (in format yyyy.ww). Table contains dates from some start date until
some end date in future (in my calendar, until year 2011). (DayType
'pre-holiday' indicates workdays immediately before state holiday, which
have shortened workday by law)
On sheet Weeks is a table with columns: WeekNo, StartDate, EndDate,
WorkDays, WorkingHours - the table is generated form table Calendar through
ODBC query, and is refreshed on open. Columns WorkDays and WorkingHours are
calculated from Calendar.

For every department, the is a workbook meant to track production on network
resource. The production workbook contains some register sheets like
ProductionArticles, ArticleNormatives, Benches, Workers, etc. It also
contains a Claendar (or Weeks, or both) sheet, to where info from Calendar
workbook is retrieved through ODBC querie(s). And it contains a single
table, where all production info is entered: Date, Article, ... etc. The
number of prepared rows in tables is controlled by procedures, invoked
automatically on open, or manually by user. All production data older than
some predefined date are removed from production table, and stored in
separate archive workbook. For all dates older than some fixed date
interval, all formulas are replaced with values, etc.

For every department, there is a report workbook, which gets needed data
through ODBC queries from production workbook to hidden sheets. The workbook
has one or several report sheets, where the user determines report
parameters, like time interval, or month, or week, or year, or article, etc,
and according data is read/calculated from hidden sheets.

When needed, there can be summary report workbook(s), where summary info
from all department workbooks is collected, and displayed accordingly
selected parameters.

 

[Bob Phillips]

If Arvi's suggestion doesn't suit, you could try this formula

=IF(Sheet1!$I$2="Jun",NETWORKDAYS(DATEVALUE("01-"&$I$2&"-2006"),$C$2,holiday
s),NETWORKDAYS(DATEVALUE("01-"&$I$2&"-2006"),DATE(2006,MONTH(DATEVALUE("01-"
&$I$2&"-2006"))+1,0),holidays))

Best to put the year in another cell though and reference that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Arvi and Bob,

Thanks to you both for the suggestions. I'll give it a try.

Denise

 

[Guest]

Bob,

I changed my formula to the one you suggested here. It works fine except I
still have my challenge of how to show future months as blank. I realize that
I could just copy the formula over when I start a new month but I was hoping
there might be some combination or nested if statement I could use to just
show a blank until the report date came current. Does this make any sense?

Thanks!

 

[Bob Phillips]

Denise,

I am not sure I totally understand, I am unsure of how your data is
structure, but does this doe it

=IF(DATEVALUE("01-"&$I$2&"-2006")>TODAY(),"",
IF(Sheet1!$I$2="Jun",NETWORKDAYS(DATEVALUE("01-"&$I$2&"-2006"),$C$2,holidays
),
NETWORKDAYS(DATEVALUE("01-"&$I$2&"-2006"),DATE(2006,MONTH(DATEVALUE("01-"&$I
$2&"-2006"))+1,0),holidays)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[marko]

This looks like a great discussion group and I'm hoping someone out there
will be able to help me. I'm setting up a spreadsheet for 2006 with Jan,
Feb,
Mar, etc out to YTD column headers and productivity items down the rows. I
will be tracking each item for actual quantity and percent of monthly
objective achieved.

I have a holiday table set up on a separate sheet for productive days and
have used the following to get the productive days for each month (using
June
as an example):

=IF('sheet1'!$i$2="Jun",NETWORKDAYS(g28,$c$2,holidays),NETWORKDAYS(g28,g29,holidays))

If report month is June, then show June networdays to report data date,
otherwise show June total month networkdays

Where:
sheet1 i2 = Report Month (in this case June)
g28 = June start day (06/01/06)
c2 = report data date (06/16/06 as an example)
g29 = June end day (06/30/06)

This works fine for June and all previous months but I need to show future
months as blank to prevent summing to YTD to prevent distorting averages.
I
know I can nest up to 7 IF statements but I haven't been able to get this
working. Any suggestions are much appreciated!