Data appearing as Values
 

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?
--
Many Thanks. Warmest Regards,

Data appearing as Values
 

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

Data appearing as Values
 

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)


"Polochilde" wrote:

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?
--
Many Thanks. Warmest Regards,

Data appearing as Values
 

This was very helpful. Thank you!

Polochilde
Many Thanks. Warmest Regards,


"Vergel Adriano" wrote:

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)


"Polochilde" wrote:

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?
--
Many Thanks. Warmest Regards,

Data appearing as Values
 

Thank you very much. It was very helpful.

Many Thanks. Warmest Regards,
Polochilde


"DaveO" wrote:

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