If formula
 

I need a formula which will perform the calculation below, only when cell J2
is not formatted as hh:mm.


=TIME(LEFT(J2,2), RIGHT(J2,2),0)

If formula
 

First enter this UDF:

Function whatisit(r As Range) As String
whatisit = r.NumberFormat
End Function

and then

=IF(whatisit(J2)<"hh:mm", TIME(LEFT(J2,2), RIGHT(J2,2),0),"")
--
Gary''s Student - gsnu200741


"Patrick C. Simonds" wrote:

I need a formula which will perform the calculation below, only when cell J2
is not formatted as hh:mm.


=TIME(LEFT(J2,2), RIGHT(J2,2),0)

If formula
 

Hi Patrick,

See if this helps.

=IF(CELL("format",J2)="D9",J2,TIME(LEFT(J2,2), RIGHT(J2,2),0))

If format of J2 is h:mm (or hh:mm) then it places the value of J2 in the
cell. If not it does a conversion of J2 into time format.

I suggest that you have a look at Cell function in help because there are
other varieties of time format and make sure that you are testing for the
correct one.

Regards,

OssieMac

"Patrick C. Simonds" wrote:

I need a formula which will perform the calculation below, only when cell J2
is not formatted as hh:mm.


=TIME(LEFT(J2,2), RIGHT(J2,2),0)

If formula
 

Thanks for taking the time to respond. I stumbled upon this as a solution:

=IF(CELL("format", J2)="G",TIME(LEFT(J2,2), RIGHT(J2,2),0),J2)




"Gary''s Student" wrote in message
...
First enter this UDF:

Function whatisit(r As Range) As String
whatisit = r.NumberFormat
End Function

and then

=IF(whatisit(J2)<"hh:mm", TIME(LEFT(J2,2), RIGHT(J2,2),0),"")
--
Gary''s Student - gsnu200741


"Patrick C. Simonds" wrote:

I need a formula which will perform the calculation below, only when cell
J2
is not formatted as hh:mm.


=TIME(LEFT(J2,2), RIGHT(J2,2),0)