Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

timesheet breakdown of hours

0

Good Day,

I have a sheet that have daily hours worked, at the end i total the number of hours. I now have to manually separate it into 8hrs or less, then anything over this is at time and a half and on sundays 4 hours goes on the time and a half and four goes on a next column "double time". Is there a way to put in formulas for this.

Thanks in advance for the assistance

Attached is the file im working with, i need all hours for each days that is 8 hours or less except sunday to be summed in the col "st", 

excel
posted by: liz (rep: 2) on Wed Apr 11, 2018 at 11:02 am
Answer
Discuss

Discussion

of course it's possible. This is what Excel was made for! But everything depends upon worksheet design and the flow of input and calculation in that design. I suggest you first look at the example you find listed on the left of your screen. Then post your workbook with a specific question - one formula per question.
Variatus (rep: 4889) Apr 11, '18 at 7:54 pm
ok, i want a formula for all the days of the week except sundaty that gives the value for each day that is 8 hours or less. I usually total the all the days.
liz (rep: 2) Apr 12, '18 at 8:40 am
Such a formula can only be designed with full knowledge of the worksheet layout. I suggest you append a copy of your workbook to your original question. Then specify the exact cells whose data must be considered and the cell where the rsult is to be displayed.
Variatus (rep: 4889) Apr 12, '18 at 8:27 pm
Thanks for all your patience in assisting me, much appreciated. I have attached a sample of my workssheet to the original post
liz (rep: 2) Apr 13, '18 at 8:16 am
Add to Discussion

Answers

0
Selected Answer

Please find my solution in the attached workbook. There are lots of comments in the worksheet which I hope will prove helpful.

Key to the solution is the availability of true dates so that Excel can identify Sundays. My formulas presume that Sunday is day 1 of your week. I'm not sure if that changes with regional settings. Try it out. If 1 doesn't work for you, change the number (it might be 7) in the formulas or adjust your regional settings.

Column J has an array formula which must be confirmed with Control+Shift+Enter, pressed simultaneously, instead of the singular Enter you are accustomed to. The formula calculates the sum of a maximum of 8 hours for each day and then deducts the Sunday hours included in that total.

The formula in column K takes the total hours, minus the ST hours and subtracts the numbers of hours worked in excess of 4 on Sunday.

That leaves the double time hours to be the remaining balance. However, if you wish to calculate this total indpendently it would be the number of hours worked in excess of 4 on Sunday. You will find this formula in the remarks.

A second sheet was added to the originally attached workbook which enables equal treatment of a list of holidays and Sundays.

Good .luck!

posted by: Variatus (rep: 4889) on Sat Apr 14, 2018 at 12:48 am
Discuss

Discussion

thank you very much this was extremly helpful. Just one more request is therr a way to reomve a day in the week from one catagory and add it to another for e.e i want fri (which is a holiday) to be added to double time and not included in "st"
liz (rep: 2) Apr 16, '18 at 1:54 pm
I added a solution to your new problem. Find it in the revised workbook attached to my answer. Note that this isn't a little add-on to the original question but a whole new ballgame. I don't normally do follow-ups for precisely this reason. Please frame further questions you might have in new threads. Thank you.
Variatus (rep: 4889) Apr 16, '18 at 9:48 pm
thank youuu very much, this was very helpful. saved me lots 
liz (rep: 2) Apr 17, '18 at 8:19 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login