thanks for that response
I am having another issue which makes me mad I checked Your calculation and results are in line with my excel calculation. I copied formula from Your calculated field to my data source and none of the results are correct :/.. I have created an extract and would really appreciate if You could take a look what is wrong .. I am pretty sure that yesterday I got it working but can't move on right now
Ok i think that there was some issue with the database as it seems to be working right now sorry for disturbance
Dimitri Blyumin there seems to be a problem with end date and start date being the same day and start date is > 08:00..
Please see highlighter line - 5.5 hours should be 0,5626 of working day and not 0,79..
Yes, it is an error, thank you for spotting.
To fix it, replace the last line of code with this one, which takes care of start times after 8:00
//work days elapsed from 8:00 or Start Date (whichever is later), until End Date (or 17:30, whchever is earlier), as fraction of 9.5 hour work day (MIN([End Date],DATETRUNC('day',[End Date]) + 1/24*17.5) - MAX([Start Date],(DATETRUNC('day',[End Date]) + 1/24*8)) ) /(1/24*(17.5-8))
Great solution Dimitri Blyumin !
There seems to be an issue with dates starting on a Friday and ending on the following monday. They result in a negative amount.
All monday dates are without time, so default on 00:00.
What's happening do you think?