excel time calculations

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - time calculations

Excel - time calculations

resolvedResolved · 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.


 

Excel tip:

Jumping Between Sheets in a Book

PgDn and PgUp keys scrolls up and down a screen page in most applications.

Ctrl+PgDn and Ctrl+PgUp keys jump from one sheet in your workbook to the next, up or down through the pages.

View all Excel hints and tips


Server loaded in 0.11 secs.