Tracking employee schedules with Excel?
February 1, 2005 9:11 PM Subscribe
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!
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!
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
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
Response by poster: 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
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
This thread is closed to new comments.
posted by willnot at 9:24 PM on February 1, 2005