# Excel Timecard minus Lunch Break



## lola is bk (Apr 28, 2004)

I need a formula that will keep track of hours worked (I can do that part of the formula) AND subtract 30 minutes for lunch if the employee works 5 hours or more.

Ex. Start time 7:00 (A1)
Quit time 15:30 (A2)
Total Hours 8:30 (Clocked In)

I tried A2-A1 and got the total hours but I need to have Excel automatically subtract :30 for lunch and I can't figure out how to do that.
Someone on another forum suggested =IF((A2-A1)>5,A2-A1-0.5,A2-A1) but it didn't subtract the lunch break.
Thanks for your help in advance.

Lola


----------



## maxflia10 (Feb 25, 2003)

Excel stores time as fractions of a day. 30 minutes is 1/48 of a day or 0.020833333. It's easier if you just enter 0:30 in a cell and reference the cell in your formula.

=B1-A1-C1

where C1 houses 0:30

=B1-A1-1/48

=B1-A1-"0:30"+0

Custom format the cell that houses the formula as [h]:mm


----------



## Anne Troy (Feb 14, 1999)

Hi, Lola and Max.

Chip Pearson has a bunch of great info on time and even some free downloadable timesheets:
http://www.cpearson.com/excel/topic.htm


----------



## rama4672 (Jun 1, 2003)

you could try something like this

=IF(COUNT(A1,B1)=2,(B1-A1+(B1<A1))*24,"")-0.5

Ian


----------



## lola is bk (Apr 28, 2004)

Dreamboat,
You truly ARE a Dreamboat. That link you sent me was exactly what I needed!!!!!!!!!!!!!!!!!!!!!! 
Thanks


----------



## Anne Troy (Feb 14, 1999)

Thanks, Lola. Glad to help. Lots of people love Chip's information on time/date in Excel. If you appreciate it, thank him, not me!  But I understand...


----------



## 0pak (May 20, 2007)

hey rama4672 I tried your code: =IF(COUNT(D9,E9)=2,(E9-D9+(E9<D9))*24,"")-0.5

and it works, but we still have a problem when an employee only works 4 hours and still is taken 30 min for lunch. We wanted to be if the total of hours = 5 hours or more then take out -0.5 for lunch..


----------



## bomb #21 (Jul 1, 2005)

=IF(end_time-start_time<1/24*5,end_time-start_time,end_time-start_time-1/48)

Just substitute "start_time" and "end_time" with appropriate cell references.

HTH


----------

