Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Calculating deadlines

    Hi loungers...I am not sure how to proceed here, but I had a thread running from June 28/09 to Aug 6/09 concerning a request for help in calculating deadlines....Don Wells was (as always) extraordinarily helpful............I don't know whether I am supposed to return to that thread or start anew, but I suspect that latter.

    I am attaching a dummy copy of the workbook (with some columns hidden for ease of use, but which can be un-hidden) that I am using. I have entered some sample data in rows 13, 31, 299, 535......it is a scheduling s/sheet that I use for booking trips and managing the deadlines associated with a reservation. Currently, each reservation has 4 deadlines ("Current Deadlines") in R8, W8, Y8 and AF8. I need to change the deadlines (and how they are calculated) to conform with what is shown as "New Deadlines will be" in row 2 (essentially, advance deadline #1 and #2 by a week, and, either change deadline 3 as shown (or, delete deadline 3 as is and change deadline 4 from '1 week before the date of the trip' to 'the last Thu before the week in which the trip occurs')......what may not be apparent from the existing setup is that if a deadline falls on one of the dates shown in A12:A28, then the deadline advances to the previous business day, and that requirement should continue in the 'new deadlines'....I have studied the formulae that Don did but there is so much of it that I lose track of how it all fits together......if someone can understand how Don did this (or Don sees this) and can help me with these changes, I would be very grateful....thank you.
    Attached Files Attached Files
    Last edited by dmcnab; 2011-03-01 at 12:11.

  2. #2
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers....since starting this thread yesterday, I have been re-thinking about the simplest way to approach this and it dawned on me that I simply hide the column containing the 1st deadline (don't want to delete the column in case other columns depend on it); I could then re-label deadline #2 as deadline #1, re-label deadline #3 as deadline #2, and, re-label deadline #4 as deadline #3......then I only need to re-write the formula for the 'new' deadline #3 to have it generate a day that is the last Thursday before the week in which the event occurs (and if that day falls on one of the days shown in A12:A28, then it backs it up to the previous business day........can anyone see why this wouldn't work? Can someone suggest how I could re-write the formula for the 'new' deadline #3, b/c I have tried using a modified version of the formula that produces deadlines 1 and 2, but that doesn't seem to work....any ideas? Thanks..

  3. #3
    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
    I can't get the file to open. I get an "unrecognizable zipfile read error" message...

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...yes, I see that.....I have just finished making the changes that I referred to in the start of this thread, and so I hope (?) that I have everything fixed up....if so, then this thread can be closed..and I will advise in a few days.....do you still want me to try to re-zip the file and re-upload it, even if the problem is solved?

  5. #5
    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
    If the problem is solved, I don't need to spend any time with it...

    Steve

Posting Permissions

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