Formula Problem

[kantian1]

Hi,

I've attached a worksheet with a formula problem I cannot solve.

If a person does a full day I want the formula to calculate how many hours minus 30 mins for lunch i.e. 7:30 - 4:00 = 8 hours. I have done this and it works fine, the problem comes when a person does a half day i.e. 7:30 - 11:30, this will read 4.5 hours. If this is the case I don't want the 30mins deducted so it should be 4.0 hours worked.

I have high lighted a column in the work sheet (yellow) whereby the user puts an 'x' in the appropriate cell if they are working a half day.
The probem is I cannot incorperate the two formulas in one cell (the Actual hours cell) I have the one formula in the actual hours cell and the other bolted on the end thus giving two different readings.

I need the both formula's to work together in the 'Actual Hours Cell'?

Thanks.problem.xls

[DonkeyOte]

one way

E6:=24*(MOD(D6-C6,1)-(TIME(0,30,0)*(H6<>"x")))

[Cheeky Charlie]

How about e6=24*(IF(C6>D6,D6+1-C6,IF(H6="x",D6-C6,D6-C6-0.02)))

(there are much better ways of doing this but I've spent about as much time on this as you spent titling your thread).

CC

[kantian1]

How about e6=24*(IF(C6>D6,D6+1-C6,IF(H6="x",D6-C6,D6-C6-0.02)))

(there are much better ways of doing this but I've spent about as much time on this as you spent titling your thread).

CC

Hi,

What are you getting at?

[DonkeyOte]

Just to reiterate your existing formula can be simplified if you use:

MOD(end time - start time,1)

This caters for possibility of end time < start time and v-v...
(a trick I picked up from daddylonglegs).

then simply subtract 30 mins from above result wherever H does not contain "x"

multiply result by 24.

(CC was getting at the fact that "Formula Problem" was not overly descriptive...)

[kantian1]

Just to reiterate your existing formula can be simplified if you use:

MOD(end time - start time,1)

This caters for possibility of end time < start time and v-v...
(a trick I picked up from daddylonglegs).

then simply subtract 30 mins from above result wherever H does not contain "x"

multiply result by 24.

(CC was getting at the fact that "Formula Problem" was not overly descriptive...)

Thanks for your help.

The description got his attention though didn't it.

[NBVC]

kantian1,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[NBVC]

DonkeyOte and Cheeky Charlie, to avoid these kinds of issues, please do not respond to threads that have non-descriptive titles... The OP should read the rules before posting and understand that the titles must be more descriptive.

Thank you

[kantian1]

This is one of the most hostile forums I have ever had the pleasure to grace.

there are much better ways of doing this but I've spent about as much time on this as you spent titling your thread

If I had asked him for his help face to face, would I have got the same response after he had given me his answer?

I doubt it. It's just another case of people hiding behind their computer screens, under the impression that they have the right to talk to people with no regard whatsoever.

Shameless.

[shg]

Kantain, we provide free help to people, and ask only in return that people read and follow our forum rules; it doesn't seem like a bad trade. The reason we require descriptive thread titles is that it results in a searchable database that reduces the necessity of answering the same question several times a day.

If you frequent a lot of forums, you know that there are some with strict rules, some with none, and we're about in the middle. The good news is, everyone can choose a place that makes them comfortable.

Kind regards,

[NBVC]

We are all volunteers trying to help you get the answer you want, and as fast and efficiently as possible so that you can be satisfied and continue with your projects...

We also have our own work to do and don't always have time to spend back and forthing on every thread...

So the more time you spend making sure your question is clear and concise, the faster and better response you get.

[DonkeyOte]

I agree that Kantian's thread title was vague however in OP's defence I think the question was well detailed and as such received a solution on first attempt - the same can not be said for many other posts regardless of the thread title -- I think I would prefer a more detailed question than a detailed thread title but I totally accept & understand shg's point re: search engine etc... however that said I think the more experienced posters here should also take more care with their posts so as not to cause offence, sarcasm is not always guaranteed to "read" in quite the same tone in which was "written"/"intentioned".

Kantian1 - do not be put off this forum... there are lots of good posters here -- in fact I would be so bold as to say that the opinion of each and every respondent on this thread is one worth considering and searching out should you have a particular XL problem ;-)

All the best and hopefully see you again...

[kantian1]

We are all volunteers trying to help you get the answer you want, and as fast and efficiently as possible so that you can be satisfied and continue with your projects...

We also have our own work to do and don't always have time to spend back and forthing on every thread...

So the more time you spend making sure your question is clear and concise, the faster and better response you get.

Hi,

You've missed the point. I understand there are rules and that sometimes people get annoyed when they see the same mistakes happening day in, day out.

My point is, why carry that sarcastic tone whilst reminding me of the rules?

If he/she had reminded me of the rules in a straight talking manner (As you did) it would have been far more appreciated.

Thanks.

[shg]

I echo DO's sentiments, kantian, we'd be happy to have you stick with us.

Think of the bumper sticker you see in Miami: "Come back! We weren't shooting at YOU!"

[kantian1]

I appreciate the support DonkeyOte and shg.

Thank you.

[Cheeky Charlie]

For my part, I did actually reply, with an appropriate solution (DO's is better btw), with an addendum to the effect that I thought the thread title wasn't very well thought out.

I think refusing to help until it was remedied would have been much more aggressive (although still reasonable IMHO) and chose not to take that route.

In any case, kantian, I'm not here to offend, I'm here to help, and I did. The reason people here don't like poor thread titles is because it makes it so much harder for people to help themselves (by searching). Just try to bear in mind what you would search for when titling threads and you'll find yourself writing a good description in no time at all.

CC