If formula

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

 

[Guest]

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

 

[Guest]

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]

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)