Subtracting 30 minutes from time in and out for schedule

snedman

New Member
Joined
May 2, 2002
Messages
39
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi snedman,

Does this help?...


Excel Workbook
ABCDEFGHIJ
1NameStart TimeEnd TimePaid HoursHoursLunchHours Worked
2Bill9:30 AM6:00 PM8:0006:0000:308:30
3Bob11:30 AM5:00 PM5:305:30
4Tom9:00 AM5:30 PM8:008:30
5Tim6:00 AM5:00 PM10:3011:00
Sheet1



The formula in E2 needs to be copied down.

Good luck.

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

Any advice.

The links I followed for the HTMLMaker I can't get to work correctly for taking a range of the spreadhseet to post.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top