+ Reply to Thread
Results 1 to 5 of 5

Major nested IF statement problem. Or is there a different way to accomplish this?

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    Ithaca, New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Major nested IF statement problem. Or is there a different way to accomplish this?

    I have a major nested IF statement problem. I need a cell to check the A column (Cells A2-A20) for a specific 9 digit number(already in cell F2). If it finds the number in Column A, it will look at the time (hh:mm) in the cell next to the cell with the number (a cell in column B). If the time is greater than or equal to 8:55, "ABSENT" will be displayed in the cell. This is the formula I have right now. Its not as scary as it looks considering its the same formula 15 times (it will check 15 cells for the 9 digit number). It is easy to understand why it doesn't work though. I appreciate any assistance! Here is the formula by itself: IF(A2=F2,IF(TIMEVALUE(TEXT(B2,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF...(if false it goes onto the next formula to check the next cell)

    =IF(A2=F2,IF(TIMEVALUE(TEXT(B2,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A3=F2,IF(TIMEVALUE(TEXT(B3,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A4=F2,IF(TIMEVALUE(TEXT(B4,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A5=F2,IF(TIMEVALUE(TEXT(B5,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A6=F2,IF(TIMEVALUE(TEXT(B6,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A7=F2,IF(TIMEVALUE(TEXT(B7,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A8=F2,IF(TIMEVALUE(TEXT(B8,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A9=F2,IF(TIMEVALUE(TEXT(B9,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A10=F2,IF(TIMEVALUE(TEXT(B10,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A11=F2,IF(TIMEVALUE(TEXT(B11,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A12=F2,IF(TIMEVALUE(TEXT(B12,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A13=F2,IF(TIMEVALUE(TEXT(B13,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A14=F2,IF(TIMEVALUE(TEXT(B14,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A15=F2,IF(TIMEVALUE(TEXT(B15,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A16=F2,IF(TIMEVALUE(TEXT(B16,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A17=F2,IF(TIMEVALUE(TEXT(B17,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A18=F2,IF(TIMEVALUE(TEXT(B18,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A19=F2,IF(TIMEVALUE(TEXT(B19,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME")),IF(A20=F2,IF(TIMEVALUE(TEXT(B20,"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME"),"ABSENT")

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Major nested IF statement problem. Or is there a different way to accomplish this?

    Try

    =IFERROR(IF(TIMEVALUE(TEXT(VLOOKUP(F2,A2:B20,2,FALSE),"hh:mm"))>=TIMEVALUE("8:55"),"LATE","ONTIME"),"ABSENT")

  3. #3
    Registered User
    Join Date
    05-07-2013
    Location
    Ithaca, New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Major nested IF statement problem. Or is there a different way to accomplish this?

    Thanks so much! It works like a charm.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Major nested IF statement problem. Or is there a different way to accomplish this?

    Gald to help..

    this might be even simpler..
    Are the times in Column B just times, or are they DATE + Time ?

    If they are just times, then we can eliminate the Timevalue(Text functions..

    =IFERROR(IF(VLOOKUP(F2,A2:B20,2,FALSE)>="8:55"+0,"LATE","ONTIME"),"ABSENT")

    If they are Date+Time, then we can do

    =IFERROR(IF(MOD(VLOOKUP(F2,A2:B20,2,FALSE),1)>="8:55"+0,"LATE","ONTIME"),"ABSENT")

  5. #5
    Registered User
    Join Date
    05-07-2013
    Location
    Ithaca, New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Major nested IF statement problem. Or is there a different way to accomplish this?

    Yes, it is just the time in column B. I tried what you suggested earlier and it worked great, but the initial problem I had that caused me to take such desperate measures is that the NOW() function updates everywhere on the sheet. What I am working on is an attendance system for my technology class. Each student has a NOW() function associated with their punch in. If all of the NOW() functions update together then their punch in times will automatically be the time the last person punched in. I would like them to update separately. On another forum, someone gave me some macro code that would simply offset the time the NOW() function enters 1 cell to the right. The code you gave me works great! I still gotta mess with the macros though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1