Looking for an IF formula which prints YES if less than 24 hours and NO if more than 24 hours

[JRUBES]

I am fairly novice in Excel… so hopefully someone can help, for I am in need.
I have a =SUM(E7-D7) formula in F column (time formatted) that determines the TIME difference of columns D and E…(both date formatted)
NOW, I am trying to find the formula for the G column that can answer YES if the time length is less than/equal 24 TOTAL hours (24:00:00), and NO if the time is longer/greater than 24 hours.

Is there something that is limiting this calculation? Is Excel viewing this 24:00:00 as an actual time versus a time length? ?
I came up with a couple of formulas, but I am running into issues.

D E F G

[TABLE="align: left"]
<tbody>[TR]
[TD]6/30/13 8:59[/TD]
[TD]6/30/13 16:15[/TD]
[TD]7:16:00[/TD]
[TD] YES/NO[/TD]
[/TR]
[TR]
[TD]6/30/13 15:25[/TD]
[TD]6/30/13 19:18[/TD]
[TD]3:53:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/3/13 19:23[/TD]
[TD]7/4/13 13:26[/TD]
[TD]18:03:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/6/13 11:23[/TD]
[TD]7/6/13 15:21[/TD]
[TD]3:58:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/6/13 22:35[/TD]
[TD]7/8/13 7:24[/TD]
[TD]32:49:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/6/13 2250[/TD]
[TD]7/10/13 2250[/TD]
[TD]96:00:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(F7>TIMEVALUE(“24:00:00”),”NO”,”YES”)
=IF(F7<=TIME(24,0,0),”YES”,”NO”)[/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

if in D1:
[TABLE="class: grid, width: 591"]
<tbody>[TR]
[TD="align: center"]6/30/13 8:59
[/TD]
[TD="align: center"]6/30/13 16:15[/TD]
[TD="align: center"]7:16:00[/TD]
[TD="align: center"]n[/TD]
[/TR]
[TR]
[TD="align: center"]6/30/13 15:25[/TD]
[TD="align: center"]6/30/13 19:18[/TD]
[TD="align: center"]3:53:00[/TD]
[TD="align: center"]n[/TD]
[/TR]
[TR]
[TD="align: center"]7/3/13 19:23[/TD]
[TD="align: center"]7/4/13 13:26[/TD]
[TD="align: center"]18:03:00[/TD]
[TD="align: center"]n[/TD]
[/TR]
[TR]
[TD="align: center"]7/6/13 11:23[/TD]
[TD="align: center"]7/6/13 15:21[/TD]
[TD="align: center"]3:58:00[/TD]
[TD="align: center"]n[/TD]
[/TR]
[TR]
[TD="align: center"]7/6/13 22:35[/TD]
[TD="align: center"]7/8/13 7:24[/TD]
[TD="align: center"]32:49:00[/TD]
[TD="align: center"]y[/TD]
[/TR]
[TR]
[TD="align: center"]7/6/13 22:50[/TD]
[TD="align: center"]7/10/13 22:50[/TD]
[TD="align: center"]96:00:00[/TD]
[TD="align: center"]y[/TD]
[/TR]
</tbody>[/TABLE]

formula in G1 is =IF(F1>=TIME(23,59,59)+ TIME(0,0,1),"y","n")

 

[JRUBES]

ill try that! Like the motocon... that was me all day

 

[JRUBES]

cyrilbrd, that worked like excel magic! Thanks for the prompt help!!

if you have a sec, could you explain why =TIME(1,0,0) would work for 1 hour length, but when reaching the farther times of length, it would not?

 

[cyrilbrd]

Thanks for the feedback.
Glad it worked for you.
Based on Microsoft Excel Help:

TIME(hour,minute,second) Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

So for 24 Hours, we need to add a second (here stated as TIME(0,0,1)).
Was this answering your query?

 

[JRUBES]

Resolved! Thanks again!

 

[cyrilbrd]

Resolved! Thanks again!

Most welcome.
Glad it worked for you.
Thanks for the feedback.

 

[mfexcel]

Hi cyribrd, very good observation about the TIME.

Btw, may I ask why not simply use:
=if(F1<=1,"Yes",No")

 

[cyrilbrd]

Yes mfexcel it would work as TIME(24,0,0) would be equivalent to 1. I however made this proposal as to give the possibility to the OP to adjust his limiting factor in a TIME format as to ensure that it will be easier for him, hence 0.999988425925926 is not as comprehensible as TIME(23,59,59).
But yes you are correct in saying that =IF(F1<=1,"No","Yes") would work as it is the same as what I proposed.

Cheers!

 

[mfexcel]

You are a detailed person! That's the essential quality for being good at Excel!

Yes mfexcel it would work as TIME(24,0,0) would be equivalent to 1. I however made this proposal as to give the possibility to the OP to adjust his limiting factor in a TIME format as to ensure that it will be easier for him, hence 0.999988425925926 is not as comprehensible as TIME(23,59,59).
But yes you are correct in saying that =IF(F1<=1,"No","Yes") would work as it is the same as what I proposed.

eers!