Hi All,
I'm looking to find the best excel function to get the number of minutes I have for each case by hour (would also need to account for midnight rule). Please see attached workbook example with yellow highlighted cells being the expected output. Also - for the second example you can see that the case began right before midnight and ended at 1a. So minutes are also counted in column Y (23:00)
Case Minutes.xlsx
Really hitting a roadblock with this - any help would be appreciated!!
Last edited by excelhelp99; 07-13-2023 at 06:35 PM.
Please read the yellow banner at the top of page and attach a good sample workbook - please remember to enter EXPECTED results
Hi Gregb11 - please see attachedCase Minutes.xlsx
hi there, try this formula to copy and drag from C3:
I hope it result useful.
=IF(C$2<$B3, 1440*MIN(MAX(, C$2+1/24-$A3+($A3>$B3)), MAX(, MOD($B3-C$2, 1)), 1/24), "")
Hi ExceLogan - thank you! It looks like the formula generally works but doesn't work for cases that began the day before...i.e. not counting minutes for the day before at all. Is there any fix for that?
Try the following modification to ExceLogan's formula:Formula:
=IF(C$2<$B3, 1440*MIN(MAX(, C$2+1/24-$A3+($A3>$B3)), MAX(, MOD($B3-C$2, 1)), 1/24), IF(AND($A3>$B3,SUM(C$2,1/24)>$A3),1440*MIN(1/24,SUM(C$2,1/24)-$A3),""))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
This worked! Thank you! One more question - this formula does not account for cases beginning and ending in the same hour, is there a way to add that to the formula? As an example, I can have a case begin at 12:06 and end at 12:25, but it will say the total duration during 12:00 is 25 minutes not 19.
Really appreciate all your help!
This seems to work:Formula:=IF(C$2<$B3, 1440*MIN(MAX(, C$2+1/24-$A3+($A3>$B3)), MAX(, MOD($B3-C$2, 1)), MAX(, MOD($B3-$A3, 1)), 1/24), IF(AND($A3>$B3,SUM(C$2,1/24)>$A3),1440*MIN(1/24,SUM(C$2,1/24)-$A3),""))
Let us know if you have any questions.
This worked!!! Thank you!!
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks