Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

1. Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

Hello, I am using Excel 2010.

I don't use spreadsheets much, and when I do design them, they are fairly basic, rarely complicated. There is something I want to do with one, though, and am requesting assistance, please.

I'm creating a payroll history spreadsheet for myself, to keep track of my hours, overtime, gross wages, etc. Nothing fancy.

I'd like to know if I can enter my hours into a cell, and have Excel automatically deduct my lunch time if I'm over 8 hours.

For example, if I type 6-2:30 in a cell, can Excel automatically see it is more than eight hours ? And then automatically add 8 hours to the Total Hours cell, instead of 8 1/2?

Here are two screen shots of what I want to do.

excel1.jpg

excel2.jpg

Is this possible? Can I get Excel to automatically determine 6-2:30 is a set of hours, determine it's 8 1/2, and if over 8, deduct .5 hours? What's an easy way to do this, if possible?

If you need more information from me, please request it. I'll do my best to provide further details. Please consider me an Excel novice. Thank you! Jd

2. Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

There are 2 ways to do it.

First is to insert your time in 24 hr format, this way it'll be easier to extract the information (assuming if you'd like to input the number of hours in 1 cell)

Second way is to have start and end time in each cell...i.e. column B will be start time and column C will be end time (column A will be your date)

3. Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

It didn't even occur to me to have an IN and OUT column, like time clocks with time cards. That will double the amount of columns for the work week, but if it ultimately makes the calculations easier, I'll go for it.

But I still can't figure out how to get Excel to calculate the 8-hour day if it's over 8 hours.

4. Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

Hello jdanniel,

If you want, I can create a simple "template" for you.

But I would like to know a little info from you, such as will you work after hours...in a sense, you begin at 11pm and end at 2am etc?

5. Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

jdanniel, just remember, excel treats hours as fractions/decimals of a day, even though it displays then as time. So 8 hours (or 08:00 AM) is really 8/24 or 0.333 of a day. 6 hours is 0.25, 16 hours (or 04:00 PM) is 0.666...and so on

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1