Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a Query i think it is so easy but i am stuck on it little bit I use Interval match on that it works but not perfectly.
Query is i want to calculate work hours between two times Start and End, i am able to calculate hour difference but not accurate see sample example like
MON-FRI | SAT | SUN | |
---|---|---|---|
A | 0:15 | 4:00 | 0 |
B | 0 | 2:00 | 0 |
C | 2:00 | 0 | 0 |
D | 0 | 5:00 | 0 |
E | 0 | 0:48 | 5:00 |
F | 0 | 0 | 5:00 |
EXAMPLE:-
START | End | DIFF | FRI Time | Sat Time |
---|---|---|---|---|
9/23/201111:45:00 PM | 9/24/20114:00:00 AM | 4:15:00 | 00:15:00 | 4:00:00 |
How to calculate this please provide details
See my sample file.
Rgds
Anand
Anand,
it seems that I am stucked too...
Could you point me to the inaccuracy in your example?
Hi,
My point is in example a 00:15 mins is for Friday and 4:00 hours for saturday when i use Interval match it shows 4:15 but 15 mins are for friday i want only Saturday time and if it is Sunday so that time.Because 15 mins are come under working days which is 15 mins we are not take it under 24th date but it is of 23th date.
What is the best way to achieve this i try so many ways but not get acurate result.
Let me know
Rgds
Anand
Anand,
not sure what the best solution is, but I used a while loop over the records in the script to get a record for each single weekday.
Then just parsed the hours worked each day into hoursworked using some if()s.
Could be maybe more elegant, but it works.
Stefan
Stefan,
Thanks so much its good way that you provide but any other way we can generate and show the values like in this example, In this way i want output some thing like this.
MON-FRI | SAT | SUN | |
---|---|---|---|
A | 0:15 | 4:00 | 0 |
B | 0 | 2:00 | 0 |
C | 2:00 | 0 | 0 |
D | 0 | 5:00 | 0 |
E | 0 | 0:48 | 5:00 |
F | 0 | 0 | 5:00 |
Rgds
Anand
Sure,
use
=if(Day < 5,'Mon - Fri', Day)
as calculated dimension in the chart (or similar in the script).
This gives you exactely your above table.