# Time Sheet Help

#### Exodus129

##### New Member
I am trying to set up a time sheet that would look like this:
 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 6:00 AM 12:00 PM 6:00 AM 4:00 PM #VALUE!

<tbody>
</tbody>
 When I try and do a formula to have lunch taken out it breaks because I still do not completely understand what I am doing. I can take out lunch with =SUM(D29-C29-"00:30")+(F29-E29-"00:30")+(H29-G29-"00:30")+(J29-I29-"00:30")(L29-K29-"00:30") but I want to only subtract it on days that have more than 6 hours worked. I tried this one =SUM((D29-C29)*IF((D29-C29)>VALUE("6:00"),-"00:30"),"(D29-C29)") and it isnt working properly. Once I had this good I was going use it for each set of days in my SUM function to get total hours worked. Any assistance would be welcomed with open arms.

<tbody>
</tbody>

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### texasalynn

##### Well-known Member
does this work
=IF((D29-C29)*24>6,((D29-C29)-TIME(,30,))*24,(D29-C29))

Last edited:

#### Exodus129

##### New Member
It gave me a result of 0 instead of what should have been 4 so it kinda worked.
 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 6:00 AM 12:00 PM 0 6:00 AM 4:00 PM 9:30

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Driving me a little crazy currently...but thank you for trying.

#### Exodus129

##### New Member
In case anyone was looking at this for an answer, I got this formula to work for removing a 30 minute lunch for any work day over 6 hours and giving me the total time worked for the week.
=SUM(IF(F28-E28<1/24*6.1,F28-E28,F28-E28-1/48))+(IF(H28-G28<1/24*6.1,H28-G28,H28-G28-1/48))+(IF(J28-I28<1/24*6.1,J28-I28,J28-I28-1/48))+(IF(L28-K28<1/24*6.1,L28-K28,L28-K28-1/48)+(IF(N28-M28<1/24*6.1,N28-M28,N28-M28-1/48)))
Good Luck out there!

Replies
10
Views
509
Replies
1
Views
186
Replies
1
Views
295
Replies
2
Views
301
Replies
2
Views
316

1,191,282
Messages
5,985,740
Members
439,978
Latest member
Mr930R

### 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.

### Which adblocker are you using?

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

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