Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculating calendar days from working days (Excel 2002)

    I need a formula that will calculate a calendar day value from a working day value based on a given start date. For example I need to place an order for a part where the supplier has quoted me 4 working days leadtime. I'm placing the order today (Tuesday 5th August) so the calendar day value will be 6 as there is a weekend. If I'd placed the order yesterday, the calendar day value would be 4 (as delivery would occur on the Friday of the same week). I also need to exclude Bank Holidays. My spreadsheet looks like...
    Cell A1- Order Date
    Cell A2 - Leadtime Working Days
    Cell A3 - Calculated calendar days leadtime

    Any help you can give would be much appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating calendar days from working days (Excel 2002)

    How about in A3
    =workday(A1,A2,B1:B15)-A1

    I presume that B1:B15 contains a list of the dates of bank Holidays. You must install the Analysis toolpack for the Workday function to work (You will get a #name error if it is not installed)

    Steve

  3. #3
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculating calendar days from working days (Excel 2002)

    Thanks Steve. That works a treat. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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