 Former Member

### How to find the SUM of TIME Fields

Hello Experts,

#########################

1. Introduction:

I am trying to make Crystal Reports add several HH:MM:SS TIME fields together so that I can get the SUM.

The name of the table is ChargesTable and the column is TIMESPENT.

So that's: ChargesTable.TIMESPENT.

The database saves time in milliseconds, so I have used a Formula called "Time Spent"...

`Time({ChargesTable.TIMESPENT}/86400000)`

...to convert it into a HH:MM:SS format i.e. 01:00:00

If I don't use the "Time Spent" formula, the times are displayed as number i.e 3600000

#########################

2. Previous Attempts:

I have tried to Insert > Summary > Choose field to summarize...

But! The TimeSpent formula does not provide a SUM option.

Image 1: #########################

3. Formulas:

So, I created two Formulas in an attempt to summarize the fields in another way.

Formula 1:

```TimeVar t:= Time({ChargesTable.TIMESPENT});

(Hour(t) * 3600) + (Minute(t) * 60) + Second(t);
```

Formula 2:

```// '@Formula 1' is the formula created in the 1st formula.
// {AaaUser.FIRST_NAME} is the field used to Group.

NumberVar fin_t   := Sum({@Formula 1}, ({AaaUser.FIRST_NAME}));

NumberVar Hours   := Truncate (fin_t / 3600);

NumberVar Minutes := Truncate (Remainder (fin_t, 3600) / 60);

NumberVar Seconds := Remainder (fin_t, 60) ;

Time(Hours, Minutes, Seconds)
```

Then I place the "Formula 2" field into the report.

And...it does nothing?

Image 2: (The 'Formula 2' is bold and green) - What is going wrong?

Apologies for any obvious mistakes; I am fairly new to Crystal.

If you need more pictures or information, do not hesitate to ask :)

Thanks.

Gareth.

image-1.png (27.0 kB)
image-2.png (7.4 kB)
10|10000 characters needed characters exceeded Former Member
Feb 09, 2017 at 09:52 AM

Hi Gareth,

What happens if you just have Formula3 do:

`Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME});`

I would like to see what comes up. If it's still 0 then I suspect we're running into NULLs. If this is the case, go to File | Report I Options and put a check in Convert NULL to default values. Then see if Formual3 returns a number. If it does then try converting it to a time.

If you get a number then we'll have to find out why we can't convert the number into a time.

Brian Dong

10|10000 characters needed characters exceeded
• Feb 07, 2017 at 09:22 PM

Hi Gareth,

I believe you have the right logic but just need to tune it a bit. Instead of converting the field to a time first, sum the TIMESPENT field then convert the summed amount to a time.

So leave your Formula 1 as is so we can see the detail time. Your second formula would be something like:

```NumberVar TotTime;

TotTime := Sum ({ChargesTable.TIMESPENT}, {ChargesTable.WORKORDERID});
(Hour(TotTime)*3600)+(Minute(TotTime)*60)+ Second(TotTime);```

This assumes you have a group on WORKORDERID. So the TIMESPENT is summed for each WORKORDERID and converted to a time afterwards.

I hope this helps,
Brian

10|10000 characters needed characters exceeded
• Former Member

Hi Brian,

The report Groups on Technician Names; as the point of the report is to see how many hours of work each technician has done.

Image 1: However, if I change the 'Group' to {AaaUser.FIRST_NAME}...

```NumberVar TotTime;

TotTime := Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME}); //Group on the name of the technician
(Hour(TotTime) * 3600) + (Minute(TotTime) * 60) + Second(TotTime);
```

I get an error:

Image 2: If I change the code to:

```NumberVar TotTime;

TotTime := Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME}); //Group on the name of the technician
Time(TotTime);```

The total time is still 00:00:00?

Regards,

Gareth

image-1.png (21.9 kB)
image-2-error.png (14.6 kB)
• Feb 08, 2017 at 03:48 PM

Hi Gareth,

Create a new formula with this code:

`Sum({@Formula 1},{AaaUser.FIRST_NAME})`

Place this on the Group Footer - does it display anything at all?

-Abhilash

10|10000 characters needed characters exceeded
• Former Member Brian Dong

Hi Brian,

The above formula worked!

(I then converted the NUMBER value into a TIME datatype as recommended.)

Image 5: Formula 3 - Code:

```NumberVar s;

s := Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME});

// The database saves time values in milliseconds, so the data needs to be divided by '86400000' first.

Time(s / 86400000);
```

Thanks for your help - much appreciated :D

Regards,

Gareth

image-5.png (5.1 kB)
• Feb 09, 2017 at 08:16 AM

I also suspect it's NULLS.

As Brian said, select 'Default Values for NULLS' and see if it helps.

-Abhilash