Excel Calculating Problem - Calculing hours

[TH3ROOKIE]

I'm doing a timesheet. But I have some problems. the only formul
missing is if somebody works from 9am to 6pm for a total of 9hours o
work "=(C3<B3)+C3-B3" then I want to remove an hour of lunch on th
total time work if that person as work more than 6 hours.

Exemple. If the persone work a total of 6 hours, then remove 3
minutes. if the person work more than 6, then remove 1 hour

 

[JE McGimpsey]

One way:

I'll suggest using this equivalent to your formula:

=MOD(C3-B3,1)

just for brevity.

Since XL stores times as fractional days (e.g., 06:00 = 1/4 = 0.25), to
take away a half-hour for 6 hours or less, and an hour if the employee
works more than six hours can be done by:

=MOD(C3-B3,1) - (1+ MOD(C3-B3,1)>0.25)/48

 

[TH3ROOKIE]

It doesn't work First, XL wanted me to change the formula to tha
one "=MOD(C3-B3;1) - (1+ MOD(C3-B3;1)>0,25)/48" and it always remov
half an hour all the time, no matter what time in the total cell.
know i have to change 48 by 24 to remove an hour. And I forgot to sa
(my big mistake here...) that if the person work less than 5 hours, yo
don't remove anything.

Anyway, I'll keep working on it, but any help is welcome

 

[JE McGimpsey]

Yeah, it usually helps when you give the complete specifications <g>.

One way:

=MOD(C3-B3;1) - ((MOD(C3-B3;1)>=5/24) + (MOD(C3-B3;1)>0.25))/48

 

[TH3ROOKIE]

Well, thanx a lot. My problem is solve now. I wish I could have don
all that by myself but, but I'm a first timer. But it seems to b
pretty easy when you know the basics.


Thanx again for your help buddy