# Conversion Formula Help

#### squeakums

##### Well-known Member
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,

Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)

Any ideas please?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Is it?

=MOD(E1-D1,1)-F1

I have this formula - =IF((((E6)+(F6))*24)>8,8,((E6)+(F6))*24) but it isn't quite working. The one you gave isn't working ;x

In what way is my formula not working?

Sure... I use this in my time sheets:

(Start Time - Lunch Time clockout)*24 + (Back after Lunch Time - End time)*24 = time worked.

Or, ((Start Time - Lunch Time clockout)+(Back after Lunch time - End Time))*24.

You must convert the Excel time format to hours by multiplying by 24.

Hope this helps...
XLXRider

I just have a total hours of lunch time taken. I dont' have the in and out. I think I need a formula that will just add and multilply and then minus that time - but for some reason it is not working for me ;x The formula that I gave is always giving me 8 as the answer for everyone and I know that everyone didnt' work exactly 8 hours ;x

Start End Lunch Total
10:01 AM 7:08 PM 01:01:03 (formula needed)

Any ideas?

If you don't have the in and out times how on earth are you going to calculate hours worked?

Try using VoG's suggestion, a little modified, i.e.

=MIN((MOD(E6-D6,1)-F6)*24,8)

That assumes that start time is in D6, end time in E6 and lunch hours in F6

That last one works thanks!

Replies
3
Views
236
Replies
0
Views
85
Replies
8
Views
138
Replies
6
Views
261
Replies
9
Views
269

Threads
1,202,990
Messages
6,052,958
Members
444,621
Latest member
MIKOLAJ_R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back