Share via

Formula for start, end, and actual time in Excel 2007

Anonymous
2013-03-22T00:48:03+00:00

I am trying to set up a spreadsheet in Excel 2007, where I have a column for clock in time, clock out time, and actual time that I stay to get the job completed. I am trying to determine how many extra hours I am putting in per day, over the clocked hours I enter for start and end time. Can anyone help me with this? I have tried several videos and sites on the Internet, but their suggestions are not working on my spreadsheet. If anyone can help me with this, it would be greatly appreciated! I'm not a computer wizard, so the easier you make it for a layperson, the better. I really need some help, so thank you to anyone who responds!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2013-03-22T10:57:17+00:00

Hi,

try this...

in A1 start time

in B1 end time

(A1,B1 Custom Format hh:mm am/pm)

in cell C1, type your desired  time

Format as Time 13:30

note:  if you want 8 hours, write 8:00

if you need 8h,30min write 8:30

in cell D1 write

=IF(B1-A1-C1<0,"",B1-A1-C1)

format as Time 13:30

XXXXXXXXXXXXXXX

example

1) in A1 write:

8:30 returns ....8:30 am

2) in b1:

17:40 returns....5:40 pm

3) in C1:

8:00

result in cell D1...1:10 (1h,10min)

Was this answer helpful?

2 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-03-22T18:33:00+00:00

    Thank you, thank you, to all of you! This worked, and you really came to the rescue fast! The only thing I have to remember is to enter my ending time in military hours.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-03-22T06:46:07+00:00

    Here are some timesheets already built

    http://www.techrepublic.com/blog/msoffice/build-a-simple-timesheet-in-excel/2552

    http://www.techrepublic.com/forum/questions/101-348370/daily-rate-weekly-timesheet-two-rates-available

    Build a simple timesheet in Excel

    http://blogs.techrepublic.com.com/msoffice/?p=2552&tag=nl.e056

    Excel makes it easy to set up a system for tracking time. Follow these steps to create your own or download our sample timesheet template and customize it to fit your needs.

    http://www.techrepublic.com/whitepapers/office-timesheets-time-tracking-made-easy-profitable/390507

    http://www.techrepublic.com/whitepapers/make-time-sheets-an-effective-part-of-your-business/389362

    http://spreadsheetpage.com/index.php/file/employee\_time\_sheet/

    http://www.ozgrid.com/Services/ExcelTimeSheetScreen.htm

    http://office.microsoft.com/en-us/templates/results.aspx?ck=1&ex=2&qu=timesheet&av=zxl140

    Part 4: Time sheets and Resource management

    http://chandoo.org/wp/2009/08/05/excel-time-sheets-project-management/

    *************

    http://www.cpearson.com/Excel/overtime.htm

    Rounding to the Nearest Quarter Hour

    http://excel.tips.net/T009359_Rounding_to_the_Nearest_Quarter_Hour.html

    Three tips for rounding Excel time values

    http://www.techrepublic.com/blog/window-on-windows/three-tips-for-rounding-excel-time-values/6479?tag=nl.e064

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-03-22T06:04:59+00:00

    Assuming your In-Time is in cell A1 in time format like 7:40 AM  and your Out-Time is in cell B1 like  5:10 PM,  and the time which is needed to stay is in cell C1 as 8 hours ( you put just 8) then give this formula in cell D1 for getting the extra hours worked

    =(B1-A1)*24-C1   

    It will give result as 1.5 hour which is difference between In-time and Out-time as 9.5 hours and further reduced by 8 hours.

    Please ensure that the formatting of cell C1 and D1 is as General.

    Was this answer helpful?

    0 comments