Excel Help needed with IF use with Time statement?

Question

I've created a table with values where values are based on if the number of hours is shorter or greater than a certain value.

if <1=: N3=100$
if >1 but <=2: N4=200
if >2 but <=5: N5=300
if >5 but <=30 N6=400
if >30: N7=500

I'm using a time format in column E(estimated work time) which computes the difference in times between 2 days and give you the difference in the number of hours.(start date/finish date)

The formula I've come up with is:

=IF(COUNT([@[Estimated work Time]]),IF([@[Estimated work Time]]<=TIME(1,0,0),$N$3, IF(COUNT([@[Estimated work Time]]),IF([@[Estimated work Time]]<=TIME(2,0,1),$N$4, IF(COUNT([@[Estimated work Time]]),IF([@[Estimated work Time]]<=TIME(5,0,1),$N$5, IF(COUNT([@[Estimated work Time]]),IF([@[Estimated work Time]]
The problem I'm having is that for some times it isn't working properly.
For example the first 3 values where the time is between 0 and 5 the formula works. But when the Estimated work time value is greater than 5 but less than 30 it automatically gives me 500 rather than 400. Any value above 30 also gives the value 500.

I've tried multiple other things but haven't found a way to do this. Help would be appreciated.

Answer 1

This is a horrible formula. Any more than two nested IFs, and you really ought to reconsider how you're approaching the problem.



The odd-numbered IF functions only have two arguments, which will cause them to return FALSE if the COUNT function doesn't do whatever it is meant to. (At first glance, the only possible outcomes of your COUNT are 1 if there is a time entered, and 0 if there isn't.)



The others have pointed out that TIME will not return the value you expect for times greater than 24 hours. If you are working with such large lengths of time, you should probably convert the difference to hours by multiplying by 24 (the numbers will have units in hours, not days).



Estimated work time = (End time - Start time) * 24

This formula will fail if your start/end times do not have attached dates. You will need to enter the full date/time values.



Your problem is easily solved with a lookup table, which calls for functions like LOOKUP, VLOOKUP, or perhaps the INDEX(MATCH) combination. Lookup functions prefer that the breakpoints *start* at a clean integer (0 =< n < 1, 1 <= n < 2, etc.), rather than ending on one:

=LOOKUP([@[Estimated work Time]], {0, 1, 2, 5, 30}, {100, 200, 300, 400, 500})

=LOOKUP([@[Estimated work Time]], {0, 1, 2, 5, 30}, $N$3:$N$7)





To do exactly what you're asking, you may want to consider subtracting from a fixed value and adjusting the lookup table:

=LOOKUP(100 - [@[Estimated work Time]], {0, 70, 95, 98, 99}, {500, 400, 300, 200, 100})

=LOOKUP(100 - [@[Estimated work Time]], {0, 70, 95, 98, 99}, {$N$7, $N$6, $N$5, $N$4, $N$3})



Should you want a value returned only if a time is entered, that is when you can use IF:

=IF(ISNUMBER([@[Estimated work Time]]), LOOKUP( … ), "")

Answer 2

I think your problem is time(30,0,1) is not what you think it is. What you really want is the equivalent of 1 day and 6 hours to represent 30 hours. If you do Time(30, 0, 1), I think it's still bounded by a time specification and by its nature cannot be outside of a 24 hour window.



The number you probably want is "1.25," which in Excel terms would translate to 1/2/1900 06:00 AM.



The other though I would pass on to you is you can make your formula quite a bit simpler if you use a vlookup. If you have sheet2 that looks something like this:

00:00:00 100

01:00:00 200

02:00:00 300

05:00:00 400

1/2/1900 06:00:00 500



A formula like this might make it a little cleaner:



=vlookup( @[Estimated work Time], Sheet2!$A$1:$B$5, 2, true)



rather than all of those nested ifs

Answer 3

TIME(30,0,1) is 30 hours, or 24 + 6, which makes it 6am (06:00:01 AM) so you're going from 1 am, 2am, 5am then 6am.



This, in effect, is only a difference of 1 hour from your previous argument, so if the difference is over 6, it doesn't register correctly. This info should help you fix the issue.