98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - time calculations
Excel - time calculations
Resolved · High Priority · Version 2003
Selina has attended:
Excel Intermediate course
Excel - time calculations
Hi there,
I am trying to replicate a timesheet that we used in a previous role where the formula could calculate hours due/owed by the employee. I have managed to get the formula to work as long as the employee does their hours or over time but it will not show a negative result in the event that an employee does not do enough for that day.
At present the calculation is relatively simple with a cell which adds up the finish time minus the start time and time taken for lunch (hours actually done) and then another cell to show the normal hours for the day. The final cell then calculates the hours actually done minus the normal hours. Obviously it has troubles showing the result as a negative figure.
All cells are formatted in the hh:mm format.
How can I get it to show this?
Thanks
Selina
RE: Excel - time calculations
Hello Selina
Thank you for your question.
Even if the result shows up as a negative figure, is the result of the formula correct, e.g. if someone is short of their normal hours by 1hr 30 does the result show as -1:30?
thanks
Amanda
RE: Excel - time calculations
Hi,
Yes the cell does calculate the amount correctly i.e. if I summed the balance of hours for each day in a week it still give the correct answer including the negative amount but the problem is that it refuses to show this amount in the cell.
Can this be rectified?
Thanks
Selina
RE: Excel - time calculations
Hi Selina
What do you get showing up in the cell? If you like you can email me a sample, my email address is amanda@stl-training.co.uk
This would helps as I can take a look at the formulas in the sheet.
Kind regards
Amanda
RE: Excel - time calculations
Hi Selina
There is a way you can fix this, although I'm unsure if this will have other implications for other formulas in your spreadsheet. If you are using the spreadsheet in isolation for the purposes of a timesheet, and you are not linking this information to other spreadsheets it should be fine.
1. Go to Tools - Options - Calculation tab in 2003; or Office Button - Excel Options - Advanced in 2007.
2. Find the 1904 date system option and tick it.
3. Click OK.
You should find the result of your formula now displays.
I can't take credit for this, I found the answer here :)
http://stackoverflow.com/questions/21880/is-there-any-way-to-properly-display-negative-time-spans-in-excel
I hope this helps.
Amanda
Fri 20 Feb 2009: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Jumping Between Sheets in a BookPgDn and PgUp keys scrolls up and down a screen page in most applications. |