Data appearing as Values

[Guest]

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?

 

[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

 

[Guest]

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)

 

[Guest]

This was very helpful. Thank you!

Polochilde
Many Thanks. Warmest Regards,

 

[Guest]

Thank you very much. It was very helpful.

Many Thanks. Warmest Regards,
Polochilde