Add Weekly Hours To Get 4 Weekly Total Hours

[SoulfulNeo]

Hi.

I've been working on a personal timesheet to keep track of my hours at work. I've made great progress, but I am stuck on my final formula, I have tried everything to get a result, but it's not happening.

To calculate the weekly hours from the attached worksheet I have used this formula:

Please Login or Register  to view this content.

I wish to get a result that shows all four weeks calculated so that each week the hours worked and hours up/down are displayed with text. As an example, I want it to say this on cell A2: Total Hours Worked: 88:03 - £851 Total Pay. Where the 88.03 hours are this would be dynamic by the actual hours each week and the same with the total pay by adding all the weeks up. Doing a simple SUM formula doesn't give the expected result. I have done SUM in cell I1 and I2 and tried this formula, but it doesn't work:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I hope someone can advise. Any help would be appreciated.

[BadlySpelledBuoy]

You say using SUM doesnt give the expected result, but I'm not 100% sure what the expected results are or why a SUM wouldn't work.
If it's a case that it needs to be dynamic because you could have varying numbers of shifts each week, and therefore varying numbers of rows of data, then you could use SUMIF.

If you put the word "Totals" as a header in the rows that sum the weeks up (in your example file that's A11, A20, A30 and A40) then you could use the below formula in cells A2.

Please Login or Register  to view this content.

If that doesn't do what you need then give us a bit more of an explanation of the expected outcome and we can help from there.

BSB

[SoulfulNeo]

Hi.

Thanks for your response.

What I've done so far is used the SUM function in cell I1 which is: =SUM(F11,F20,F30,F40). This calculates my total hours in each week. I've then used the SUM function in cell I2 which is =SUM(G11,G20,G30,G40). This calculates my total pay in each week. Then in cell A2 the cell I1 and I2 is referenced to output the total hours and pay. This formula is:="Total Hours Worked: "&TEXT(I1,"[hh]:mm")&" - "&TEXT(I2,"£#,###")&" Total Pay".

I'm getting a positive result, but the total pay in cell A2 has rounded up to £851 which should be £850.56. How do I get it to display the true value and not the rounded up number?

I guess I over complicated my question. If there's a better way to get my results instead of using SUM and then referencing the cell again that'll be great. Mainly I need to ensure the total hours worked and total pay is calculated in A2.

I hope that helps better.

[BadlySpelledBuoy]

Understand you now.
Adding the part in red below will prevent the pay being rounded up:

Please Login or Register  to view this content.

You can do away with the need for I1 and I2 by moving those sum formulas into the main formula, like this:

Please Login or Register  to view this content.

BSB

[SoulfulNeo]

Thank you that worked wonders.

I wanted to change the text should, but I can't remember the formula even though it's in front of me. The formula is now:

="Total Hours Worked: "&TEXT(SUM(F11,F20,F30,F40),"[hh]:mm")&" • "&TEXT(SUM(G11,G20,G30,G40),"£#,###.00")&" Total Pay"

How do I get it to say this: Total Hours Worked: 88:03 • Total Pay: £850.56

Thanks again for your help. That second formula was a treat to not have another formula to reference to.

[SoulfulNeo]

I figured it out. Thank you for all your help. I truly appreciate it.

I used this code:

Please Login or Register  to view this content.

[BadlySpelledBuoy]

No problem at all. Glad I could help

BSB

[SoulfulNeo]

No problem at all. Glad I could help

BSB

I feel bad for asking, but I've run into one final issue and then I'm done. With the help you gave earlier I've added in text to display of my hours are up or down hard on contract hours and hours worked. I've got this formula to add all the hours, but it's returning 00:00 no matter what I do. So you happen to know what's causing this and a fix?

Thanks.

="Total Hours Worked: "&TEXT(SUM(F11,F20,F30,F40),"[hh]:mm")&" • Total Pay: "&TEXT(SUM(G11,G20,G30,G40),"£#,###.00") &" • Contract Up / Down: "&TEXT(SUM(H11,H20,H30,H40),"[hh]:mm")

[BadlySpelledBuoy]

The 00:00 result is caused by the up/down values being being in text format.
Excel isn't great at showing negative times, well not without changing the default date system to the 1904 date system.

There may be another way around it but I'll have to have a think on it.

BSB

[BadlySpelledBuoy]

Maybe this?

Please Login or Register  to view this content.

BSB

[SoulfulNeo]

Thank you so much. This worked perfectly. Simple little .00 fixes everything. Thanks for all your help.

[BadlySpelledBuoy]

Happy to help mate. Good luck with the rest of the build and give us a shout if you need more help along the way.

BSB