Writing formula

[Sunny]

My work requires me to write a formula for attendance, but I couldn't write it right. Anyone can help ASAP?


Here is the situation:
I want to calculate an employee's attendance between 1730(means5:30pm) and 1830(means 6:30pm). If one shows up before 1730(5:30pm) or after 1830(6:30pm), (means he doesn't shows up in between 1730 and 1830) then I want the answer to be 0.

Here is the formula I write:
IF(1830>A4>1730, A4-1730,0)

A4 is the column of an employee's show up time.

Logically, if his show-up time is 1720(means 5:20pm), the answer of my formula should be 0.

However, when I filled in 1720 in A4 column, the answer was -10, not 0!

Why? What's the right way to write this formula?

Your kindly help is appreciated!

 

[Aladin Akyurek]

On 2002-04-11 01:48, Sunny wrote:
My work requires me to write a formula for attendance, but I couldn't write it right. Anyone can help ASAP?


Here is the situation:
I want to calculate an employee's attendance between 1730(means5:30pm) and 1830(means 6:30pm). If one shows up before 1730(5:30pm) or after 1830(6:30pm), (means he doesn't shows up in between 1730 and 1830) then I want the answer to be 0.

Here is the formula I write:
IF(1830>A4>1730, A4-1730,0)

A4 is the column of an employee's show up time.

Logically, if his show-up time is 1720(means 5:20pm), the answer of my formula should be 0.

However, when I filled in 1720 in A4 column, the answer was -10, not 0!

Why? What's the right way to write this formula?

Your kindly help is appreciated!

=(A4>=1730)*(A4<=1830)

or

=IF(AND(A4>=1730,A4<=1830),1,0)

These formulas are equivalent. They produce 1 if show up time is between 1730 and 1830 inclusive, otherwise 0.