Forum Discussion

Wesley1979's avatar
Wesley1979
Frequent Visitor
2 years ago
Solved

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 🙂

DAX
time calculation
Working Time
  • Wesley1979's avatar
    Wesley1979
    2 years ago

    Thank you for your reply. Unfortunately, the data used I am unable to get to upload.


    On a birghter note, I managed to resolve using:
    04 = IF(
    HOUR(Timesheets[START_TIME Revised]) = 4 &&
    MINUTE(Timesheets[START_TIME Revised]) <> 0,
    60 - MINUTE(Timesheets[START_TIME Revised]) +
    IF(
    HOUR(Timesheets[END_TIME Revised]) = 4,
    MINUTE(Timesheets[END_TIME Revised]),
    0
    ),
    IF(
    HOUR(Timesheets[START_TIME Revised]) <= 4 &&
    HOUR(Timesheets[END_TIME Revised]) > 4,
    60,
    IF(
    HOUR(Timesheets[END_TIME Revised]) = 4,
    MINUTE(Timesheets[END_TIME Revised]),
    BLANK()
    )
    )
    )

    Thanks for any views. Worm Regards ğŸ™‚

2 Replies