# Thread: Calculating dates in reverse; always 'land on' a Friday

1. Hi all.....the attached scheduling s/sheet requires certain parameters built into columns J-M.

What happens is that someone reserves a seat on Boat A or B on any given date (col D). They can confirm or stand-by (col H); there are 4 seats/per day (altho I have allowed for 6, in case of expansion). Once 4 seats have been booked, it shows “N” (more room) in column B.

If they want to travel, for example, on any day from June 1/09 to June 5/09, we call them no later than the 4th Friday before the week in which they want to travel (eg: if traveling b/w June 1-5, we call no later than Fri May /09).

The have to return our call no later than the 3rd Friday before the week they are traveling (eg: if traveling b/w June 1-5, they must call back no later than Fri May 15; they have to send in their payment no later than the 2nd Friday before the week they travel (ie: by Fri May 22)….and the final confirmation check date within the office is a minimum if 1 week before the travel date (eg: if travel is Tue June 2, final date is Tue May 26).

These are deadlines that we impose on ourselves and people who wish to travel…if a Friday deadline falls on a holiday, and we are closed, the date gets pushed up by 1 day to the Thursday see Jan 6, 2009), but I am not too concerned about a formula for this variation b/c that only really occurs once or twice a year and we can make that adjustment manually.

What I am looking for is a formula that will calculate (backward) the deadline dates, especially the date in column J, and have it always land on a Friday…..I imagine that if I can get a formula for the date that falls in column J, setting up the other dates (in col K, L & M) can tie back to the col J date (by adding 7 or 14 or 21 as need be, as I have already done.). The hard part is getting the date in col J to always land on a Friday…and, if I change the year in cell C3, then the workdays change automatically and that throws any direct linking out of sync.

2. In cell J13 type

=D1-(CHOOSE(WEEKDAY(D1),2,3,4,5,6,7,1))-14

3. [quote name='dmcnab' post='782191' date='28-Jun-2009 14:18']Hi all.....the attached scheduling s/sheet requires certain parameters built into columns J-M.[/quote]

Hi Dave

Take a look at the formulae in J15:M15 of the attached workbook.

4. Hi Jezza & Don...thank you for those formula....

5. [quote name='dmcnab' post='782226' date='28-Jun-2009 19:36'][/quote]
The attached workbook has all holidays listed in Column O. The formulae look for a match and adjust accordingly. They also test whether the previous milestone is a Friday and adjusts accordingly if not.A formula has been added to E15 allowing holidays to be entered in Column O and forgotten.

6. Good morning, Don....thank you for your latest effort...I think (emphasis, think) that I understand most of it, altho the formula in column I is more difficult (the Call on column)........can I ask this--if Dec 24 and Dec 25 are both holidays,and I add them to the list in column N, shouldn't any deadline that falls on Fri Dec 25 be backed up to Thurs Dec 24, and then again backed up to Wed Dec 23?

And for the formula in col J-L, I gather that the reference to "6,7,8" is days of the week (?) but I don't understand the 8...??..??

7. [quote name='dmcnab' post='782279' date='29-Jun-2009 06:59'][/quote]

>>And for the formula in col J-L, (sic) I gather that the reference to "6,7,8" is days of the week (?) but I don't understand the 8...??..??
The formulae in columns K:M are all dependent on the preceding column; which will be either a Thursday or Friday (weekday 6 or 7 respectively). If the WEEKDAY function returns 6, 7 days are added; if not it is a Thursday and 8 days are added.

>>shouldn't any deadline that falls on Fri Dec 25 be backed up to Thurs Dec 24, and then again backed up to Wed Dec 23?
That would be nice, but you will need to determine the limit to which you are willing to go (3 days? a week?). Having made that determination it will be necessary to exponentially complicate the formulae to accommodate the requirement. If one were to add this as a requirement, I would take a different approach and build a custom calendar off to the right which contains only the true workdays; and use only this calendar in your calculations.

8. [quote name='dmcnab' post='782279' date='29-Jun-2009 06:59']Good morning, Don....thank you for your latest effort...I think (emphasis, think) that I understand most of it, altho the formula in column I is more difficult (the Call on column)........can I ask this--if Dec 24 and Dec 25 are both holidays,and I add them to the list in column N, shouldn't any deadline that falls on Fri Dec 25 be backed up to Thurs Dec 24, and then again backed up to Wed Dec 23?

And for the formula in col J-L, I gather that the reference to "6,7,8" is days of the week (?) but I don't understand the 8...??..??[/quote]

Hi David

I have given some further thought to your problem and believe that the attached answers your needs. Some explanation:
• All response dates are derived from the calendar in column W.
• Column W is derived from the year entered in C3 and the holidays entered in column S
• In order to reduce the calculation time I have set it up such that position A1 must be the first position entered for any excursion date.

9. Hi Don....I tried to send you a message, in reply to yours of July 2...not sure if I did it correctly, so I will repeat here what I put in the message. Thank you for your help with this; I didn't see your message and post til Sunday (traveling). I think that I understand the relationships amoung the formulas, and I was able to re-work it so that it works within my workbook. However, at the bottom of the s/sheet, you'll see a lot of NA# or blanks...actually, the NA# start in Feb with the first holiday (Feb 16).........I don't know the significance of 90000 in Cal 4 and tried changing that to 100000 (to see if it would fix the problem, but it didn't). I think that the porblem relates to the fact that the holidays (column A) don't show up in Cal 4....I also don't know what the dates in Cal 4 'seem' to go up to 2046 (am I right?!?) but don't plan on worrying about that b/c that doesn't seem to be the source of the present problem. I am re-posting the full worksheet, in a compressed format as it is about 2MB unzipped. Are you able to take a look at it and see what the problem is re: the NA#..??....once that is solved, I beleive that the workbook will be complete...as always, thank you.

10. [quote name='dmcnab' post='783643' date='08-Jul-2009 16:37']Hi Don....I tried to send you a message, in reply to yours of July 2...not sure if I did it correctly, so I will repeat here what I put in the message. Thank you for your help with this; I didn't see your message and post til Sunday (traveling). I think that I understand the relationships amoung the formulas, and I was able to re-work it so that it works within my workbook. However, at the bottom of the s/sheet, you'll see a lot of NA# or blanks...actually, the NA# start in Feb with the first holiday (Feb 16).........I don't know the significance of 90000 in Cal 4 and tried changing that to 100000 (to see if it would fix the problem, but it didn't). I think that the porblem relates to the fact that the holidays (column A) don't show up in Cal 4....I also don't know what the dates in Cal 4 'seem' to go up to 2046 (am I right?!?) but don't plan on worrying about that b/c that doesn't seem to be the source of the present problem. I am re-posting the full worksheet, in a compressed format as it is about 2MB unzipped. Are you able to take a look at it and see what the problem is re: the NA#..??....once that is solved, I beleive that the workbook will be complete...as always, thank you.[/quote]
Dave
I have repaired your file and returned it along with an explanation of all formulae referenced my previous offering.

11. Hi Don....thank you very much for the update, and especially for the explanation, as this helps me to learn what you did (espec if I can look at it next to a real-life example..very much appreciated).....I have another question/something that I thought of as I was working on this today-----it's 2 parts:

(1) I have attached a clip from the file you sent me and I have added 2 worksheets: a Conf sheet and an Agents sheet. I was thinking that the Confirmation sheet (Outline of Travel Arrangements) that is prepared manually could be done automatically as a new booking is made.....at this point, I see the data being entered using drop-down menus that read the data entered in the various cells in the different columns mentioned, on the scheduling Calendar....this is better than printing it and hand-writing the data, as I could use drop-downs and scroll thru the list of names, move to each of the deadline dates and do the same....that would be relatively quick and produce a neater piece of paper, but is there any easy way to populate those cells with freshly entered data each time a booking is set up? As I say, using drop-downs will work for me, but greater automation is always better...altho I fear that what is needed would make the file huge.....

(2) I have set up a list in a drop-down of travel agents....as agents names, phone numbers & email get entered into the list, the names (and phone & email) can be accessed on the Calendar page using a drop-down....I want to be able to select an agents name and have the name and the phone number and email populate the correspondingly adjacent cells on the Calendar sheet (without having to use a drop-down for each of phone and email). I tried to define a list as being the data in the name & 2 adjacent columns, thinking it would pull data from 3 columns into the 3 corresponding columns on the Calendar sheet, but it wouldn't let me...it referred to a delimited list but I can't find anything (that makes sense) in the HELP files about delimited lists........this issue would actually be a bigger time saver/error preventer than addressing (1) above.....any suggestions? Thanks....

...ps: I am wondering if I could shade the row on the Calendar sheet that I want to copy data from and jump over to the Conf sheet and run a macro that would populate the cells with the approriate data that was copied from the Calendar sheet????

12. [quote name='dmcnab' post='783850' date='09-Jul-2009 17:06']Hi Don....thank you very much for the update, and especially for the explanation, as this helps me to learn what you did (espec if I can look at it next to a real-life example..very much appreciated).....I have another question/something that I thought of as I was working on this today-----it's 2 parts:

(1) is there any easy way to populate those cells with freshly entered data each time a booking is set up? As I say, using drop-downs will work for me, but greater automation is always better...altho I fear that what is needed would make the file huge.....

(2) I have set up a list in a drop-down of travel agents....[/quote]

Hi David

(1) I have put macros in the Workbook module and the Calendar worksheet module which automates the whole thing.

(2) I have added formulae to columns M:N to capture the data.

13. Good afternoon, Don....boy, that code is amazing...I was anticipating something that would make the file monstrous in size....I am still making some adjustments to it and doing some testing but so far, so good.....with 2 small excpetions -- on the Calendar sheet, I intend to protect it with a password -- when I did that on my sample working copy (and added a macro to auto format column width etc), I was no longer able to manually unprotect using the password, click in the top left corner (to select all cells) and manually unhide columns (Format_Column-Unhide)........??............and the formula in column U is slightly erroneous...see U25, where it says Dec 22/08...it should be Dec 26/08 (but b/c the 26th would be Boxing Day and the 25th is also a holiday) it should 'back up' to Dec 24/08......see attached file that I have renamed

ps: and it won't met me even insert a column etc.....

14. [quote name='dmcnab' post='784038' date='10-Jul-2009 15:21']Good afternoon, Don....boy, that code is amazing...I was anticipating something that would make the file monstrous in size....I am still making some adjustments to it and doing some testing but so far, so good.....with 2 small excpetions -- on the Calendar sheet, I intend to protect it with a password -- when I did that on my sample working copy (and added a macro to auto format column width etc), I was no longer able to manually unprotect using the password, click in the top left corner (to select all cells) and manually unhide columns (Format_Column-Unhide)........??............and the formula in column U is slightly erroneous...see U25, where it says Dec 22/08...it should be Dec 26/08 (but b/c the 26th would be Boxing Day and the 25th is also a holiday) it should 'back up' to Dec 24/08......see attached file that I have renamed

ps: and it won't met me even insert a column etc.....[/quote]

>>the formula in column U is slightly erroneous.
I have revised the formulae in P13; Q13: and U13. And of course the corresponding cells for subsequent excursion dates. Instead of looking for a date earlier than six days before the controlling date (W13 in the case of U13), it now looks for a weekday which is greater than the weekday of the controlling date.

>> protect it with a password -- when I did that on my sample working copy (and added a macro to auto format column width etc),
Let me have a look at the macro please. As I understand the problem:
• You protected the sheet
• then you ran the macro
• then you were unable to unprotect the sheet.

15. Hi Don -- here is the macro that I am using to autoformat the page, in order to widen out any columns to accommodate data entered into them...you'll see that I can't insert any columns, and if I untprotect the page, it still doesn't let me unhide columns or do anything else to format the page.....??..??

BTW: I notice that the email addresses that are 'copied' into the Calendar sheet from the Agents page are not 'active'..ie: they don't allow me to click them and send an email....could that be related to the code that you set up for me??

Sub AF_Calendar()
'
' AF_Calendar Macro
' Macro recorded 7-10-2009 by David J. McNab
'

'
Columns("A:Y").Select
Selection.Columns.AutoFit
Columns("E:F").Select
Selection.EntireColumn.Hidden = True
Columns("AB:AJ").Select
Selection.EntireColumn.Hidden = True
Selection.Rows.AutoFit
Rows("12:3132").Select
Selection.RowHeight = 30
Range("b5").Select