Calculate Fractional minutes between half hour intervals with multiple start and end times

[CheeksExcelForum]

Hi All,

Basically I have data that needs to be displayed in half hour intervals. If all shifts started and ended on the half hour dots, it wouldn't be a problem, except not all of them do. So what I'm trying to accomplish here is to calculate how many employees fall within the half hour plus fractional minutes if any that did not make the full half hour to get decimal or fractional count. I've tried sum product, sumifs, but cant get the fractional count. I've attached the spreadsheet.

Any help is appreciated thanks.

HCVR SHIFTS_WITH_DATES_AND_BREAK_TIMESv3.xlsx

[protonLeah]

I don't quite understand: fall within the half hour plus fractional minutes if any that did not make the full half hour to get decimal or fractional count
But you can try:

Please Login or Register  to view this content.

in column H.

[CheeksExcelForum]

Sorry I wasn't clear on that. In the attached file above, columns T & U (column U won't really apply in this case though) are where I'm trying to get the results calculated correctly. I highlighted the half hour intervals where it should have decimal places to account for minutes as well. In column V, I noted what the results should calculate to be when considering the non half hour shift times of <*:00> or <*:30.>

Thank you very much.

[CheeksExcelForum]

Bump no response

[daddylonglegs]

Try this "array formula" in T2

=SUM(IF(L$2:L$100=T$1,IF(G$2:G$100>S3,IF(D$2:D$100<S4,IF(G$2:G$100<S4,G$2:G$100,S4)-IF(D$2:D$100>S3,D$2:D$100,S3)))-IF(F$2:F$100>S3,IF(E$2:E$100<S4,IF(F$2:F$100<S4,F$2:F$100,S4)-IF(E$2:E$100>S3,E$2:E$100,S3)))))/(S4-S3)

confirmed with CTRL+SHIFT+ENTER and copied down column

[CheeksExcelForum]

Perfect! Just what I needed and a formula that is quick to calculated and won't hog processors. Thank you very much!