"If" Formula help

[LoriD]

Can I write a formula that says, If "x" is divisible by 4(hrs), subtract .15(min)?

*I want to evaluate productivity on actual hours worked, so for every 4 hours worked, I need to deduct a 15 minute break period.

Thanks in advance.

[Jay Petrulis]

Hi,

Suppose A1 shows the hours worked.

=A1-INT(A1/4)/4

should give you the net hours. This subtracts .25 hours from each 4, so it will not work if your data is in time format rather than decimal format.

[ This Message was edited by: Jay Petrulis on 2002-05-24 14:00 ]

[Aladin Akyurek]

On 2002-05-24 13:35, LoriD wrote:
Can I write a formula that says, If "x" is divisible by 4(hrs), subtract .15(min)?

*I want to evaluate productivity on actual hours worked, so for every 4 hours worked, I need to deduct a 15 minute break period.

Thanks in advance.

Maybe:

=(MOD(A1*24,4)=0)*(SUM(A1,-"0:15:00"))

where A1 houses the hours worked in time format.

[IML]

If I read you correctly, and a1 is formatted as time, you may need to go with some thing along the lines of

=A1-INT(A1/(4/24))*15/1440

With a time of eight hours, this should result in two breaks, for a total of 7:30.

This is just kind of morph of aladin's and jay's formulas good luck.

[Yogi Anand]

Hi LoriD:
Nothing new here ... you have the formulas from Jay, Aladin and IML, I just wanted to present it to you in HOUR and Minute notation, if your time in cell A5 is in hours then ...

=A5-INT(A5/"4:00")*"0:15"

Regards!

[LoriD]

Thank you all!