Subtracting 30 minutes from time in and out for schedule

[snedman]

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

 

[Akashwani]

Hi snedman,

Does this help?...

Excel Workbook
	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
Sheet1

The formula in E2 needs to be copied down.

Good luck.

Ak

 

[snedman]

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.

 

[snedman]

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.

 

[snedman]

I will make yours work for me.

Thanks for the help.

 

[Akashwani]

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

 

[Akashwani]

Hi snedman,

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

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

Sorry about that.

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

http://www.youtube.com/watch?v=CtrFnGWqoGE

Ak

 

[snedman]

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