Minutes worked per hour
Hello folks, I am hoping someone can help me as I've searched forums and wrangled with CoPilot to no avail so far! I have timesheet data nicely formatted with a START_TIME and END_TIME and needed to show this in a table with NAME and CALENDAR_DAY and then columns from 00 to 23 to shade in when they have worked accross those hours. I have done this part using the DAX: 06 =IF(AND(HOUR(Timesheets[START_TIME Floor])<=06,HOUR(Timesheets[END_TIME Ceiling])>06),1,BLANK()) (obviously, 06 adjusted to each hour, then conditional formatting to shade a colour where '1') However, for the Start and End I need to shade a different colour if they don't start/end right on the hour. I thought the best way is to amend the DAX above to just show the number of minutes worked per hour and apply conditoinal formatting to that instead. But I can't get this to work. I used this DAX: 07 = SUMX( FILTER( Timesheets, Timesheets[START_TIME Revised] < TIME(8, 0, 0) && Timesheets[END_TIME Revised] > TIME(7, 0, 0) ), VAR StartTime = MAX(Timesheets[START_TIME Revised], TIME(7, 0, 0)) VAR EndTime = MIN(Timesheets[END_TIME Revised], TIME(8, 0, 0)) VAR MinutesWorked = IF( StartTime < EndTime, DATEDIFF(StartTime, EndTime, MINUTE), 0 ) RETURN MinutesWorked ) However, this gives a wild number as you can see: Essentially, the screenshot above just returns a 1 or 0 if someone worked in that hour, but i'd like to get the number of minutes worked in that hour. For example, if someone worked 09:30 to 13:00 it would show 30,60,60,60 accross the four columns. Thank you for any help in advance 🙂
