Conversion Formula Help

[squeakums]

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?

 

[VoG]

Is it?

=MOD(E1-D1,1)-F1

 

[squeakums]

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

 

[VoG]

In what way is my formula not working?

 

[XLXRider]

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

 

[squeakums]

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

 

[squeakums]

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

Any ideas?

 

[VoG]

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

 

[barry houdini]

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

 

[squeakums]

That last one works thanks!