Tracking employee schedules with Excel?

February 1, 2005 9:11 PM

Excel formula question. Using Excel as an employee scheduler and need to help trying to create a formula for tracking.

The schedule has the person's name, the time they begin work, and the time they end work. (Generally written as "John Doe 8:00 - 4:30"). I need to find a way to track how many hours the person works each day, minus their lunch time. I'm not very advanced with Excel, and the help is rather confusing, since I also need to convert to military time and account for days with no lunches and days off.

I have searched via Google for samples of similar spreadsheets to try and adapt a formula, but nothing appears to match my needs. The concept seems utterly simple, so I'm astonished that I haven't been able to solve this myself and am hoping someone could let me know a formula to use, or point me in the right direction. Thanks!

posted by cyniczny to computers & internet (3 answers total)

Are the start and stop times in separate cells? Is lunch always the same amount of time, or is that tracked as a separate value?
posted by willnot at 9:24 PM on February 1, 2005

Try this link.
Or try a google search for Excel Elapsed Time.

Once you figure out how to calculate that value you can easily subtract however much you need to for lunch, or not as necessary.

As to military time, if you select all your times, then right click, choose the "number" tab, or your version's equivalent and specify the format you want your time to display. It will convert the military time for you.

Don't be afraid of Excel. Don't be timid. It senses your fear and gets wily. Get a book about it if you are going to be using it a lot. Or learn how to be an expert Excel tip googler. Godspeed.
posted by tinamonster at 10:13 PM on February 1, 2005

willnot: everything is in different cells. Lunch is usually 30 minutes, but a few have 45 minutes. I have not yet added the lunch into the spreadsheet at all, but can put it into a separate cell, if required.

tinamonster: thank you for the link and google search phrase. I will have a look at those.
posted by cyniczny at 5:33 AM on February 2, 2005