Forum home » Delegate support and help forum » Microsoft Excel Training and help » EXCEL -FORMULA
|
|
|
The UK's most regular 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 -FORMULA
Resolved · High Priority · Version 2003
Reply Thu 5 Mar 2009, 10:21Delegate Tracey said...
Tracey has attended:
Excel Intermediate course
Excel Advanced course
EXCEL -FORMULA
Hi
I am completing a spreadsheet and need to know how I can make a formula using time - Me and my Colleague have to complete a form for additional hours worked.
I want to know if it is possible to enter a start time and finish time in seperate columns and then get the total number of extra hours worked in the total column and do the same for owing hours.
I.e if I enter 17:00 in start column and 18:00 in finish column I want the total column to show 1 hour and the same if someone needs to finish early and they go inot to minus hours.
Hope this makes sense
Tracey
For upcoming training course dates see: Pricing & availability
RE: EXCEL -FORMULA
Hello Tracey
Thank you for your question.
Perhaps if it is possible, could you please email me a copy of the sheet (or something similar) with detail of what you'd like to happen with specific reference to the spreadsheet you email through, so I can have a look at this for you and see if I can come up with anything.
Kind regards
Amanda
Reply Thu 5 Mar 2009, 11:56Delegate Tracey said...
RE: EXCEL -FORMULA
Hi Amanda
That would be great, I can certainly do that.
Where do I e-mail it to? I couldn't see that I could attach anything to this.
Kind Regards
Tracey
RE: EXCEL -FORMULA
Hi Tracey
Sorry about that - you can send an email through to amanda
thanks
Amanda
RE: EXCEL -FORMULA
Hi Tracey
Please find attached a sheet I have created based on what I understand your requirements to be.
Please have a look through and let me know if you have any questions.
Kind regards
Amanda
Attached files...
hours sheet.xlsReply Mon 9 Mar 2009, 11:10Delegate Tracey said...
RE: EXCEL -FORMULA
Hi
Sorry, I forgot to say on earlier email that I need a version for a 37.5 hour week and a 37 hour week, how would I adjust the formula 2 reflect this?
Thanks
Tracey
RE: EXCEL -FORMULA
Hi Tracey
I think the way the spreadsheet is designed at the moment, it doesn't matter how many hours someone is working in a week, it's just recording hour credits and debits and balancing them out by the end of the week.
As a further explanation of what the spreadsheet does:
In column E, the difference is calculated between the actual start/finish time, and the standard start/finish time (subtracting standard start/finish time from actual start/finish time).
In order for the negative hours to show, I changed the date system used in the spreadsheet (Tools - Options - Calculation tab, tick 1904 date system - otherwise negative hours do not show up properly).
In the Running credit/debit total column, the value is F6 is equal to the value in E6 (the number of hours in credit/debit for Monday).
Then in the following cells in column F (F7 to F10), the credit/debit from the previous day (in column F) is taken and then the Hours credit/debit for the current day from column E is added to it.
In F10 I changed the formula slightly as the result came out as a negative result to start off with, so I added in the - sign and the brackets to switch the result from negative to postive.
I hope this helps.
Amanda
Tue 17 Mar 2009: Automatically marked as resolved.
|