Hello everyone,
I am currently using a metric to calculate the duration between 2 Timestamps :
Interval(Max([TIMESTMP])-Min([TIMESTMP]),'h:m'
If I select a specific day, my value is fine => I obtain the number of hours of working during the day selected.
MY PROBLEM is that I want to calculate the number of working hours over several days and if I select more than 1 day the metric that I use will include the time between last timestamp of today and first timestamp of tomorrow. As a consequence I obtain a much bigger duration that I want.
To clarify a bit, what I want is to be able to sum the different durations that I obtain for each separate days instead of measuring the time between the first timestamp of the first day and the last timestamp of the last day.
Illustration:
| Transaction Number | Date | Hour |
|---|
| x1 | 1-AUG-15 | 8:00 |
| x2 | 1-AUG-15 | 12:00 |
| x3 | 1-AUG-15 | 16:00 |
| x4 | 2-AUG-15 | 9:00 |
| x5 | 2-AUG-15 | 12:00 |
| x6 | 3-AUG-15 | 8:00 |
| x7 | 3-AUG-15 | 14:00 |
For example in this case, the metric i'm currently using gives me the whole time between 8:00 on 1st august and 14:00 on 3rd august => 54 hours (between those 2 hours)
WHAT I WANT is a formula that would give me : 8 (hours of 1st day) + 3 (2nd day) + 6 (3rd Day) = 17 hours of Actual working.
If you can help me I'd be really graceful.
Have a good day !
Alexandre BERNARD
