Need help with Excel need certain formulas

colin300

Right apologies if this is in the wrong place but i couldn't find a forum suitable for this apart from here.


Right i want to create a excel document for calculating my hours worked and my total pay. When I put in my hours and put in the subtract formula the hours come out right. Say i worked from 09:00 till 19:00 it comes up with 08:00 hours so then if i put in all my week it comes out with different hours worked for each day according to how long i worked for which is right. But when i make a place for total hours worked i have the following hours to be added 08:00 + 08:00 + 05:15 + 05:15 which equals to with the formula =sum(blabla) 02:30. What i want to know is there anyway of making it add up to 26:30??

Also what i want to do after u have added it up i want the hours multiplied by my wage lets say 10€ an hour to make it easy which would be 263€. Any ideas??


Also it is isn't important but i would like to be able to deduct the low tax rate 20% from the total???


Again sorry if its in the wrong place just couldn't find another place to put it.


Thanks in advance for helping.

tba

you have to change the number format for your hours cell

colin300

To what cause when i change it to general i get 0.3333333 plus how would i get the quarter hours and half hours?

Zynks

colin300 wrote:

i have the following hours to be added 08:00 + 08:00 + 05:15 + 05:15 which equals to with the formula =sum(blabla) 02:30. What i want to know is there anyway of making it add up to 26:30??

The problem is that it is telling you the time of the day it will be after you add all those hours, starting at 00:00.

Solution: Right click on the cell where you want the result, select Format Cells -> Number -> Select Custom in the Category list and enter this in the Type box:

[h]:mm

colin300 wrote:

Also what i want to do after u have added it up i want the hours multiplied by my wage lets say 10€ an hour to make it easy which would be 263€. Any ideas??

Also it is isn't important but i would like to be able to deduct the low tax rate 20% from the total???

Next cell under the result, make it equal to the cell where you got the result multiplied by 24 (say =B24*24), then Right click on the cell , select Format Cells -> Number -> Select General.

Now you have the total number of full hours and decimals.

In the next cell enter the formula =<the last cell>*<hourly fee>

That should work

colin300

cheers the thing u said about the [h]:mm worked exactly right enjoy your dinner!!!!!

Zynks

I just edited my response with the rest of the information. Let me know the result.

colin300

yep all worked to plan cheers for that.

Any idea on the Tax side of things? How to make the total that you will get after tax?

Zynks

Glad it worked.

colin300 wrote:

Any idea on the Tax side of things? How to make the total that you will get after tax?

If you want a straight calculation excluding 20%, all you need is =<cell where gross income is>*0.8

On the other hand, if you want to take PRSI and tax allowances into consideration, that is a bigger job and depends on loads of bits...

colin300

nah just the tax since i dont know the figures for the others yet.



cheers again.

hamster

Hey Guys,

This is an excellent forum for Excel ->
http://www.excelforum.com/

Recently I got some really good help on concatenating multiple rows of text data onto a single row in a column using conditional formatting with the VBA editor. Recommended!