+ Reply to Thread
Results 1 to 7 of 7

Networkdays & future months

  1. #1
    denise
    Guest

    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,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!

  2. #2
    Arvi Laanemets
    Guest

    Re: 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<at>tarkon.ee )

    "denise" <denise@discussions.microsoft.com> wrote in message
    news:3493FD9E-3A92-4548-9641-4B6698261015@microsoft.com...
    > 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!




  3. #3
    Bob Phillips
    Guest

    Re: 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" <denise@discussions.microsoft.com> wrote in message
    news:3493FD9E-3A92-4548-9641-4B6698261015@microsoft.com...
    > 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,h
    olidays))
    >
    > 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!




  4. #4
    denise
    Guest

    Re: 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" <denise@discussions.microsoft.com> wrote in message
    > news:3493FD9E-3A92-4548-9641-4B6698261015@microsoft.com...
    > > 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,h
    > olidays))
    > >
    > > 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!

    >
    >
    >


  5. #5
    denise
    Guest

    Re: 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" <denise@discussions.microsoft.com> wrote in message
    > news:3493FD9E-3A92-4548-9641-4B6698261015@microsoft.com...
    > > 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,h
    > olidays))
    > >
    > > 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!

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: 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" <denise@discussions.microsoft.com> wrote in message
    news:8F53C3F0-988C-47F2-93C1-BBA610C539F6@microsoft.com...
    > 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" <denise@discussions.microsoft.com> wrote in message
    > > news:3493FD9E-3A92-4548-9641-4B6698261015@microsoft.com...
    > > > 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,h
    > > olidays))
    > > >
    > > > 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!

    > >
    > >
    > >




  7. #7
    marko
    Guest

    Re: Networkdays & future months


    "denise" <denise@discussions.microsoft.com> wrote in message
    news:3493FD9E-3A92-4548-9641-4B6698261015@microsoft.com...
    > 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!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1