Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Writing formula

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Taiwan
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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