MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Timesheet in Excel PLEASE HELP MEEEEE

Posted by ~*~*Louise*~*~* on February 11, 2002 3:24 AM

hello, i'm creating a timesheet for a school project i'm doing, but i've got a big problem.the manger enters the number of hours to be worked that month (say,180) and i subtract the hours worked by the employee by that number to get 'hours remaining'. but '180' is formatted as a number and the start,finish and monthly total amounts are formatted as times. so the i am not getting the right answer. eg if an employee has worked 16hrs 42mins that month its formatted as 16:42 and when subtracted from 180 i'm getting 7:18. i'm really really stuck please help!!!


Posted by Computerfreaks on February 11, 2002 3:40 AM

You need seperate cells for hours and minutes, then format all as numbers not times.

If your employee has worked 16hours, 42minutes, you would then have a cell that works out the total number of hours that this is e.g
hours+ (minutes/60)

in this case you would get 16.7 hours, you can then have 180- hours, giving 163.3 hours remaining

Posted by Mudface on February 11, 2002 3:40 AM

First, make sure all your cells are formatted as [hh]:mm (go to Format-Cells and choose the custom option, simply type in [hh]:mm in the box). Then, re-enter the time worked as 180:00 and your calculation should come out OK.

Posted by Computerfreaks on February 11, 2002 3:42 AM

or just do what Mudface says, that sounds a bit easier!!!

Posted by ~*~*louise~*~* on February 11, 2002 10:52 AM

thanks for your everyone :>)