Thanks:  0
Likes:  0

# Thread: Subtracting 30 minutes from time in and out for schedule

1. ## Subtracting 30 minutes from time in and out for schedule

I am attempting to figure out how to change the formula to automatically subtract a 30 minute lunch if the total time for the day exceeds 6 hours worked.

B value Time of day punch in: 9:30 AM
C value =IF(D11="","","-")
D value Time of Day punch out: 6:00 PM
E value =IF(D11="","",IF(D11<=B11,((VLOOKUP(D11,TValue,3,))-(VLOOKUP(B11,TValue,2,))),((VLOOKUP(D11,TValue,2,))-(VLOOKUP(B11,TValue,2,)))))

E, is of course indicating the total time for the day of 8.50, vs the actual hours worked minus a 30 minute lunch.

This is a spreadsheet that I am attempting to change to help out my manager keep track of total employee weekly hours better.

Please keep it simple. I am not versed at all in VBA.

Any assistance on this would be greatly appreciated,

Thanks,

Snedman

2. ## Re: Subtracting 30 minutes from time in and out for schedule

Hi snedman,

Does this help?...

Sheet1

 A B C D E F G H I J 1 Name Start Time End Time Paid Hours Hours Lunch Hours Worked 2 Bill 9:30 AM 6:00 PM 8:00 06:00 00:30 8:30 3 Bob 11:30 AM 5:00 PM 5:30 5:30 4 Tom 9:00 AM 5:30 PM 8:00 8:30 5 Tim 6:00 AM 5:00 PM 10:30 11:00

 Cell Formula E2 =IF(D2-B2>\$G\$2,D2-B2-\$H\$2,D2-B2) J2 =D2-B2

Excel tables to the web >> Excel Jeanie HTML 4

The formula in E2 needs to be copied down.

Good luck.

Ak

3. ## Re: Subtracting 30 minutes from time in and out for schedule

I am having a hard time posting in a screenshot/html of the actual spreadhseet.

The links I followed for the HTMLMaker I can't get to work correctly for taking a range of the spreadhseet to post.

4. ## Re: Subtracting 30 minutes from time in and out for schedule

Akashwani, there is not actual place in the spread sheet for a lunch time. It is assumed that if you work an 8 hour shift a lunch is taken.

The 30 minutes would need to be deducted from the time autmatically from time in and time out only.

5. ## Re: Subtracting 30 minutes from time in and out for schedule

I will make yours work for me.

Thanks for the help.

6. ## Re: Subtracting 30 minutes from time in and out for schedule

Hi,

Does this work?.....

=IF(D2-B2>\$G\$2,D2-B2-"00:30",D2-B2)

And if you cannot put a time value in G2.....

=IF(D2-B2>"06:00",D2-B2-"00:30",D2-B2)

Ak

7. ## Re: Subtracting 30 minutes from time in and out for schedule

Hi snedman,

That will not work, but this will....

=IF(MOD(D2-B2,1)>0.25,(D2-B2)-0.5/24,D2-B2)

You may want to take a look here....

Ak

8. ## Re: Subtracting 30 minutes from time in and out for schedule

Akashwani,

That worked beautifully.

You have my greatest gratitude and appreciation for taking time to help me with this.

You sir, are a king : ]

Snedman

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•