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

Hodders · 7 Jan 2009 at 13:54

I think the problem you have is that the cell formats are a mix of 'number' and 'time'

Excel turns hours into 'days' when it puts the result in a 'number' formatted cell.

Try setting the format to the result cells as a custom date/time format of [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).

Hodders · 7 Jan 2009 at 13:56

Also watch multiplication/addition as 5 * 4:50 will be seen a 24 * 4:50 as 5 will be cast into time which will be 24 hours * 5

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?

Cobertizo · 7 Jan 2009 at 20:42

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

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

Cobertizo · 7 Jan 2009 at 21:23

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

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

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

Nanobot · 8 Jan 2009 at 23:43

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

Thanks

Looks good, you've have every flexi company downloading it if you're not careful!

Tomsk · 9 Jan 2009 at 09:49

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

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

Thanks

Have you locked and protected the non-yellow cells?

SexyGreyFox · 9 Jan 2009 at 18:08

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

No - good idea