# Excel Timecard minus Lunch Break

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### lola is bk

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.

Lola

#### maxflia10

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

#### rama4672

you could try something like this

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

Ian

#### lola is bk

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

#### Anne Troy

Anne
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

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

=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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On