Calculate Number off of Date

[tcfd1166]

I have a database that I print out a report of my employees locations. We work on tours of every 4 weeks. I would like the report to automatically print out a tour number based off of the week number of the year. I would also like it to print a week number, 1 through 4. Example: This week is tour 9, week 3 for our company. The week and tour is based off the first sunday in January of each year, so I may have to modify the starting date. This year, we are starting the second week in January. I know I can ask in the report in a text box each time the report is printed, but I would like this to be automatically done for me, since some other people also print this report.

Thanks.

 

[tcfd1166]

I have figured out the tour field.
=-Int(Format(Date(),"ww")/-4)

I am still having trouble with the week field.

 

[xenou]

Hi,
I would probably create a lookup table at the start of each year and then join to it on dates to get the information I need. This is probably easier and safer than trying to come up with some formula that calculates tours and weeks (which will probably not work someday when another "exceptional" case occurs).

E.g., something like this (shown for the first few months) - I'm using the "fld" prefix in order to avoid conflicts with VBA named functions on the fiels such as Date and DayOfWeek. Create the data in Excel and import it (excel is easier to use for generating a list like this).

----------------------------------------------------------
|  ID |   fldDate | fldDayOfWeek | fldTour | fldTourWeek |
----------------------------------------------------------
|   1 | 01-Jan-12 |            1 |       0 |           0 |
|   2 | 02-Jan-12 |            2 |       0 |           0 |
|   3 | 03-Jan-12 |            3 |       0 |           0 |
|   4 | 04-Jan-12 |            4 |       0 |           0 |
|   5 | 05-Jan-12 |            5 |       0 |           0 |
|   6 | 06-Jan-12 |            6 |       0 |           0 |
|   7 | 07-Jan-12 |            7 |       0 |           0 |
|   8 | 08-Jan-12 |            1 |       1 |           1 |
|   9 | 09-Jan-12 |            2 |       1 |           1 |
|  10 | 10-Jan-12 |            3 |       1 |           1 |
|  11 | 11-Jan-12 |            4 |       1 |           1 |
|  12 | 12-Jan-12 |            5 |       1 |           1 |
|  13 | 13-Jan-12 |            6 |       1 |           1 |
|  14 | 14-Jan-12 |            7 |       1 |           1 |
|  15 | 15-Jan-12 |            1 |       1 |           2 |
|  16 | 16-Jan-12 |            2 |       1 |           2 |
|  17 | 17-Jan-12 |            3 |       1 |           2 |
|  18 | 18-Jan-12 |            4 |       1 |           2 |
|  19 | 19-Jan-12 |            5 |       1 |           2 |
|  20 | 20-Jan-12 |            6 |       1 |           2 |
|  21 | 21-Jan-12 |            7 |       1 |           2 |
|  22 | 22-Jan-12 |            1 |       1 |           3 |
|  23 | 23-Jan-12 |            2 |       1 |           3 |
|  24 | 24-Jan-12 |            3 |       1 |           3 |
|  25 | 25-Jan-12 |            4 |       1 |           3 |
|  26 | 26-Jan-12 |            5 |       1 |           3 |
|  27 | 27-Jan-12 |            6 |       1 |           3 |
|  28 | 28-Jan-12 |            7 |       1 |           3 |
|  29 | 29-Jan-12 |            1 |       1 |           4 |
|  30 | 30-Jan-12 |            2 |       1 |           4 |
|  31 | 31-Jan-12 |            3 |       1 |           4 |
|  32 | 01-Feb-12 |            4 |       1 |           4 |
|  33 | 02-Feb-12 |            5 |       1 |           4 |
|  34 | 03-Feb-12 |            6 |       1 |           4 |
|  35 | 04-Feb-12 |            7 |       1 |           4 |
|  36 | 05-Feb-12 |            1 |       2 |           1 |
|  37 | 06-Feb-12 |            2 |       2 |           1 |
|  38 | 07-Feb-12 |            3 |       2 |           1 |
|  39 | 08-Feb-12 |            4 |       2 |           1 |
|  40 | 09-Feb-12 |            5 |       2 |           1 |
|  41 | 10-Feb-12 |            6 |       2 |           1 |
|  42 | 11-Feb-12 |            7 |       2 |           1 |
|  43 | 12-Feb-12 |            1 |       2 |           2 |
|  44 | 13-Feb-12 |            2 |       2 |           2 |
|  45 | 14-Feb-12 |            3 |       2 |           2 |
|  46 | 15-Feb-12 |            4 |       2 |           2 |
|  47 | 16-Feb-12 |            5 |       2 |           2 |
|  48 | 17-Feb-12 |            6 |       2 |           2 |
|  49 | 18-Feb-12 |            7 |       2 |           2 |
|  50 | 19-Feb-12 |            1 |       2 |           3 |
|  51 | 20-Feb-12 |            2 |       2 |           3 |
|  52 | 21-Feb-12 |            3 |       2 |           3 |
|  53 | 22-Feb-12 |            4 |       2 |           3 |
|  54 | 23-Feb-12 |            5 |       2 |           3 |
|  55 | 24-Feb-12 |            6 |       2 |           3 |
|  56 | 25-Feb-12 |            7 |       2 |           3 |
|  57 | 26-Feb-12 |            1 |       2 |           4 |
|  58 | 27-Feb-12 |            2 |       2 |           4 |
|  59 | 28-Feb-12 |            3 |       2 |           4 |
|  60 | 29-Feb-12 |            4 |       2 |           4 |
|  61 | 01-Mar-12 |            5 |       2 |           4 |
|  62 | 02-Mar-12 |            6 |       2 |           4 |
|  63 | 03-Mar-12 |            7 |       2 |           4 |
|  64 | 04-Mar-12 |            1 |       3 |           1 |
|  65 | 05-Mar-12 |            2 |       3 |           1 |
|  66 | 06-Mar-12 |            3 |       3 |           1 |
|  67 | 07-Mar-12 |            4 |       3 |           1 |
|  68 | 08-Mar-12 |            5 |       3 |           1 |
|  69 | 09-Mar-12 |            6 |       3 |           1 |
|  70 | 10-Mar-12 |            7 |       3 |           1 |
|  71 | 11-Mar-12 |            1 |       3 |           2 |
|  72 | 12-Mar-12 |            2 |       3 |           2 |
|  73 | 13-Mar-12 |            3 |       3 |           2 |
|  74 | 14-Mar-12 |            4 |       3 |           2 |
|  75 | 15-Mar-12 |            5 |       3 |           2 |
|  76 | 16-Mar-12 |            6 |       3 |           2 |
|  77 | 17-Mar-12 |            7 |       3 |           2 |
|  78 | 18-Mar-12 |            1 |       3 |           3 |
|  79 | 19-Mar-12 |            2 |       3 |           3 |
|  80 | 20-Mar-12 |            3 |       3 |           3 |
|  81 | 21-Mar-12 |            4 |       3 |           3 |
|  82 | 22-Mar-12 |            5 |       3 |           3 |
|  83 | 23-Mar-12 |            6 |       3 |           3 |
|  84 | 24-Mar-12 |            7 |       3 |           3 |
|  85 | 25-Mar-12 |            1 |       3 |           4 |
|  86 | 26-Mar-12 |            2 |       3 |           4 |
|  87 | 27-Mar-12 |            3 |       3 |           4 |
|  88 | 28-Mar-12 |            4 |       3 |           4 |
|  89 | 29-Mar-12 |            5 |       3 |           4 |
|  90 | 30-Mar-12 |            6 |       3 |           4 |
|  91 | 31-Mar-12 |            7 |       3 |           4 |
|  92 | 01-Apr-12 |            1 |       4 |           1 |
|  93 | 02-Apr-12 |            2 |       4 |           1 |
|  94 | 03-Apr-12 |            3 |       4 |           1 |
|  95 | 04-Apr-12 |            4 |       4 |           1 |
|  96 | 05-Apr-12 |            5 |       4 |           1 |
|  97 | 06-Apr-12 |            6 |       4 |           1 |
|  98 | 07-Apr-12 |            7 |       4 |           1 |
|  99 | 08-Apr-12 |            1 |       4 |           2 |
| 100 | 09-Apr-12 |            2 |       4 |           2 |
| 101 | 10-Apr-12 |            3 |       4 |           2 |
| 102 | 11-Apr-12 |            4 |       4 |           2 |
| 103 | 12-Apr-12 |            5 |       4 |           2 |
| 104 | 13-Apr-12 |            6 |       4 |           2 |
| 105 | 14-Apr-12 |            7 |       4 |           2 |
| 106 | 15-Apr-12 |            1 |       4 |           3 |
| 107 | 16-Apr-12 |            2 |       4 |           3 |
| 108 | 17-Apr-12 |            3 |       4 |           3 |
| 109 | 18-Apr-12 |            4 |       4 |           3 |
| 110 | 19-Apr-12 |            5 |       4 |           3 |
| 111 | 20-Apr-12 |            6 |       4 |           3 |
| 112 | 21-Apr-12 |            7 |       4 |           3 |
| 113 | 22-Apr-12 |            1 |       4 |           4 |
| 114 | 23-Apr-12 |            2 |       4 |           4 |
| 115 | 24-Apr-12 |            3 |       4 |           4 |
| 116 | 25-Apr-12 |            4 |       4 |           4 |
| 117 | 26-Apr-12 |            5 |       4 |           4 |
| 118 | 27-Apr-12 |            6 |       4 |           4 |
| 119 | 28-Apr-12 |            7 |       4 |           4 |
----------------------------------------------------------

 

[tcfd1166]

As you mentioned, my calculation is now incorrect. It shows me off by a week (when supposed to be Tour 4 Week 1, it shows Tour 3 still). Can you elaborate further on how to do your example and implement it? "join to it on dates to get the information I need. "

 

[xenou]

You would load the table with all dates of the year, and the remaining information such as day of week, tour, and tour week. Then any query or report with a date in it can be joined to this lookup table, with criteria such as:

...
WHERE 
    MyTableOrQuery.SomeDate = LookupTable.FldDate