[bmmp]

My tiny brain can't seem to get a simple excel formula to work, so I thought I would pose the question to all you brilliant programmers that could do this in your sleep.

IF (CELL A1 > 10, ADD CELL A1 + 5) (CELL A1 < 10, MINUS CELL A1 - 5)

In English, If A1 is greater than 10, add 5, if AI is less that 10, minus 5.

Mahalos!

[suthercd]

If(A1>10, A1+5, A1-5)

An If conditional evaluates the first expression (A1<10), if that is true, then the second condition is excuted ( A1+5), if it is false then the third expression is excuted.

If(This is true, execute this, otherwise excute this)

HTH
Craig

[bmmp]

suther you are the fo shizzle.
thanks!

I now have a formula that I don't think is possible in excel.
I have 2 cells with Time Data (6AM & 6PM)
I need a formula that can calculate the number of hours between the two times within another range say 3AM to 3PM.

In other words, Between the Hours of 3AM to 3PM, how many hours did this person work if they started at 6AM and ended at 6PM.

I don't have a clue where to begin.
The answer is 9, but the answer isn't as important as the formula.

any help most appreciated!

[iolaire]

Use the HOUR command:
say cell A1 is time formatted: 6:00 PM
then to get the value of that do in A2: =HOUR(A1)
say cell B1 is time formatted: 6:00 AM
then to get the value of that do in B2: =HOUR(B1)

Then just subtract in C2: =A2-B2
Which is 12 hours.

[larkost]

bmmp: The formula is only hard in that you have to think it thorough the right way. I will assume that no-one ever works across midnight, but you will probably have to check for that.

So, the meta code we are looking for is:

(whichever is earlier: the end time or 3PM) minus (whichever is later: the start time or 3AM)

and this will translate to the formula:

if(HOUR(a2) > HOUR("3PM"), HOUR("3PM"), HOUR(a2)) - if(HOUR(a1) < HOUR("3AM"), HOUR("3AM"), HOUR(a1))

you will have to think through the case where things go overnight... as that might or might not be in your problem set...