|
Excel Timesheet
Hi all
Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks |
Just subtract the lower from the higher, and format that cell as time as
well =B2-A2 then just add the two totals cells together, and again format as time, say =E2+F2 then at the bottom, just sum them and format as time =SUM(G2:G8) If you want a decimal number, say to multiply by rate to get pay, multiply by 24 =G10*24 -- HTH RP (remove nothere from the email address if mailing direct) "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks |
Matt, try Chip Pearson's site:
http://www.cpearson.com/excel/datetime.htm#TOC ************ Anne Troy www.OfficeArticles.com "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks |
I can now get the start and finish times to add up the hours i have done each
day but at the bottom when i go to add up the total hours worked in the week as it is set to time format when you get to 24:00 it goes back to 01:00 instead of carrying on as i need the total hours to read at least 37.5 hours, how can i solve this? Many thanks "Bob Phillips" wrote: Just subtract the lower from the higher, and format that cell as time as well =B2-A2 then just add the two totals cells together, and again format as time, say =E2+F2 then at the bottom, just sum them and format as time =SUM(G2:G8) If you want a decimal number, say to multiply by rate to get pay, multiply by 24 =G10*24 -- HTH RP (remove nothere from the email address if mailing direct) "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks |
Matt Wrote: I can now get the start and finish times to add up the hours i have done each day but at the bottom when i go to add up the total hours worked in the week as it is set to time format when you get to 24:00 it goes back to 01:00 instead of carrying on as i need the total hours to read at least 37.5 hours, how can i solve this? Many thanks "Bob Phillips" wrote: Just subtract the lower from the higher, and format that cell as time as well =B2-A2 then just add the two totals cells together, and again format as time, say =E2+F2 then at the bottom, just sum them and format as time =SUM(G2:G8) If you want a decimal number, say to multiply by rate to get pay, multiply by 24 =G10*24 -- HTH RP (remove nothere from the email address if mailing direct) "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks Matt Try formatting the cell as [h]:mm -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401464 |
I am sorry people are not understanding. I have got my spreadsheet together
so at the end of each day it adds up how many hours ive worked during each day e.g. see below: Day Start Finish Start Finish Hours Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10 Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45 Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55 Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55 Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15 Total 17:00 when i go to add up the total hours worked during the ween at the bottom it still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or something instead of carrying on, i also need it so it just counts 1-60seconds in time format not up to 100 in normal number. Please help someone. Thanks "Paul Sheppard" wrote: Matt Wrote: I can now get the start and finish times to add up the hours i have done each day but at the bottom when i go to add up the total hours worked in the week as it is set to time format when you get to 24:00 it goes back to 01:00 instead of carrying on as i need the total hours to read at least 37.5 hours, how can i solve this? Many thanks "Bob Phillips" wrote: Just subtract the lower from the higher, and format that cell as time as well =B2-A2 then just add the two totals cells together, and again format as time, say =E2+F2 then at the bottom, just sum them and format as time =SUM(G2:G8) If you want a decimal number, say to multiply by rate to get pay, multiply by 24 =G10*24 -- HTH RP (remove nothere from the email address if mailing direct) "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks Matt Try formatting the cell as [h]:mm -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401464 |
Ok i have got it set up now and i can work out my overtime as flexi time etc
but i did under the standard hours i am supposed to do in a week and it gave me a load of hash symbols, how do i format the time to give a negative answer e.g. -01:30. Thanks "Sandy Mann" wrote: Paul gave you the answer yesterday: Matt Try formatting the cell as [h]:mm -- Paul Sheppard -- Regards Sandy Replace@mailinator with @tiscali.co.uk "Matt" wrote in message ... I am sorry people are not understanding. I have got my spreadsheet together so at the end of each day it adds up how many hours ive worked during each day e.g. see below: Day Start Finish Start Finish Hours Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10 Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45 Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55 Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55 Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15 Total 17:00 when i go to add up the total hours worked during the ween at the bottom it still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or something instead of carrying on, i also need it so it just counts 1-60seconds in time format not up to 100 in normal number. Please help someone. Thanks "Paul Sheppard" wrote: Matt Wrote: I can now get the start and finish times to add up the hours i have done each day but at the bottom when i go to add up the total hours worked in the week as it is set to time format when you get to 24:00 it goes back to 01:00 instead of carrying on as i need the total hours to read at least 37.5 hours, how can i solve this? Many thanks "Bob Phillips" wrote: Just subtract the lower from the higher, and format that cell as time as well =B2-A2 then just add the two totals cells together, and again format as time, say =E2+F2 then at the bottom, just sum them and format as time =SUM(G2:G8) If you want a decimal number, say to multiply by rate to get pay, multiply by 24 =G10*24 -- HTH RP (remove nothere from the email address if mailing direct) "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks Matt Try formatting the cell as [h]:mm -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401464 |
XL normally cannot show negative times, (it can calculate them it just can't
display them). To show negative time change to the 1904 date system: Tools Options Calculation 1904 date system But note that ALL dates in that workbook will change by four years and one day. -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Matt" wrote in message ... Ok i have got it set up now and i can work out my overtime as flexi time etc but i did under the standard hours i am supposed to do in a week and it gave me a load of hash symbols, how do i format the time to give a negative answer e.g. -01:30. Thanks "Sandy Mann" wrote: Paul gave you the answer yesterday: Matt Try formatting the cell as [h]:mm -- Paul Sheppard -- Regards Sandy Replace@mailinator with @tiscali.co.uk "Matt" wrote in message ... I am sorry people are not understanding. I have got my spreadsheet together so at the end of each day it adds up how many hours ive worked during each day e.g. see below: Day Start Finish Start Finish Hours Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10 Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45 Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55 Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55 Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15 Total 17:00 when i go to add up the total hours worked during the ween at the bottom it still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or something instead of carrying on, i also need it so it just counts 1-60seconds in time format not up to 100 in normal number. Please help someone. Thanks "Paul Sheppard" wrote: Matt Wrote: I can now get the start and finish times to add up the hours i have done each day but at the bottom when i go to add up the total hours worked in the week as it is set to time format when you get to 24:00 it goes back to 01:00 instead of carrying on as i need the total hours to read at least 37.5 hours, how can i solve this? Many thanks "Bob Phillips" wrote: Just subtract the lower from the higher, and format that cell as time as well =B2-A2 then just add the two totals cells together, and again format as time, say =E2+F2 then at the bottom, just sum them and format as time =SUM(G2:G8) If you want a decimal number, say to multiply by rate to get pay, multiply by 24 =G10*24 -- HTH RP (remove nothere from the email address if mailing direct) "Matt" wrote in message ... Hi all Im trying to set up a new timesheet for use at work but dont know much about formulas etc, i need to put a start and finish time e.g. start 8.30, finish 12.30, then i need to calculate in a column at the end telling me how many hours ive worked between 8.30 and 12.30 then the same for the afternoon from 1.30 until 5pm with another column at the ending adding the hours worked in the morning to the hours worked in the afternoon giving me total hours worked that day and the same everyday for the week with a box at the bottom giving me total hours worked for the week, please help. Thanks Matt Try formatting the cell as [h]:mm -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401464 |
| All times are GMT +1. The time now is 11:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com