# Writing formula

This is a discussion on Writing formula within the Excel Questions forums, part of the Question Forums category; My work requires me to write a formula for attendance, but I couldn't write it right. Anyone can help ASAP? ...

1. 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?

2. 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?

=(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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•