Time troubles

Hi all,

I just got a job as a manager in a shop, part of my job is to organize a rota. I do this, but the old rota is crude and time consuming... so i made a new one.

What i really wanted is not only to have a start/stop time but have the hours clocked up in that shift, then to take 30 mins off for break.

Then to add the total hours so i can lazily check i haven't given out overtime inadvertently.

I have done all of this but with errors. I got the times and hours worked sorted, then the breaks taken off (after i found that 0.020833 is 30 mins).

The problem i have is when i add them all up i dont get the right total. I think it is because I have left 2 shifts blank (resulting in #####) for the rest days coz they differ week from week.

So is there a way of getting rid of this #### without having to change the code week by week.

I have attached a shorter version for you to look at, please dont do the work for me but tell me how to do it myself (or how can i learn?)

Thanks in advance

Jaffa

Hello Jaffa,

Rather than use a number like 0.020833 you can just use "0:30" in the formula, i.e.

=O5-N5-"0:30"

##### represents negative time (because you are still subtracting 30 minutes from a zero length shift when O5 and N5 are both zero).

If there is no shift worked I'd be inclined to leave O5 and N5 blank (rather than put zeroes in) and then use this extension of the above

=IF(COUNT(N5,O5)=2,O5-N5-"0:30","")

...or you could stick with the original formula but ensure that it can't return a values less than zero, i.e.

=MAX(0,O5-N5-"0:30")

If you have a blank returned by formula in P5 and others then to make B13 formula work correctly change to

=SUM(D5,G5,J5,M5,P5,S5)

a tip
if you multiply time by 24
it'll give it in decimal so time 00:30(30 mins) will change to 0.5 10:40 will change to 10.67 useful for calculating hourly pay(format cell as number))

thanks allot that should just about do it for me!

just one other thing, how can you display negative numbers, ie I have a girl contracted 28 hours, I add all her hours up and have a box indicating the total.
Then to make life easier I subtract the contracted hours, if they are below 28 i will know how much by so as not to over pay them.

At the moment #### is all i get

thanks again!