Excel - Time Sheet Formula

Posted on 2008-10-14
Last Modified: 2011-10-19
The problem i have is the formulas i have used in the template are not quite what i'm after. The formula in column 'O' should be to make sure that a minimum of 8 hours have been in column 'M' and that will mean they will get paid 'D'. Anything over that as per column 'N' will be multiplied by there overtime rate in column 'E' and added to column 'O'. at the moment before any hours are entered it shows there daily rate which i dont want.
Also i would like a rolling total to show in the "Summary" sheet from all the new sheets that wil be created from the template code. At the moment i have it set up for one sheet but i dont know how to include for sheets that are not created yet...... Please advise
Question by:Carlandrewlewis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 22718787
I think this is what you want for your formulas. See attachment.

For your rolling summary you can just keep adding the total from each sheet ='Week 1'!O124+


Author Comment

ID: 22719589
The formulas are perfect now thank you. It's now just the rolling total.  What i have is when you enter a name in the "New Sheet" it will automatically create a new worksheet called the same. What i need if possible is when it creates a new sheet it adds the total aswell.......

Author Comment

ID: 22719596
The toatls are to show in the summary sheet, i think this makes sense!!
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

LVL 18

Accepted Solution

Curt Lindstrom earned 500 total points
ID: 22720713
I can do it like this. See attachment.

To do it when you add a new sheet gets a bit too complicated since you would not only have to work out how many week sheets there are, but also try to construct formulas in the summary sheet based on the sheet names. This is easier and works just as well. Just press the button to get the current total. The macro used by the button is in module1.


Author Closing Comment

ID: 31506205
Thanks for the advice, it all works perfect now....

Thanks for your time

Author Comment

ID: 22783918
Curt, you help me with the above problem where you created a macro to calculate the totals of all created sheets. I am going to post a question in regards to 'Excel Macros - Calculating Totals' could you have a look at it for me.


LVL 18

Expert Comment

by:Curt Lindstrom
ID: 22783975
Sure Carl, I'll look at it unless someone else beats me to it!


Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question