IF Formula -How to add another IF Stament

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: IF Formula -How to add another IF Stament

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF Formula -How to add another IF Stament

     
    Hi,

    A little help for my formula below please? I wanted to show in column E that if the actual login is equal to the person's schedule, she will be tagged as "P" or present and if he's tardy from his schedule but didnt exceed more than 5mins late, then he will still be tagged as Present. I wanted to add this statement to my current IF formula which is: If(E2<=5,"P") but i am getting an error "You've entered too many arguments for this function. Can somebody help please? THanks a lot!!

    A B C D E
    1 Name Schedule Actual Login Status
    2 Tanya 7:00 AM 7:05 AM =IF(d2<=c2,"P",ABS(d2-c2)*1440)
    3 Rap VL #NA

  2. #2
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,669
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

    Hi, welcome to the board

    I think the message you are seeing is more like you are creating a circular error?

    You cannot (normally) create a formula in a cell that references that same cell, it creates a circular reference error.

    Also, What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sat 12 Aug 2017) is actually 42959

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    So <=5 will always show as TRUE. You need to adjust that to something like 5/60/24 (or 0.003472222)
    If you need to test for >= 7:00 AND <=7:05, you could use something like this...
    =IF(AND(c2>=TIME(7,0,0),d2<=TIME(7,5,0)),"Y","N")

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #3
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,777
    Post Thanks / Like
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

    You could also try expanding your existing formula to
    =IF(D2<=C2,"P",IF(ABS(D2-C2)*1440<=5,"P",ABS(D2-C2)*1440))
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

    HI,

    Thank you. This one worked!

  5. #5
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,669
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

    Quote Originally Posted by HznWFM View Post
    HI,

    Thank you. This one worked!
    which 1?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  6. #6
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

    @FDibbins: Thanks for the time and for sharing some of your inputs! Appreciate it!

  7. #7
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

    this one from Fluff:

    =IF(D2<=C2,"P",IF(ABS(D2-C2)*1440<=5,"P",ABS(D2-C2)*1440))

  8. #8
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,777
    Post Thanks / Like
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)

    Default Re: IF Formula -How to add another IF Stament

      
    Glad to help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

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
  •  

 

 
DMCA.com