Code Not Converting Text Time To Real Time

[Ark68]

I am using this line of code to place a text time value into a cell, convert it to a valid time number, and format it as "h:mmA/P"

ws_cd.Cells(r, 27) = Format(CDate(.Cells(rp, 11)), "h:mmA/P") 'light off

The format is right, but I don't believe the time is true. (ie if I try to manually change the format to a number I still get the text).4

What needs tweaking?

 

[Rick Rothstein]

Show us a typical value that is in .Cells(rp, 11) and show us what gets placed in ws_cd.Cells(r, 27) so that we have some idea what you are talking about.

 

[Ark68]

Hi Rick, here is the data for example at .cells(5,11) from the source data. I've also included the results of the isnumber function for that cell confirming(?) it isn't a number. PEBF2.xlsx is the source worksheet. (different workbook)

ws_cd.Cells(r, 27) = Format(CDate(.Cells(rp, 11)), "h:mmA/P") 'light off

PEBF2.xlsx
	K	L
5	9:00 PM	FALSE
Sheet1

Cell Formulas
Range	Formula
L5	L5	=ISNUMBER(K5)

And this is that value placed at ws_cd.cells(3,27) for example, with the results (for debugging purposes) of isnumber also shown. 'task allocation.xlsx' is the destination worksheet (different workbook)

ws_cd.Cells(r, 27) = Format(CDate(.Cells(rp, 11)), "h:mmA/P") 'light off

task allocation.xlsm
	AA	AB	AC
3	9:00P		FALSE
Core_Data

Cell Formulas
Range	Formula
AC3	AC3	=ISNUMBER(AA2)

I hope this helps.

 

[Ark68]

It would appear that Excel doesn't process my time format.
Although I have used this format throughout my application, in this case it isn't working. I'll have to go back and check the other instances where this format is used to ensure that the results are indeed time, and not text. But I have not received any errors or unusual behaviour with the values having this format.

 

[Rick Rothstein]

The value placed in a cell using this line of code...

ws_cd.Cells(r, 27) = Format(CDate(.Cells(rp, 11)), "h:mmA/P") 'light off

is Text, not a date... since that text is not in a form Excel recognizes as a Date, Excel simply leaves it as Text. What I think you would need to do is output the real date to Excel and then let Excel format it the way you want. Something like this...

ws_cd.Cells(r, 27) = CDate(.Cells(rp, 11)) 'light off
ws_cd.Cells(r, 27).NumberFormat = "h:mmA/P"

 

[Ark68]

Hi Rick, thank you for sharing that last post.

I improved my code with your suggestion, but just curious ... why would this work for me?

ws_cd.Cells(r, 27) = Format(CDate(.Cells(rp, 11)), "h:mm am/pm") 'light off

 

[Rick Rothstein]

Because you have a space between the time and the AM or PM that the Format function outputs... that is a recognizable time value so Excel converts it into a real time... without the space, the text is not in a recognizable time format to Excel so it leaves it alone.

 

[Ark68]

Interesting. The workaround then is to use 'NumberFormat'. Great lesson!