If formula

P

Patrick C. Simonds

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)
 
G

Gary''s Student

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),"")
 
O

OssieMac

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
 
P

Patrick C. Simonds

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)
 
Top