Help with if function in excel

[friedrc]

Good Morning All,

I would like the ST to indicate no more than 80 hours, and OT to indicate the total # of hours over 80. What am I doing wrong with my ST & OT formulas or the format of those two cells? Any assistance would be greatly appreciated. Attached is the worksheet that has me stumped.

[Gearcutter]

Hi.
I believe because the way the cells I8:I21 are formatted excel is reading the cell value of 12:00 hours as half of one day so when you sum the range I8:I21 instead of 108 hours it calculates it as 4.5 days so when you ask the question =If(I22<80,I22,80) it reads that it is less than 80 in fact it reads 4.5 the easy way round this is to alter formula in cell I24 to
=IF(I22<3.333,I22,3.3334) 3.3334*24= 80hrs and in cellI25 formula is:
=IF(I22<3.333,Round(I22-3.3334,0)0) which deliver the correct answers that I believe you were looking for.
hope this helps
regards Howard

[EdMac]

It's all to do with time formatting. One solution, a little messy is

ST

Please Login or Register  to view this content.

OT

Please Login or Register  to view this content.

[friedrc]

Thanks for the formula suggestion. It worked just fine; however, I do have 1 additional ?. Would it be better to leave the formula as is and change the format of the cells where time is entered? What is the best format to use when time is involved? I believe the way I have it formatted requires the time to be entered in military format. Looking forward to your reply.

[Gearcutter]

Hi,
To use your original formulas then format the timesheet to number format as I have done on the attachment.


As far as which avenue to go down I personally would use original time format with Ed's formula's. Ed describes them as messy, but they are not as messy as mine.
Regards Howard

[EdMac]

Working with time can be difficult and produce results that appear non-sensical, as you found.

If I get results that don't appear to make sense, I always go to TOOLs, Formula auditing > Evaluate to see what is happening.

I think I would go with Howard and stick with the way you have started - once you understand what is going on, it ceases to be a problem.

[friedrc]

Guys,

Thanks for all of your assistance. I took Ed's suggestion and made the changes to the formulas. For whatever reason, the workers feel more comfortable seeing A.M. and P.M. I have one more question. If I want to protect the cell format but allow users to enter data in the cells I want to protect, what steps do I take to get this done?

Many thanks,

Ryan

[Gearcutter]

Hi Ryan,
to protect the time format of the input cells then select the range C8:H21
then go to format>cells> then click on Protection tab and make sure that the cells are locked that is ticked.

Then go to Tools>Protection>allow users to edit> click on new this registers the range if yiu want to enter a password do so. come out of this then Protect the worksheet in the normal way.

The range with the weekly date if that is formatted should be protected this way as well. Other cells where user has input then they should be unlocked by selecting them going to format>cells>protection and removing the tick in the Locked box.
hope I have explained this clearly for you
regards Howard

[friedrc]

I am trying to determine what's the easiest way for employees to enter < an hour for lunch. With the current format of [h]:mm, employees need to enter 00:30 for a 30 minute lunch. Is this the easiest way to enter fractions of an hour or is a better option available?


Thanks,

Ryan