Time format

[Robert Crandal]

On my spreadsheet, cell "A1" shows the time of "9:50 PM".
However, when I run the following code:

MsgBox Range("A1").Value

the message box shows a time of "0.9097222222".

How do i fix this so it shows "9:50 PM" in the MessageBox?

Thank you!

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 12:28:25 -0700 schrieb Robert Crandal:

On my spreadsheet, cell "A1" shows the time of "9:50 PM".
However, when I run the following code:

MsgBox Range("A1").Value

the message box shows a time of "0.9097222222".

How do i fix this so it shows "9:50 PM" in the MessageBox?

try:
MsgBox Format([A1], "h:mm AM/PM")


Regards
Claus Busch

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 21:32:45 +0200 schrieb Claus Busch:

MsgBox Format([A1], "h:mm AM/PM")

or:
MsgBox [A1].Text


Regards
Claus Busch

 

[Robert Crandal]

try:
MsgBox Format([A1], "h:mm AM/PM")

Thanks Claus. That code above did work great.

However, I found code in my project that does NOT
use Range("A1"). For example, a lot of my code
looks like this:

Sheets(i).Cells(n, 22).Value ' has a time value

I tried changing this code to:

Sheets(i).Cells(n, 22).Text

.....but that didn't work. What do I need to do with
this code that uses Sheets() rather than Range()??

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 12:48:02 -0700 schrieb Robert Crandal:

Sheets(i).Cells(n, 22).Value ' has a time value

I tried changing this code to:

Sheets(i).Cells(n, 22).Text

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work


Regards
Claus Busch

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 21:56:12 +0200 schrieb Claus Busch:

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work

if you want to insert a time in sheets(i).cells(n,22) then try:
Sheets(i).Cells(n, 22) = Format(TimeValue("09:15:00"), "h:mm AM/PM")


Regards
Claus Busch

 

[Robert Crandal]

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work

Now I know what went wrong. The cell that holds
a time value is protected as "hidden". Ugh, does that
mean I need to write code that temporarily removes
the "hidden" property?

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 13:04:47 -0700 schrieb Robert Crandal:

Now I know what went wrong. The cell that holds
a time value is protected as "hidden". Ugh, does that
mean I need to write code that temporarily removes
the "hidden" property?

Regards
Claus Busch

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 13:04:47 -0700 schrieb Robert Crandal:

Now I know what went wrong. The cell that holds
a time value is protected as "hidden". Ugh, does that
mean I need to write code that temporarily removes
the "hidden" property?

only the formula is hidden. The value or the text you can show with a
MsgBox


Regards
Claus Busch

 

[Robert Crandal]

only the formula is hidden. The value or the text you can show with a
MsgBox

Okay. I must have a bug somewhere, because every time I try this:

MsgBox Sheets(iSheet).Cells(n, 22).Text

....the message box only shows a "#" character and no time.
There is a valid time inside Cells(n,22). I tried removing the
hidden property and the message box still shows a "#".

Hmm, i wonder where this is going wrong?

 

[Claus Busch]

Hi Robert,

Am Mon, 29 Apr 2013 13:20:20 -0700 schrieb Robert Crandal:

...the message box only shows a "#" character and no time.
There is a valid time inside Cells(n,22). I tried removing the
hidden property and the message box still shows a "#".

what happens when you try it with:
Format(Sheets(i).Cells(n, 22), "h:mm AM/PM")


Regards
Claus Busch

 

[Robert Crandal]

what happens when you try it with:
Format(Sheets(i).Cells(n, 22), "h:mm AM/PM")

Okay, that worked perfect.

I was sure that the option that uses ".Text" would work,
but I was wrong. Anyhow, thanks for your help.

Robert

 

[Robert Crandal]

what happens when you try it with:
Format(Sheets(i).Cells(n, 22), "h:mm AM/PM")

Hi again Claus,

Just so you know, I believe that I found the problem with
the code: Sheets(i).Cells(n, 22).Text

The problem was that Cell(n, 22) had a very very
narrow column width. I'm guessing that is why the
Msgbox() was displaying a "#" character.

Thanks for the help again.