Rota - Hourly Calculation

[Premy]

Hello all,

I am a total newb when it comes to Excel and I struggle daily with it!

My question is, I have attached my attempt at a weekly rota that counts the aTester Rota.xlsmount of hours (two sets of times in each one cell here) in each cell+row for a weekly total and then below a daily total. I don't imagine to know anything about formulas and the one I am using for the weekly totals is something from another section on this forum. The problem with that formula is that I can't seem to extend it to the other cell range I want.

Any help would be greatly appreciated.

Many Thanks

Premy

Attachment 189420

[BeachRock]

Hi Premy,

If you just want H5 and the cells below it to add up the hours to the left of them, put the following formula into Cell H5 in place of what you have. Copy the formula down through H14.

=((MID(G5,6,2)&":"&RIGHT(G5,2)+0)-(LEFT(G5,2)&":"&MID(G5,3,2)+0))*24+((MID(F5,6,2)&":"&RIGHT(F5,2)+0)-(LEFT(F5,2)&":"&MID(F5,3,2)+0))*24+((MID(E5,6,2)&":"&RIGHT(E5,2)+0)-(LEFT(E5,2)&":"&MID(E5,3,2)+0))*24+((MID(D5,6,2)&":"&RIGHT(D5,2)+0)-(LEFT(D5,2)&":"&MID(D5,3,2)+0))*24+((MID(C5,6,2)&":"&RIGHT(C5,2)+0)-(LEFT(C5,2)&":"&MID(C5,3,2)+0))*24+((MID(B5,6,2)&":"&RIGHT(B5,2)+0)-(LEFT(B5,2)&":"&MID(B5,3,2)+0))*24

[Premy]

Ignore that! I was being silly!

Thank you so much. How would I do it so downwards it would count the days hours?

Thanks

[Premy]

Hey,

I tried that but it just comes up with 38 as the total, even though 38 is not the total?

[BeachRock]

38 is the total I get and when I add them up:

Monday = 6
Tuesday = 6
Wednesday = 6
Thursday = 6
Friday = 6
Saturday = 8

Altogether = 38

Maybe I'm not understanding what your desired outcome is supposed to be?? What should the total be in cell H5?

[BeachRock]

No worries Premy.

I'll see what I can do for the day totals.

[Premy]

Ok,

So the problem I have now is it takes all the numbers from B1,C1,D1 etc when I am putting it in H6, H7 etc instead of H6=Left of=B7,C7,D7,E7,F7. Is there any way of getting those letters and numbers to automatically change so it picks up the right cells in the formula?

Thanks!

[BeachRock]

Hi Premy,

Please put the following formula into Cell B16 and then hold down Ctrl+Shift and hit the Enter key. Then copy that cell, highlight cells C16 through G16 and right click them, past special - formula.

=SUM((MID(B5:B14,FIND("-",B5:B14)+1,20))-(LEFT(B5:B14,FIND("-",B5:B14)-1)))/(100)

That should do it for all 6 columns.

[BeachRock]

I added the example I've been working with into my previous post. Please take a look at it. All of the columns add up correctly.

[Premy]

Wow, you are a star! Thank you so so much!

[BeachRock]

Thank you, Premy. I'm glad I could help.

If this has completely solved your problem, please make sure to mark your thread as Solved using the Thread Tools menu at the top of your first post.