• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating an Excel timesheet

Grumpy88

Grumpy88

Member
Hi.

I am struggling to create a timesheet with formulas in Excel that will keep a running debit / credit time balance for myself. I am using the TIME function to factor my 45-minute Lunch hour into the the end time less the start time calculation, in order to determine the total number of hours worked (e.g. an 08h00 start time and a 16h15 ending time, less a 45 minute Lunch, equals a 7.5 hour working day). However, I am stumped as to how to keep a running total of hours that I am in arrears / have to my credit, such that if I work more than that I am building up a credit balance of hours (and vice versa if I work less than 7.5 hours in a day). I can't find a formula that will marry a negative time balance carried forward from the previous day, with today's hours worked calculation (e.g. if I start today two hours in arrears, and work 8.5 hours today, one hour more than I should, then I must end the day with a -1 hour balance). Excel won't take a negative time amount (-2:00).

Any ideas?
Thanks.
 
EDIT: In short, therefore, I need to know how to make Excel recognise and work with a negative number while still using it in an hh:mm format.

Thanks!
 
Hi,

I know some expert will come with some other way to do this, meanwhile you can try this. If the file is solely intended for your use, change the date system to 1904 system from Option-Advanced. It can handle -ve time in calculations. But if you put dates and send the file to someone with 1900 date system, the dates will be jumbled up and -ve time will be shown as ####.

So be careful!

Regards,
 
Thanks guys!

I am rather hesitant to switch to the 1904 system as I will be sending my spreadsheet to my boss each month, so I'll go with iferror's solution of quoting the balance in terms of minutes rather than hours until an expert does come up with another solution.

Appreciate the response!
 
It's fine thanks - I've already added all kinds of worksheet protection, etc. that I'd then have to remove again if I posted it. I'm happy to stay with a cumulative balance in minutes, as everything else works the way I want it to.
 
Yes, that is pretty much what I had in mind, thanks! Provides me with a great alternative to a cumulative balance in minutes.

Appreciate it!
 
You must log in or register to reply here.
Back
Top