Formula to pull start and end times between start and end Dates

[RedSparks]

Crystal 8.5

I have created a timesheet that with formulas determines the dates and days for a pay period. Next, I need to pull in the scheduled times that relate to those timesheet dates.

Each individuals schedule may change at any time. If I have an individuals who's schedule changes during the pay period I need their timesheet to reflect both schedules to the appropriate dates.

For Example:

Date Day Work Schedule
4/17/06 Mon 8:45 to 1:45
4/18/06 Tue 8:45 to 1:45
4/24/06 Mon 9:00 to 2:00
4/25/06 Tue 9:00 to 2:00

The date & day is calculated based on the ?StartDate parameter.

The formula I have for pulling in the schedule is as follows:

if(({FD__FGP_ClientHours.Start_Date} in {@Day2} to {?EndDate}) or
({FD__FGP_ClientHours.Start_Date}<={@Day2} and isnull({FD__FGP_ClientHours.End_Date})) or
({FD__FGP_ClientHours.Start_Date} <= {@Day2} and {FD__FGP_ClientHours.End_Date}in {@Day2} to {?EndDate}) or
({FD__FGP_ClientHours.Start_Date}<={@Day2} and {FD__FGP_ClientHours.End_Date}>={?EndDate}))

then



(if {@DayOfWk2}="Mon" and {FD__FGP_ClientHours.Monday}="T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else if {@DayOfWk2}="Tue" and {FD__FGP_ClientHours.Tuesday}="T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else if {@DayOfWk2}="Wed" and {FD__FGP_ClientHours.Wednesday}="T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else if {@DayOfWk2}="Thu" and {FD__FGP_ClientHours.Thursday}="T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else if {@DayOfWk2}="Fri" and {FD__FGP_ClientHours.Friday} = "T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else if {@DayOfWk2}="Sat" and {FD__FGP_ClientHours.Saturday} = "T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else if {@DayOfWk2}="Sun" and {FD__FGP_ClientHours.Sunday} = "T" then
totext(time({FD__FGP_ClientHours.HoursFrom}),"h:mm")+" - "+totext(Time({FD__FGP_ClientHours.HoursTo}),"h:mm")

else "")

The formula is only bringing back the 9 to 2 schedule. I would appreciate assistance on getting this figured out.

Thanks!

 

[Madawc]

First, you should be using DatePart, which includes hours and minutes. You've shown some ingenuity in adapting ToText to perform the same function, but it was designed for display and it's not surprising it doesn't entirely work.

Create formula fields that get the dates, hours and minutes. Then additional formula fields to test if they are in range. Do boolians - tests without an 'IF', meaning that they return True or False. Display these on the test report, to ensure that they give the answers you want. Then use them in your record selection - a boolian can be referenced by name, meaning the record is selected when the boolian test is true.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[RedSparks]

This is not helping to bring back the appropriate records between start and end dates. How do I force it to read my criteria and bring back the correct times between the start and end dates?

 

[lbass]

I think we would have to know the content of your nested formulas to be able to help.

-LB

 

[RedSparks]

{@Day2}= DateAdd ("d",1,{?StartDate})
{@DayofWk2}= WeekdayName (DayOfWeek ({@Day2}),True)

What else do you need to know in order to be able to assist me with this issue?