Closed Thread
Results 1 to 6 of 6

Nested If Statement using Time formatted cells

  1. #1
    Registered User
    Join Date
    11-17-2008
    Location
    Texas
    Posts
    15

    Nested If Statement using Time formatted cells

    I've searched through about 4 pages of threads with "time" in the title but cannot find an example of this situation.

    I am trying to build an if statement to test variables that are in time format and then perform a calculation.

    I was able to get an example working if I convert the start times to integers rather than time values. However, the data won't be provided to me as integers.

    So, I need a nested if statement (using "and") that will test for two situations using cells in time format or I need to write a macro to convert the time data to integer format. I've been working on the former most of the day and have hit a brick wall.

    Can someone take a look at this and offer a solution or perhaps something I haven't considered.

    Thanks
    Attached Files Attached Files
    Last edited by tcowen61; 11-13-2009 at 10:18 PM. Reason: wrong file

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,677

    Re: Nested If Statement using Time formatted cells

    Try this formula in K7 copied down

    =IF(F7=8,IF(OR(J7*24={0,16,23}),8,9),F7)/24+J7

  3. #3
    Registered User
    Join Date
    11-17-2008
    Location
    Texas
    Posts
    15

    Re: Nested If Statement using Time formatted cells

    DaddyLongLegs,
    Thank you VERY much! Works perfect (of course).

    I'm not sure exactly what all it's doing, but it is a elegant solution. You guys never cease to amaze me!
    (J7*24={0,16,23})
    Is this part an array forumula?


    Are there some resources that would help me learn the various aspects of using time in calculations?
    Last edited by tcowen61; 11-13-2009 at 10:37 PM.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,677

    Re: Nested If Statement using Time formatted cells

    This part

    {0,16,23}

    is sometimes known as an "array constant", as opposed to a "calculated array" where a calculation derives the array. Here it just allows you to shorten the OR function, instead of

    =OR(J7*24={0,16,23})

    you could make that

    =OR(J7*24=0,J7*24=16,J7*24=23)

  5. #5
    Registered User
    Join Date
    09-03-2011
    Location
    Rye Brook, New York
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Nested If Statement using Time formatted cells

    I am trying to create an IF statement that allows me to calculate whether a person get time off for lunch, based on the number of hours worked in a day.

    6 hours of less = no lunch minutes
    6 to <8 hours = 30 minutes
    8 hours or more = 45 minutes

    B8 8:00 am
    B9 5:00 pm
    B10 9:00
    b11 IF Statement

    Please help

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    Belgium
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,198

    Re: Nested If Statement using Time formatted cells

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Quoting entire posts clutters the forum and makes threads hard to read !

Closed 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