Dates on a delivery schedule

[sjf127]

Hi, we have set customers up on a delivery schedule, i.e., customer A gets a delivery every Monday, Wednesday, Friday and Customer B gets a delivery on Tuesday and Thursday.

I am attempting to build a report to show orders placed since the last delivery day. The delivery days are currently in an Excel file which I create so the layout is flexible.

Currently I have 2 worksheets that I'm using. The first sheet contains roughly 250 customers and 10 route names. It looks something like this:


Cust Name[tab]Route Name
Customer A[tab]North Route
Customer B[tab]South Route
Customer C[tab]North Route
Customer D[tab]East Route


The second sheet contains the route names, the delivery day, and the corresponding previous delivery day like the following:

Route Name[tab]Delivery Day[tab]Previous Day
North Route [tab]Monday [tab][tab] Friday
North Route [tab]Wednesday[tab][tab]Monday
North Route [tab]Friday [tab][tab] Wednesday
South Route[tab]Tuesday [tab][tab] Thursday
South Route[tab]Thursday [tab][tab] Tuesday

I am stuck figuring out how to get a weekday name converted to a date. For example, my Excel spreadsheet says that the last delivery day for the customer was Tuesday and Today is Thursday, how can I get Crystal to calculate the date for Tuesday? I know that I could use a DateDiff formula but there are too many combinations of delivery days to hardcode. By the way, I am using Crystal 11. Any help will be greatly appreciated.

 

[SkipVought]

Hi,

Rather than a string value for day of the week, use a numeric value to indicate the day of week (0 to 6) as iDOW.

You can calculate a beginning of week DATE. Then add the iDOW.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[sjf127]

I've messed around with converting the string to a number and that works. I've never heard of iDOW. I searched the web for it and can't find anything very descriptive, any thoughts on how I can use this? Thanks for your help!