using hours not time in Excel

scorpweb

Board Regular
Joined
Jul 26, 2011
Messages
124
Hey All,

i want to be able to add 0:12 as 12 minutes in Excel

if i format it to [h]:mm it LOOKS ok but in the formula bar it shows as 12:12 AM

problem with this then is that it won't pick up through excel due to this difference.


anyone know how to fix?

if it's easier i just need to check if the time is under 15minutes if it's quicker VBA could would be great
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

scorpweb

Board Regular
Joined
Jul 26, 2011
Messages
124
A2: 0:12

B2:

=IF(A2*24*60 < 15, 1, 0)

A hit is rendered as 1, a miss as a 0.

any idea how i can do this in VBA ...have lots of cells and it's going to linked to a condition to skip the row so either need to hard format the cell or VBA it.

thanks so much though :)
 

scorpweb

Board Regular
Joined
Jul 26, 2011
Messages
124
A2: 0:12

B2:

=IF(A2*24*60 < 15, 1, 0)

A hit is rendered as 1, a miss as a 0.

managed to figure it out in VBA using this :) thanks

for anyone else it may help

Code:
dim time as integer
time = Sheets("data").Cells(e, LTColTot).Value * 24 * 60

if time < 15 then
.....

thanks again
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
managed to figure it out in VBA using this :) thanks

for anyone else it may help

Code:
dim time as integer
time = Sheets("data").Cells(e, LTColTot).Value * 24 * 60

if time < 15 then
.....

thanks again

That's great.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,309
Members
409,510
Latest member
HQ2401

This Week's Hot Topics

Top