Networkdays & future months
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)
"denise" wrote in message
...
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" wrote:
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)
"denise" wrote in message
...
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,holid ays),NETWORKDAYS(g28,g29,h
olidays))
If report month is June, then show June networdays to report data
date,
otherwise show June total month networkdays
Whe
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!
|