calculating minutes within a certain time range

[marilyn]

Happy New Year! I need help calculating total minutes within a time range (including start and finish time) on certain weekdays. Please see attached file. Thank you!

 

[vletm]

marilyn
One sample ...
... are all those times valid?
... is there still max 24hrs per day?
... if some time ranges are like night shifts then this needs modification.

 

[marilyn]

marilyn
One sample ...
... are all those times valid?
... is there still max 24hrs per day?
... if some time ranges are like night shifts then this needs modification.

Thank you,
1) the times are valid, but I had to change some from 1:00 am to 25:00, for example, for another macro I have to work
2) still max 24 hours in a day
3) some shift time occurs during the night (after 5pm), which would likely fall out of range and not be counted unless they continued past 7:30am the next day

 

[marilyn]

Thank you for looking at this

 

[vletm]

marilyn
... hmm?
If times are valid as You've written then why need to have another macro to do something?
What kind of clock shows 25:00?
If shift starts after 5pm and ends ... next day then end time should be record to next day.
... Yes, I looked and wondered - what? ... how?

 

[GraH - Guido]

Maybe with some helper columns

• [D2]=WEEKDAY(A2,2)
• [E2]=VALUE(B2)
• [F2]=MOD(VALUE(C3),1)
• [G2]=(IF(F2<E2,TIME(17,0,0),IF(F2<TIME(7,30,0),TIME(7,30,0),MIN(F2,TIME(17,0,0))))-MIN(TIME(17,0,0),MAX(E2,TIME(7,30,0))))*24*60
• [H2]=(IF(F2<E2,TIME(17,0,0),IF(F2<TIME(8,30,0),TIME(8,30,0),MIN(F2,TIME(17,0,0))))-MIN(TIME(17,0,0),MAX(E2,TIME(8,30,0))))*24*60
• [K3]=SUMIFS(G:G,D:D,"<>3",D:D,"<6")
• [K7] =SUMIFS(H:H,D:D,3)

 

[marilyn]

Thank you very much this is so helpful.