How do you subtract one date time from another and display weekdays.

[dg3249]

I have a start date and an end date field. I am trying to print out the days of the week that a person worked using the two above fields..

 

[kenhamady]

See the first formula on faq149-243 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

I'm not sure if this is exactly what I need. I am trying to print out people's names that are working on a specific day. i.e. Listing the peoples names that are working on Mon, Tues, Wed.... using the start and end date fields. I hope that this clarifies what I need.

 

[kenhamady]

Are startdate and enddate parameters or fields from the database? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

They are fields in the database.

 

[kenhamady]

Not sure I follow. You have one record per person, and each record has a start and end date?

And you want to know who was working on a Monday. How will the report know which Monday - does the report target a specific week? How is the week specified, or is it always the current week? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

Esentially what I need to do is this;
I have an order, and each order has one or more people assigned to it. This order has a start date field and a finish date field ( the finish date field may be 3 months from now). What the client is requesting is the ability to run a report showing who is working on a given week. For example, Joe Smith works from Aug 19 - Aug 30. I need to show his name and all others that are working for a specific client each day of the week.

 

[kenhamady]

Here is one approach:
You prompt the user for the Monday of the selected week.
You calculate the other 4 workdays by using formulas:

//{@tuesday}
{?PromptDate} + 1

//{@wednesday}
{?PromptDate} + 2


Then you can write a formula for each day of the week that uses the person's start and end dates for this project to decide if their name should print:

If {@tuesday} in {StartDate} to {EndDate}
then {Person's Name}else "" Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

Thank you Ken for your help, your suggestion worked!!

 

[dg3249]

It seems now that the client does not want the report to prompt for a parameter, instead they would like to show all data for a month period. Your help would be greatly appreciated.

 

[kenhamady]

I would use the same report again as a subreport, once for each week of the month. Prompt for the first week, and calcluate the other weeks from that. Pass these values as links to the subreports. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

Is there any way to do this without prompting for any data. It seems that I cannot post a parameterized report to out website.
Can you do Ending Date - Starting Date = @Date to find out the dates that this order is for?
Diana

 

[kenhamady]

I thought those were fields in every record. How will you know which month to run if every record has different start dates? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

This is where I run into problems. I have multiple orders which have different Start and Finish dates and my client would like the report to show for each week in a month period who is working on a given day.

 

[kenhamady]

If you don't prompt for a month, how will the report know what month you are looking for? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dg3249]

I wanted the report to group by month and then by week up to the current week. Could you do last 4 weeks to date?

 

[kenhamady]

Sure, you can calculate the last 4 weeks from the CurrentDate function. Then calcualte the start and end of each of these weeks, and use them in each of the subreports. Are you familiar with how to link a subreport using a paramater field in the subreport? You will need this technique to have each subreport read a different week.

You can't use grouping because you want the same detail records to show up in different weeks. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com