Rounding Time to the Nearest Quarter Hour

[Hillary]

I have a report in which I need to calculate hours employees worked. The data comes into Crystal as DateTime. Basically the formula assigns a time based on when the employee Clocked In. (Ex. Clock In between 7:50 and 8:08 then 8:00) In the second paragraph of the formula I would like to replace Time({LABOR_TICKET.ACT_CLOCK_IN}) with a formula that adjusts the time to the nearest quarter hour. Ex.
>=8:08 - <8:23 return 8:15
>=8:23 - <8:38 return 8:30
>=8:38 - <8:53 return 8:45
>=8:53 - <9:08 return 9:00

I need this formula for all hours in the day not just the 8 o'clock hour.

If {EMPLOYEE.DEPARTMENT_ID} = &quot;200&quot; and
{LABOR_TICKET.CLOCK_IN} = {LABOR_TICKET.ACT_CLOCK_IN} and
Time({LABOR_TICKET.ACT_CLOCK_IN}) < Time(8,08,00) and
Time({LABOR_TICKET.ACT_CLOCK_IN}) >= Time(7,50,00)
then Time(8,00,00) else

If {EMPLOYEE.DEPARTMENT_ID} = &quot;200&quot; and
{LABOR_TICKET.CLOCK_IN} = {LABOR_TICKET.ACT_CLOCK_IN} and
Time({LABOR_TICKET.ACT_CLOCK_IN}) >= Time(8,08,00)
then Time({LABOR_TICKET.ACT_CLOCK_IN}) else

If {EMPLOYEE.DEPARTMENT_ID} = &quot;200&quot; and
{LABOR_TICKET.CLOCK_IN} <> {LABOR_TICKET.ACT_CLOCK_IN} then
Time({LABOR_TICKET.CLOCK_IN})

Thank you very much for your help!

Hillary

 

[Reebo99]

Check this out, Thread767-491558, it has all the details on how to do this.

Enjoy!! Reebo
Scotland (Going mad in the mist!)

 

[Hillary]

Reebo,

Thanks for your response.

I can calculate the final time using a spin of Timmah13's formula. (Round((({@Clock Out}-{@Clock In})/3600)*4))/4 What will make this report awesome is to have the rounding done before this step but I can't figure out how to convert Time({LABOR_TICKET.ACT_CLOCK_IN}) (see above) into an integer so I can apply this formula to it. When I insert {@Clock Out}-{@Clock In} Crystal automatically converts time into an integer (seconds). After I convert it into an integer, round the number, I will also need to convert it back into Time(hh:mm:ss) format.

Thank you very much for your help!
Hillary

 

[mbarron]

Here the method I employ:

numbervar secs;
numbervar hr;
numbervar min;
numbervar sec;

secs:={your.seconds}


hr:=truncate(secs/3600);
min:=truncate((secs/3600-hr)*60);
sec:=(((secs/3600-hr)*60)-min)*60;
totext(hr,&quot;00&quot&&quot;:&quot;&totext(min,&quot;00&quot&&quot;:&quot;&totext(sec,&quot;00&quot Mike