Accounting for hours in a workorder by month when workorder spans multiple

[Former Member]

Hello, I have a crystal report that tracks equipment downtime/availability. The report selects records for the past year and sorts them by month, I need to calculate the monthly downtime hours and equipment availability for each piece of equipment. Works fine when the start date and end date are in the same month. If the end date spans 1 or more months I have calculated the hours for that month in which the recored started. But how do I account for the hours in the next month (s). ie

Jul

WO#--

---

Startdate

---

Enddate--

---

Total hours

1--

---

2010-07-09 10:00

---

2010-07-27 17:24--

---

439.4

2--

---

2010-07-30 15:50

---

2010-09-23 12:29--

---

8.17

In the above WO#1 is started and ends in the same month - no problem

WO#2 starts in Jul but ends in Sep, I've calculated the hours to the end of Jul but what about the hours in Aug and Sep.

Can I have the WO show up in different months if the end date spans more than one month. or how would I account for the hours in Aug and Sep?

Any suggestion appriciated.

[Former Member · Accepted solution]

Hi

You can find out hours between two dates for any month or months(s). Try the following formula :

datediff('h',, {?Enter date})

This will give you total number of hours between two dates.

Thanks,

Sastry

[Former Member · Accepted solution]

Yes I understand that you can find the difference between 2 dates using Datediff. However the problem is accounting for the time in each month. i.e

A workorder starts on 16 jul 2010, the technician works on it until 23 jul 2010, another technician then works on it from the 23 Jul 2010 until 13 Aug 2010, still another technician works on it from the 13 Aug 2010 to 05 Sep 2010 and lastly another technician works on it from the 05 Sep 2010 to the 10 Dec 2010.

The report is returning records for the last year from current date , grouped by month. I need to calculate the hrs the workorder was active for each month.(The Dates used are fields in database)

so for Jul , datediff( "h",#2010/07/16#),#2010/07/23#),+ datediff( "h",#2010/07/23#),#2010/07/31#)

for aug datediff( "h",#2010/08/01#),#2010/08/13#), datediff( "h",#2010/08/13#),#2010/08/31#),

for sep datediff( "h",#2010/09/01#),#2010/09/05#), datediff( "h",#2010/09/05#),#2010/09/31#),

for oct

for nov

for dec

so the problem is because the records are grouped by month and the workorder hours span multiple months how do I account for the hours in the groups of Oct, Nov, Dec.

Help please, this is driving me nuts!!!!!!

[sabine_henderson · Accepted solution]

group your data by date and display that group for each month.

if worker A worked 5 hrs on 3 days in June

if worker B worked 2 hrs on 1 day in May

you should be able to easily see the info in a crosstab by summerizing the hrs spent working on the equipment

row = worker

column = days, grouped for each month

summary of time spent