Using excel 2003.

Have column with date-time values. I have column formatting: "m/d/yyyy
hh:mm:ss.000". Typical column displays "1/1/3018 00:12:03.930" in the cell
and displays "1/1/3018 12:12:04 AM" in formula bar.

When I try to get the formatted value for the cell in a macro using this
code:
Format(Worksheets("Sheet1").Cells(llCurrentRow, 14).Value, "m/d/yyyy
hh:mm:ss.000") I'm getting "1/1/3018 00:12:04.000" as my result. If I change
my macro code to use format "m/d/yyyy hh:mm:ss.sss" then I get "1/1/3018
00:12:04.044" for my result. But what I really want is the value I see
looking at the spreadsheet cell "1/1/3018 00:12:03.930".

Alternatively, I've tried to copy the cell's formatted value to another
cell, but I haven't figured out how to do that either.

Any help appreciated. TIA.

Susan

On Mon, 14 Nov 2005 11:23:02 -0800, "scw-tzg" <susan 1DOT wolitz AT trizetto
1DOT com wrote:

Using excel 2003.

Have column with date-time values. I have column formatting: "m/d/yyyy
hh:mm:ss.000". Typical column displays "1/1/3018 00:12:03.930" in the cell
and displays "1/1/3018 12:12:04 AM" in formula bar.

When I try to get the formatted value for the cell in a macro using this
code:
Format(Worksheets("Sheet1").Cells(llCurrentRow, 14).Value, "m/d/yyyy
hh:mm:ss.000") I'm getting "1/1/3018 00:12:04.000" as my result. If I change
my macro code to use format "m/d/yyyy hh:mm:ss.sss" then I get "1/1/3018
00:12:04.044" for my result. But what I really want is the value I see
looking at the spreadsheet cell "1/1/3018 00:12:03.930".

Alternatively, I've tried to copy the cell's formatted value to another
cell, but I haven't figured out how to do that either.

Any help appreciated. TIA.

Susan

I believe there are two things going on. Perhaps others can explain it better.

1. When you use the .value property, the Date format is implied, and I don't
believe (in VBA) that fractional seconds are directly supported.

2. The VBA Format function does not seem to support fractional seconds,
either.

3. I would suggest using the .value2 property and also, if you need to do
formatting within VBA, use the Application.Worksheetfunction.Text function. If
you are just interested in the raw number, you could also use CDbl(.value)

4. The following routine may be helpful in understanding what's going on.

A1: Enter your date/time string from above: 1/1/3018 00:12:03.930


Then run this VBA macro:

======================

Sub foo()
Dim c As Range

Set c = [A1]
With c.Offset(0, 1)
.Value = c.Value2
.NumberFormat = c.NumberFormat
End With

Debug.Print c.NumberFormat
Debug.Print c.Value
Debug.Print c.Value2

Debug.Print CDbl(c.Value)
Debug.Print CDbl(c.Value2)

Debug.Print Application.WorksheetFunction.Text(c.Value2, c.NumberFormat)
Debug.Print Format(c.Value2, c.NumberFormat)

End Sub

========================

In B1 you will see the same as A1.

In the Immediate Window you will see:

--------------------------
m/d/yyyy hh:mm:ss.000
1/1/3018 12:12:04 AM
408343.008378819
408343.008378819
408343.008378819
1/1/3018 00:12:03.930
1/1/3018 00:12:04.000
-------------------------

--ron

Thanks. Not sure I understand (I'm kind of new -- and temporary -- at
Excel). But I was able to get what I needed out of this.

"Ron Rosenfeld" wrote:

On Mon, 14 Nov 2005 11:23:02 -0800, "scw-tzg" <susan 1DOT wolitz AT trizetto
1DOT com wrote:

Using excel 2003.

Have column with date-time values. I have column formatting: "m/d/yyyy
hh:mm:ss.000". Typical column displays "1/1/3018 00:12:03.930" in the cell
and displays "1/1/3018 12:12:04 AM" in formula bar.

When I try to get the formatted value for the cell in a macro using this
code:
Format(Worksheets("Sheet1").Cells(llCurrentRow, 14).Value, "m/d/yyyy
hh:mm:ss.000") I'm getting "1/1/3018 00:12:04.000" as my result. If I change
my macro code to use format "m/d/yyyy hh:mm:ss.sss" then I get "1/1/3018
00:12:04.044" for my result. But what I really want is the value I see
looking at the spreadsheet cell "1/1/3018 00:12:03.930".

Alternatively, I've tried to copy the cell's formatted value to another
cell, but I haven't figured out how to do that either.

Any help appreciated. TIA.

Susan

I believe there are two things going on. Perhaps others can explain it better.

1. When you use the .value property, the Date format is implied, and I don't
believe (in VBA) that fractional seconds are directly supported.

2. The VBA Format function does not seem to support fractional seconds,
either.

3. I would suggest using the .value2 property and also, if you need to do
formatting within VBA, use the Application.Worksheetfunction.Text function. If
you are just interested in the raw number, you could also use CDbl(.value)

4. The following routine may be helpful in understanding what's going on.

A1: Enter your date/time string from above: 1/1/3018 00:12:03.930


Then run this VBA macro:

======================

Sub foo()
Dim c As Range

Set c = [A1]
With c.Offset(0, 1)
.Value = c.Value2
.NumberFormat = c.NumberFormat
End With

Debug.Print c.NumberFormat
Debug.Print c.Value
Debug.Print c.Value2

Debug.Print CDbl(c.Value)
Debug.Print CDbl(c.Value2)

Debug.Print Application.WorksheetFunction.Text(c.Value2, c.NumberFormat)
Debug.Print Format(c.Value2, c.NumberFormat)

End Sub

========================

In B1 you will see the same as A1.

In the Immediate Window you will see:

--------------------------
m/d/yyyy hh:mm:ss.000
1/1/3018 12:12:04 AM
408343.008378819
408343.008378819
408343.008378819
1/1/3018 00:12:03.930
1/1/3018 00:12:04.000
-------------------------

--ron