# Excel 2007 : How to convert ex: (8-4:30) into total hours

1. ## How to convert ex: (8-4:30) into total hours

Hi,
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.

This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.

Or... does anyone have a better solution to keeping track of their hours?

Thanks!
Marty

2. ## Re: How to convert ex: (8-4:30) into total hours

Hi,

I'd advise that you capture your times with a time format that excel can calculate. At the moment you're entering text which is not very useful and doesn't lend itself to easy analysis.

First decide whether you want users to enter hours and minutes or hours and decimals of an hour. i.e. 8:30 or 8.5 and create cells for both start & finish times.

Assuming you choose hours & minutes then use cell data validation and set the minimum and maximum times, (could be 0:00 to 23:59) or any other control you want, then you can prevent someone entering an invalid time.

Once you have proper time 'numbers' then you can use these in calculations.

Regards

3. ## Re: How to convert ex: (8-4:30) into total hours

Hi,
Actually, it doesn't matter how I write it, but I understand what you're saying. Right now, we just enter it exactly as: 8-4:30 or 10-6:30, etc, but as long as it's consistent, I don't mind how it's coded.

We really don't use partial hours (8:20, 9:15). It's only hour and half hour time increments.

Marty

Originally Posted by Richard Buttrey
Hi,

I'd advise that you capture your times with a time format that excel can calculate. At the moment you're entering text which is not very useful and doesn't lend itself to easy analysis.

First decide whether you want users to enter hours and minutes or hours and decimals of an hour. i.e. 8:30 or 8.5 and create cells for both start & finish times.

Assuming you choose hours & minutes then use cell data validation and set the minimum and maximum times, (could be 0:00 to 23:59) or any other control you want, then you can prevent someone entering an invalid time.

Once you have proper time 'numbers' then you can use these in calculations.

Regards

4. ## Re: How to convert ex: (8-4:30) into total hours

Richard,

I'm attaching a copy of a timesheet that we use here at my office. If you look at the far left, you'll see where we are able to view the totals for the week. I've taken all sensitive or personal data out of the worksheet. Hope this helps you. If you have questions just ask.

5. ## Re: How to convert ex: (8-4:30) into total hours

Hi

See the attached.

In C10:D10 I have added a data validation that ensures you enter a time in hours:minutes.
P10 is then simply the difference between the two less 0.5.

The overtime in E10 is therefore P10-8

You don't need the Minutes columns from column F onwards.

HTH

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