Creating an Automated Timesheet in Excel

[AKAtude98]

I am trying to create an automated timesheet in Excel. Basically, an employee should be able to enter his/her time in and time out, and Excel will calculate the total hours the employee works, how much leave time they have (anything over 7 hrs is leave time), it will know that a person is not scheduled to work on Saturdays and Sundays, and it makes sure that everyone works 7 hrs, even if the person must put in leave time. The problem that I am having is calculating the total hrs worked for the day. If someone works from 10am-6pm, I know how to make Excel show 8 hrs. But, what if they come in at 10:15am and leave at 6pm; what if they come in at 1:00pm and leave at 6pm; what if they come in at 4pm and leave at 12:00 midnight; what if they come in at 12 in the afternoon and leave at 6 pm; what if they come in at 12 in the afternoon and leave at 2:00 am; what if they come in at 3:00pm and leave at 2:00am??? I am having trouble creating if statements that consider all these cases. If anyone can help me, I would greatly appreciate it.

 

[Jack in the UK]

Hi --

You have not askeda question as such, more many quiestions, poss people wont aswer all them, its kinda of a project.

OK i have timesheet that the guru of timesheets, i designed and another i stole, well was sent, both are very touchy twitchy and frawt with issues. so expert maintance is always required as any timesheet will.

check the web for such TEMPLATES or examples, i know no way to avoid VBA scripts and that bring BIG problems as its al time convertions ie base 60.

So guru Gurus (one i think of Chris before you say) could do this in formula but it a project and a half..

I give without question anythink i have away to use view whatever, but i dont really want to they are not very good in returning results, like sick and holidays all sorts.

Fix,, build VERY slowly, manula input and replace bit by bit and so wuill cover what you need, your then understand the codes and formulas so will be able to maintain.. just there is what you want might not do the trick.

ME! i would post a question a day if needs be and getthat help you need.. and i would love the challange.. im know for fact single questions GET RESULTS from readers and i know you get that help.

Good luck.

IF YOU REALLY REALLY must.. i can send you codes and sheets, but i do not recoment taking that offer, they can be difficult, but i do not want to say no or hide the facts. Just being honest a lot to be said for that.

 

[Al Chara]

Try the following procedure:

If you put 10:15:00 AM in cell A1 and 6:00:00 PM in cell B1, then to calculate the total amount of hours worked you would enter =(D1-C10)*24 in cell C1. Does this answer your question?

 

[Yogi Anand]

Hi AKAtude98:
Suppose you have the person IN in cell A1, and the person OUT in cell B1, then the formula for hours worked in cell C1 would be
=(B1-A1)*24
Now please keep in mind that Excel considers 1 whole day as unit number 1, and the 24 hours make up day 1. So you have to format cells A1 and B1 as DATE/TIME, and C1 as NUMBER ... General. Following is a small sample of times IN and OUT entered and HrsWorked calculated according to the formula above

In Out Hrs Worked
10:30 AM 6:15 PM 7.75
3/19/02 14:00 3/20/02 3:00 13
3/19/02 10:30 3/19/02 18:15 7.75

HTH

 

[AKAtude98]

That equation has solved 99.9% of my problems!!!! I appreciate the help....I was starting to get a headache looking at this thing.