[HELP] Need someone who's fairly good with Excel/Spreadsheets!

[Kushja]

Hey all.

Hopefully someone could give us a huge hand with this (+rep!)

Basically I've just been given all of my hours for work, so I need to log these in a spreadsheet.
I've laid it out so A/B Column is Day/Date - C/D Column is Start-End hours (00:00-00:00).

Now I need to work out the hours worked in each day, and then tally up (weekly) how many hours rota'd/worked in that week..
So is there a simple way to add (Start-End) to equal a simple number ie (Start 08:00 - End 20:30) = 12.5?
So far I've found a formula online that it came out as 12:30..

Now to add those hours (Mon-Sun) To equal weekly hours (35+)..? Sounds confusing lol.

Any help would be appreciated!

[Richie]

To get the hours for each day:
=d2-c2 Obviously changing the numbers for different cells or you could just drag and drop down the column and it will do it automatically

To get the weekly hours:
=sum(f2:f24)

[Kushja]

To get the hours for each day:
=d2-c2 Obviously changing the numbers for different cells or you could just drag and drop down the column and it will do it automatically

To get the weekly hours:
=sum(f2:f24)

Thank you, I did something like that and managed to get the daily hours somewhat correct (Other than they are shown as 00:00) But that doesn't bother me - When I do The weekly hours (Week one - =SUM(F5:F11) ) I get an answer like;15:30...
Where as I should be getting 39.5?

[Kardan]

Thank you, I did something like that and managed to get the daily hours somewhat correct (Other than they are shown as 00:00) But that doesn't bother me - When I do The weekly hours (Week one - =SUM(F5:F11) ) I get an answer like;15:30...
Where as I should be getting 39.5?

You need to right click and change the formatting of the cell, general will do, it's currently set to time

[Kushja]

You need to right click and change the formatting of the cell, general will do, it's currently set to time

Now the sum = "1.645833333" -.-

[Kardan]

Now the sum = "1.645833333" -.-

Change the F column to be formatted as general, and change the formula in the cells to (D2-C2)*24

[Kushja]

Change the F column to be formatted as general, and change the formula in the cells to (D2-C2)*24

You're an absolute legend, thank you bud!

[CrazyLemurs]

If you ever work overnight (say from 10pm to 4am) you're stuffed on that system
=D2-C2+24 will stop that, if the cells are formatted to time

---
OK Kardan got there first
LOL I feel slow now

[Kushja]

Right okay just had a thought, if I work over 35hrs a week I get a £1 extra for every hour over

I have the two seperate wages for reference cells (Standard as L5 and Overtime wage as L6) Therefore I need to use the SUMIf command but totally forgot how to lay it out using this..

So I need something like.. =SUMIF(G12>35,*$L$6,$L$5)??

bobba knows lol -.-

This is what I have done so far; works fairly well (thank you)

[CrazyLemurs]

=IF(HOURS>35, 35*6.83 + ((HOURS-35)*7.83), HOURS*6.83)

That should hopefully work