Data appearing as Values

P

Polochilde

Hello,

I import data in Excel from a software that keeps people punch in-out times.
The data appears as times (eg 21:30 or 20:00...) however I cannot change that
into a numeric value. Using format option did not help and when try to use a
formula the result is an error, "value".

Could you please suggest how to import as numbers or change data to numbers?
 
D

DaveO

This is routine that will change the format of highlighted cells
without losing any data. To use it, copy and paste it into your
spreadsheet as a macro; then return to your sprdsht, highlight the
cells in question, and run the macro.

Sub Selected_Range_Format()
Dim rCell As Range
Dim TrueVal As Variant

For Each rCell In Selection.Cells
TrueVal = Trim(rCell.Value)
rCell.ClearContents
rCell.NumberFormat = "hh:mm;@"
rCell.Value = TrueVal
Next rCell
End Sub
 
V

Vergel Adriano

The cells are probably formatted as text at the time data was entered. You
should be able to use the TIME function. Assuming the time is always in
hh:mm format, to convert the text value in A1, you use:

=TIME(LEFT(A1,2),RIGHT(A1,2),0)
 
P

Polochilde

Thank you very much. It was very helpful.

Many Thanks. Warmest Regards,
Polochilde
 
Top