Need a formula for Employee Time Sheet

Hello!

I was wondering if someone could help me find the formula that can solve this problem. To be simple, I want column E automatically removes 30 minutes if the total hours exceeds 8 hours or more. If the total of hours is 7:59 or less, 30 minutes must not be removed. Cells format is [H]:mm. Also, I am using the 24hrs format instead of am/pm.

D4 - C4 = E4 then - 30 minutes only if the total is 8hrs or more.
D5 - C5 = E5 (same)
D6 - C6 = E6 (same)
etc, etc, etc .....

Capture.JPG

Hi, welcome to the forum

For future reference, please upload a sample workbook, not a pic, we cant really work with pics

Also, What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Fri Jul 2015) is actually 42216

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, if you want to remove 30 mins for times in excess of 8 hours, then something like...

=if(d4-c4>=8/24,d4-c3-(30/60/24),d4-c4)

You could do the calc with the TIME() function, as well

I noticed 1 small reference error in my formula, but when that was fixed, it worked...
=IF(D4-C4>=8/24,D4-C4-(30/60/24),D4-C4)
For comparison, I put my formula in column F...

	A	B	C	D	E	F
3	DAY	DATE	TIME IN	TIME OUT	TOTAL HRS
4	MONDAY	27/Jul/15	11:00	23:00	12:00	11:30
5	TUESDAY	28/Jul/15	11:00	23:00	12:00	11:30
6	WEDNESDAY	29/Jul/15	11:00	23:00	12:00	11:30
7	THURSDAY	30/Jul/15	11:00	16:00	5:00	5:00
8	FRIDAY	31/Jul/15	11:00	16:00	5:00	5:00
9	SATURDAY	1/Aug/15	11:00	16:00	5:00	5:00
10	SUNDAY	2/Aug/15	11:00	16:00	5:00	5:00
11				TOTAL	56:00

F4=IF(D4-C4>=8/24,D4-C4-(30/60/24),D4-C4)
(but you could copy this to E4 just as well

Also, when you are just adding/subtracting 2 (or 3) cells, you dont need to use the SUM() function, that is only really used for summing ranges, such as you have in E11. So this...
=SUM(D4-C4)
can just be...
=D4-C4

WoW! It works!

Just had to change 2 things......

Mine: =IF(D4-C4>=8/24;D4-C4-(30/60/24);D4-C4)

Yours: =IF(D4-C4>=8/24,D4-C4-(30/60/24),D4-C4)

This is probably because I am using Excel in French.

Spent like 3 days on it with my wife with no success! lol


Thank you so much! It is really appreciated!