Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I suppress values from appearing in a graph? | Excel Discussion (Misc queries) | |||
values not appearing in autofilter list | Excel Discussion (Misc queries) | |||
values are appearing automatically in the next column | Excel Discussion (Misc queries) | |||
summing values appearing in col B when col A has been filtered | Excel Worksheet Functions | |||
Drop-down data not appearing in exported XML | Excel Discussion (Misc queries) |