Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays & future months
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,holidays)) 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays & future months
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "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,holidays)) 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays & future months
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays & future months
Arvi and Bob,
Thanks to you both for the suggestions. I'll give it a try. Denise "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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays & future months
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays & future months
"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,holidays)) 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates for a the present month but not future months | Excel Worksheet Functions | |||
Calculating Dates in Terms of Months | Excel Worksheet Functions | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |