# Nested If Statement using Time formatted cells

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

2. ## 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. ## Re: Nested If Statement using Time formatted cells

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?

4. ## 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. ## 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

6. ## 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.

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