+ Reply to Thread
Results 1 to 3 of 3

Calculating and formatting business days

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    Vegas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Calculating and formatting business days

    I don't know if this is in the right area so I apologize in advance if it isn't. Our department is trying to enforce a policy where workers must return all voicemails within 5 business days.

    We created seperate workbooks for each of the workers to keep a log of their voicemails. Each workbook has 12 worksheets (Jan-Dec). In each worksheet, Column B lists the date of the voicemail and column O lists the date the call was returned.



    I then have a call summary workbook that totals each workers calls and shows me the date of the oldest call that has not been returned for each worker. (below) I use =MIN(IF(O4:O500="",IF(B4:B500<>"",B4:B500))) to give me the date of the oldest call not returned.


    summary.JPG


    My question is...

    1.Is there a way to automatically highlight (or apply some conditional format to) dates in Column B that are older than 5 business days --where there is NOT a date in column O.

    2.Can I calculate how many business days between the date in column B and the date in column O?

    Thank you

  2. #2
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Calculating and formatting business days

    Hi - you may use Network days function to play with the business days between two dates.

    See http://support.microsoft.com/kb/821091

    Hope this helps.
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.

  3. #3
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Calculating and formatting business days

    Use conditional format using a formula and use

    =IF($O2="",NETWORKDAYS($B2,TODAY())>5)

    Make sure you apply to your range in column B

    Formula updated as I forgot you only wanted to apply it if Column O didn't have anything in it
    Last edited by TheCman81; 11-06-2012 at 04:24 PM.
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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