Formula works, but I'm not quite sure about one segment

[johnjr5664]

The below formula works very well in an incident tracker I'm using. However; I'm not exactly certain why the N12*1440>240 portion is required?

=IF(AND($E9="IAR",(N12*1440>240)),1,0)

The formula looks at a time value (in hr:mm) and determines if the time exceeds a 4 hour (>240 (minute)) period and another one evaluates to a 1 hour (60> (minute)) period. My not understanding is why the time value in cell N12 has to multiply 1440 minutes, a 24 hour period before it determines whether it's greater than the 4 or 1 hour period?????

The bottom line is: This formula confuses me!!! (N12*1440>240)

Is there a math guru out there that can explain the reasoning behind the math portion???

 

[VoG]

The reason is that times in Excel are stored as fractions of a day. For example 12 noon is stored as 0.5.

To convert an Excel time to minutes you have to multiply it by the number of minutes in a day = 60 minutes x 24 hours = 1440.

 

[taurean]

Look at "Evaluate Formula" option in Formula Auditing. It will show you the steps that Excel performs!

 

[johnjr5664]

The reason is that times in Excel are stored as fractions of a day. For example 12 noon is stored as 0.5.

To convert an Excel time to minutes you have to multiply it by the number of minutes in a day = 60 minutes x 24 hours = 1440.

Thanks VoG... I completely forgot about Excels view of time and attempted several tries of .24, .024, .0024 to substitute the 24 minute period which has no bearing on the PC version 0.04652..... which does work!

TU

 

[VoG]

You are welcome. This would be an alternative

=IF(AND($E9="IAR",(N12>"04:00"+0)),1,0)

The +0 coerces the text value "04:00" into an Excel time.

 

[johnjr5664]

Thanks Taurean... Between U & VoG, I have a greater understanding of the Excel view of time, which I completely failed to recall. I was manually testing the value 24 minutes as .24, .024, etc... rather that the PC value of 0.04652....

Again - TU both.

 

[johnjr5664]

Thanks... I'll plug Ur alt in just for grins & giggles. BTW, Luv Ur avatar. But I prefer Cutty Sark!