Excel help required - please

SexyGreyFox · 7 Jan 2009 at 13:45

Here is a timesheet I'm working on - Timesheet

People log their stop and start times into the 3 columns and the total time is added on the right column.
I would now like to add up all the time in the pink column so it shows at the bottom.
I've tried a SUM command and I've tried a long winded method of +r7+r8 etc but I can't find a method that will add up that column correctly.
I can add 2 of them together but as soon as I try 3 or more the answer goes haywire.
At the moment there are only 4 figures to add up which should come to 32:45

Also in cell v47 I wanted to add a formula that would subtract v46 away from v45 and multiply it by 7.5 but I can't do that either.

Any suggestions?

Thanks

SexyGreyFox · 7 Jan 2009 at 14:01

Hodders said:
[hh]:mm and that should see you right (the []s make the hours wrap so that 1 day 4 hours is displayed as 28:00 which is what I am guessing you want).

That worked - thanks a lot

SexyGreyFox · 7 Jan 2009 at 20:24

Here is an updated timesheet I'm working on - Timesheet

My problem is that I want to subtract y15 from w15 but as you can see one of them is a number and the other is the time [hh]:00

Is there any way of converting one or the other so that I can subtract them even if I use another cell to do it?

SexyGreyFox · 7 Jan 2009 at 21:10

Cobertizo said:
Something like "=TIME(W15,0,0)-Y15"?

That didn't work.
the answer should be -0.25

SexyGreyFox · 7 Jan 2009 at 21:31

Cobertizo said:
Fair enough. Was trying it in OpenOffice. Seems Excel doesn't like negative times. What about =W15-(Y15*24) formatted as general?

You beauty

Thanks

SexyGreyFox · 8 Jan 2009 at 23:36

Here is the 99% finished article with all suggestions - Timesheet

It works a treat and my bosses think I'm a God.

Thanks

SexyGreyFox · 9 Jan 2009 at 18:08

Tomsk said:
Have you locked and protected the non-yellow cells?

No - good idea