Convert time to text for timevalue function

Post the file, I;m sure the error will be easy to spot.

A couple matters to clarify:

When you say "database sheet" do you simply mean another Excel spreadsheet that contains your data or an actual data source in a database from another program like Access?

The issue you are facing may depend on the answer to the above. I am going to start by apologizing if I am underestimating your level of understanding in the following.

Time in Excel is a serial number. Dates are whole numbers counting the days from Jan 1 1900 and time is represented as a decimal value between 0-.9999...

We obviously do not perceive time this way. So if a database is passing the time value over as anything other than a decimal, Excel "translates" it to what it thinks it should be formatted as. Keep in mind formatting (what we see) and the underlying value are not always the same.

This creates a situation in which Excel is converting a decimal to a time value (time val to time val, aka not changing underlying value only formatting it), its text converted to a number during import which is then formatted as time or its text that looks like a date but Excel cannot convert to a date and keeps as text.

Checking the number format in your code doesnt confirm what the data type is, only what the format is set to (IE I can format a number as text, in which case =@ is true even though its a numeric value). You can use something like CDate, etc to convert the value before using it or confirm the type prior/during code being run.

Hard to say without the file/sample

The description did make sense, and let me answer your question about the data.

The data comes from a central database that I access from an inquiry file. It imports the data into excel, and from that point I copy and paste it into a worksheet that I have named "database". Thus, the dates originated outside of excel.

I can upload and/or give a link to the file, but it's pretty, uhm, messy...??? I started this project out as one thing and it has evolved over time. It contains 11 worksheets (and had more that have been deleted), two modules and two user forms. If I get the file to you, how much detail do you need from me to locate the issue? For instance... would I just say "module two, sub yada, yada, yada"?

EDIT: I was unfamiliar with "CDate" and looked it up. That converts a value to a date. Doesn't the "timevalue" command require a text format?

If you mean the TIMEVALUE() worksheet function, then yes it converts a value stored as text resembling a time/date to a decimal value (which is the time value, which may then be formatted to display as time in the desired syntax).

Ex: If you have "2:24 AM" as text it a) cant be used in calculations as the time, b) cannot be reformatted to something like "02:24". TIMEVALUE will let you convert it to 0.10 which can then be formatted to any time syntax desired (hh:mm).

When you get the error, does it give you an option to debug (if so which line of your code does it fail on)? I am presuming since you posted this in the VBA section its an error in your code? Am I mistaken and its a #Value! returned by a sheet formula?

A sample file would certainly help. As per my signature the more info you give the easier it is to help.

Zer0Cool--

Let's begin here and see if this does anything. I'm attaching the latest database file, and columns L & M are the times I want to convert using "timevalue". If you use the "timevalue" function on one of these cells it give you a value error. This is what is happening when running my code (I use the excel function "timevalue" to change the time to a decimal, and then my code uses the resulting numbers). My code crashed because instead of a number being in the cell it had a value error. I need the time to look the same (9:30 AM, etc.) but be a text value. Everything I do to convert it to a text value changes it into a decimal number instead. Oddly enough, it's the correct decimal as if I had used the "timevalue" function.

EDIT: GOT IT!!!! I am using the text command and the following parameters: text(cell#,HH:MM AM/PM). Side note--the original database sent to me had the time already formatted as text. Whew!! Is there a way to do this with VBA to make this a "hands-free" conversion?

Hey,

So a good give away of actual value vs formatting, compare the cell to the formula bar. In your sample the formula bar shows the "trueish" value of the Excel standard representation of time "h:mm:ss AM/PM" despite your cell showing "h:mm AM/PM". If the underlying value had been text then both the formula bar and cell would display the same thing. Also ISTEXT() and ISNUMBER() can be helpful to evaluate the true value. Another trick, in an empty cell reference a cell with a value in it, ex: =L2. Then in the formula bar highlight the =L2 and hit F9 on the keyboard. If it was a number it will change to the unformatted actual value (ie: 5:30 PM = 0.729166666666667). Be careful as hitting enter after using F9 replaces the references and/or formulas with the static values, hitting escape instead will prevent changing the cell.

If I am understanding, for your VBA code you need the data to look like a time but actually be a text value?

If the above is correct what you likely want to do is in VBA use an IF statement and check if a given cell is text or numeric first. You could use the worksheet functions in your code to evaluate this:

Please Login or Register  to view this content.

VBA I think has a function for checking but its version of ISNUMBER has many inaccuracies if I recall.

So essentially if its numeric you should be able to use in VBA:

Please Login or Register  to view this content.

basically FORMAT is the VBA version of TEXT().

If the value is text already, that may take you down a more complex path depending on how much variation there is. You may use TIMEVALUE in that case (similar to how we used ISTEXT/ISNUMBER above in VBA).