Time Calculations

[matix10]

Hi All,

I have a similar data in my report.

Scenario:

1. Same Case having multiple Work Lines of different types Travel, Online Support, Repair

2. Need to calculate Total Travel Hours, Total Site Hours based of Start time and End Time.

3. For same Case if we have multiple Work Lines with same Owner Name then we have to calculate Total Hours based on calculated Total Travel Hours and Total Site Hours. (Highlighted in Blue)

4. For same Case if we have multiple Work Lines with different Owner Name then we have to calculate Total Hours based on calculated Total Travel Hours and Total Site Hours separately. (Highlighted in Yellow and Pink)

Can someone please help me with this? Is it possible to calculate this without Break?

Thanks in advance!

Regards,

Bhagya

[matix10]

Hi ayman.salem Sir,

Could you please help me ?

Thanks in advance!

[ayman_salem]

here is a quick solution:

...

what I did not understand why you made a "sum" of the hours (marked in yellow). it has a different case number.

...

Variables used in the table:

v_TravelHours: =TimeBetween([Start Time];[End Time]; MinutePeriod)/60

v_TotalTravelHours: =Sum([v_TravelHours] ForEach ([Owner Name]) Where ([Type] = "Travel"))

v_TotalSitelHours: =Sum([v_TravelHours] ForEach ([Owner Name]) Where ([Type] <> "Travel"))

v_TotalHours: =Sum([v_TotalTravelHours] + [v_TotalSitelHours]) In ([Owner Name]; [Case])

...

hope that gives you an idea for the solution.

[matix10]

Hi Sir,

Thank you so much for your swift reply!

Small Correction in sample data. (updated sample data attached in question)

For Case '23589034' we have three Work Lines of different type (Travel, Online Support, Repair). So here, I have to calculate Total Travel Time of type Travel, Total Site Hours (sum of Online Support & Repair types collectively) as we have same Owner for this Case. Total Hours will be sum of Total Travel Hour and Total Site Hour.

Hence, Total Hours for 23589034 will be sum of 1+0.5+2 = 3.5.

Similarly, for Case '23374598', we have three Work Lines of different type (Travel, Online Support, Repair) and two different owner. So in this case, We will calculate time of different type collectively for same owner [Travel + Online Support = Total Hours (as we have same Owner for these types)]

For same Case for second owner, we will calculate Travel Time separately.

Regards,

Bhagya

[ayman_salem]

So it makes sense, as does the mistake in the sample data.

so the result

[matix10]

Hi Sir,

I am getting #MULTIVALUE error for some cells of Total Travel Hours.

Thanks,

Bhagya

[ayman_salem]

try =Sum( [v_TravelHours] ) ForEach ([Owner Name]) Where ([Type] = "Travel")

[matix10]

Hi Sir,

v_TotalTravelHours is working fine now. But I am getting #ERROR in some cells for v_TotalHours.

I have used below formula for v_TravelHours.

=TimeBetween(ToDate([Start Time]; "INPUT_DATE_TIME");ToDate([End time]; "INPUT_DATE_TIME"); MinutePeriod)/60

Same report with these variables is working fine on my friend's system. I have checked the syntax and tried with different formulae as well but getting same error. Is it something related to format type?

Thanks,

Bhagya

[ayman_salem]

Yes, it seems that the format you are using is not compatible with the date you are reading (e.g. the date is in the format "dd/MM/yyyy" and you are using "MM/dd/yyyy")

[matix10]

Thank you so much for all your help Sir!

Regards,

Bhagya

[matix10]

Hi ayman.salem Sir,

I tried reading date in "dd/MM/yyyy" format but still getting #Error in some cells of report.

Tried with several ways but few cells of the report still contains #ERROR. Could you please help me with this as this is urgent?

=TimeBetween(ToDate([Start Date and Time]; "dd-Mmmm-yy");ToDate([ End time]; "dd-Mmmm-yy"); MinutePeriod)/60

Thanks,

Bhagya

[ayman_salem]

It seems that the problem is in your data, check your data and correct it.