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

#### johnjr5664

##### New Member
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???

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.

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

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

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.

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.

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

Replies
5
Views
502
Replies
9
Views
793
Replies
6
Views
595
Replies
0
Views
873
Replies
1
Views
849

1,206,761
Messages
6,074,786
Members
446,088
Latest member
Koustubh12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back