Time Format in VBA doesn't match Excel display

So far I've only tested this in Excel 2000, but a final solution would have
to work in Excel 2003 as well.

I'm reading data from cells in an Excel sheet and using the data to
construct ascii text files. The particular problem I have is this:
The cell I'm reading displays as "113:00:00" in Excel
The format of the cell is Category - Time, Type - 37:30:55 (and the sample
area shows "113:00:00" at this point.
The formula bar shows "1/14/1900 5:00:00 PM"
When reading the cell in my VB program, the raw value of the cell is
"4.70833333333333"
The NumberFormat property of the cell is "[h]:mm:ss"
When I read the cell with this number format (using the Format function), I
get the string ":01:00". The user would expect to see "113:00:00", which
Excel shows him is there.

How can I get the value that Excel is displaying, since using NumberFormat
doesn't work? (I know I could just tell the users to use straight text or
General format, but I would like to accommodate the users' desire to use
Excel formats to make their work easier.)
--
In theory, there is no difference between theory and practice; in practice,
there is.

I suggest that you read the .Text value of the cell rather than the .Value of
the text and format it before writing out the text file. By doing this the
text file will contain the formatted text.

Alok Joshi

"LabElf" wrote:

> So far I've only tested this in Excel 2000, but a final solution would have
> to work in Excel 2003 as well.
>
> I'm reading data from cells in an Excel sheet and using the data to
> construct ascii text files. The particular problem I have is this:
> The cell I'm reading displays as "113:00:00" in Excel
> The format of the cell is Category - Time, Type - 37:30:55 (and the sample
> area shows "113:00:00" at this point.
> The formula bar shows "1/14/1900 5:00:00 PM"
> When reading the cell in my VB program, the raw value of the cell is
> "4.70833333333333"
> The NumberFormat property of the cell is "[h]:mm:ss"
> When I read the cell with this number format (using the Format function), I
> get the string ":01:00". The user would expect to see "113:00:00", which
> Excel shows him is there.
>
> How can I get the value that Excel is displaying, since using NumberFormat
> doesn't work? (I know I could just tell the users to use straight text or
> General format, but I would like to accommodate the users' desire to use
> Excel formats to make their work easier.)
> --
> In theory, there is no difference between theory and practice; in practice,
> there is.

ActiveCell.Text

will give you the displayed value of the cell.

--
regards,
Tom Ogilvy

"LabElf" <LabElf@discussions.microsoft.com> wrote in message
news:A62CEE88-E606-4405-9B34-2BFD8725EC40@microsoft.com...
> So far I've only tested this in Excel 2000, but a final solution would
have
> to work in Excel 2003 as well.
>
> I'm reading data from cells in an Excel sheet and using the data to
> construct ascii text files. The particular problem I have is this:
> The cell I'm reading displays as "113:00:00" in Excel
> The format of the cell is Category - Time, Type - 37:30:55 (and the sample
> area shows "113:00:00" at this point.
> The formula bar shows "1/14/1900 5:00:00 PM"
> When reading the cell in my VB program, the raw value of the cell is
> "4.70833333333333"
> The NumberFormat property of the cell is "[h]:mm:ss"
> When I read the cell with this number format (using the Format function),
I
> get the string ":01:00". The user would expect to see "113:00:00", which
> Excel shows him is there.
>
> How can I get the value that Excel is displaying, since using NumberFormat
> doesn't work? (I know I could just tell the users to use straight text or
> General format, but I would like to accommodate the users' desire to use
> Excel formats to make their work easier.)
> --
> In theory, there is no difference between theory and practice; in
practice,
> there is.

Thank you. I'm not reading the active cell, but using the Text property of
the cell I'm reading does give me the displayed value, as you described.
--
Assigning guilt doesn't solve the problem


"Tom Ogilvy" wrote:

> ActiveCell.Text
>
> will give you the displayed value of the cell.
>
> --
> regards,
> Tom Ogilvy
>
> "LabElf" <LabElf@discussions.microsoft.com> wrote in message
> news:A62CEE88-E606-4405-9B34-2BFD8725EC40@microsoft.com...
> > So far I've only tested this in Excel 2000, but a final solution would
> have
> > to work in Excel 2003 as well.
> >
> > I'm reading data from cells in an Excel sheet and using the data to
> > construct ascii text files. The particular problem I have is this:
> > The cell I'm reading displays as "113:00:00" in Excel
> > The format of the cell is Category - Time, Type - 37:30:55 (and the sample
> > area shows "113:00:00" at this point.
> > The formula bar shows "1/14/1900 5:00:00 PM"
> > When reading the cell in my VB program, the raw value of the cell is
> > "4.70833333333333"
> > The NumberFormat property of the cell is "[h]:mm:ss"
> > When I read the cell with this number format (using the Format function),
> I
> > get the string ":01:00". The user would expect to see "113:00:00", which
> > Excel shows him is there.
> >
> > How can I get the value that Excel is displaying, since using NumberFormat
> > doesn't work? (I know I could just tell the users to use straight text or
> > General format, but I would like to accommodate the users' desire to use
> > Excel formats to make their work easier.)
> > --
> > In theory, there is no difference between theory and practice; in
> practice,
> > there is.
>
>
>

If you posted the line of code, I'd expect like Tom said that you did not
include .text but used either .value or defaulted to .value
when you specified the cell.


"LabElf" <LabElf@discussions.microsoft.com> wrote ...
> Thank you. I'm not reading the active cell, but using the Text property of
> the cell I'm reading does give me the displayed value, as you described.
> --
> Assigning guilt doesn't solve the problem

!! and if you have a problem and can't identify the culprit
!! how are you going to fix the problem. So I disagree.
!! I think perhaps you are mixing punishment with solution.
!! How about -- If you don't have a problem -- don't fix it.
>
>
> "Tom Ogilvy" wrote:
>
> > ActiveCell.Text
> >
> > will give you the displayed value of the cell.
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> > "LabElf" <LabElf@discussions.microsoft.com> wrote in message
> > news:A62CEE88-E606-4405-9B34-2BFD8725EC40@microsoft.com...
> > > So far I've only tested this in Excel 2000, but a final solution would
> > have
> > > to work in Excel 2003 as well.
> > >
> > > I'm reading data from cells in an Excel sheet and using the data to
> > > construct ascii text files. The particular problem I have is this:
> > > The cell I'm reading displays as "113:00:00" in Excel
> > > The format of the cell is Category - Time, Type - 37:30:55 (and the sample
> > > area shows "113:00:00" at this point.
> > > The formula bar shows "1/14/1900 5:00:00 PM"
> > > When reading the cell in my VB program, the raw value of the cell is
> > > "4.70833333333333"
> > > The NumberFormat property of the cell is "[h]:mm:ss"
> > > When I read the cell with this number format (using the Format function),
> > I
> > > get the string ":01:00". The user would expect to see "113:00:00", which
> > > Excel shows him is there.
> > >
> > > How can I get the value that Excel is displaying, since using NumberFormat
> > > doesn't work? (I know I could just tell the users to use straight text or
> > > General format, but I would like to accommodate the users' desire to use
> > > Excel formats to make their work easier.)
> > > --
> > > In theory, there is no difference between theory and practice; in
> > practice,
> > > there is.
> >
> >
> >

You and Tom both expected correctly; I was using the .Value property.

My tagline about assigning guilt was a response to the all too common
mentality of trying to solve a problem by finding someone too blame, which
gets into the realm of finger-pointing and buck-passing, and may inhibit
finding the real solution. I agree that sometimes finding the solution
includes detecting someone's mistake. Better wording might be "Punishing the
guilty may not solve the problem"
--
In theory, there is no difference between theory and practice; in practice,
there is.


"David McRitchie" wrote:

> If you posted the line of code, I'd expect like Tom said that you did not
> include .text but used either .value or defaulted to .value
> when you specified the cell.
>
>
> "LabElf" <LabElf@discussions.microsoft.com> wrote ...
> > Thank you. I'm not reading the active cell, but using the Text property of
> > the cell I'm reading does give me the displayed value, as you described.
> > --
> > Assigning guilt doesn't solve the problem
>
> !! and if you have a problem and can't identify the culprit
> !! how are you going to fix the problem. So I disagree.
> !! I think perhaps you are mixing punishment with solution.
> !! How about -- If you don't have a problem -- don't fix it.
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > ActiveCell.Text
> > >
> > > will give you the displayed value of the cell.
> > >
> > > --
> > > regards,
> > > Tom Ogilvy
> > >

to be clearer, I guess I should have said

ActiveCell.Text

will give you the displayed value of the activecell as an example. Apply
this theory to your actual practice. <g>

--
Regards,
Tom Ogilvy

"LabElf" <LabElf@discussions.microsoft.com> wrote in message
news:59AC6DF7-CD33-49BE-BF43-E15FB1FE2D66@microsoft.com...
> Thank you. I'm not reading the active cell, but using the Text property
of
> the cell I'm reading does give me the displayed value, as you described.
> --
> Assigning guilt doesn't solve the problem
>
>
> "Tom Ogilvy" wrote:
>
> > ActiveCell.Text
> >
> > will give you the displayed value of the cell.
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> > "LabElf" <LabElf@discussions.microsoft.com> wrote in message
> > news:A62CEE88-E606-4405-9B34-2BFD8725EC40@microsoft.com...
> > > So far I've only tested this in Excel 2000, but a final solution would
> > have
> > > to work in Excel 2003 as well.
> > >
> > > I'm reading data from cells in an Excel sheet and using the data to
> > > construct ascii text files. The particular problem I have is this:
> > > The cell I'm reading displays as "113:00:00" in Excel
> > > The format of the cell is Category - Time, Type - 37:30:55 (and the
sample
> > > area shows "113:00:00" at this point.
> > > The formula bar shows "1/14/1900 5:00:00 PM"
> > > When reading the cell in my VB program, the raw value of the cell is
> > > "4.70833333333333"
> > > The NumberFormat property of the cell is "[h]:mm:ss"
> > > When I read the cell with this number format (using the Format
function),
> > I
> > > get the string ":01:00". The user would expect to see "113:00:00",
which
> > > Excel shows him is there.
> > >
> > > How can I get the value that Excel is displaying, since using
NumberFormat
> > > doesn't work? (I know I could just tell the users to use straight
text or
> > > General format, but I would like to accommodate the users' desire to
use
> > > Excel formats to make their work easier.)
> > > --
> > > In theory, there is no difference between theory and practice; in
> > practice,
> > > there is.
> >
> >
> >

Don't sweat it, you were quite clear. I knew exactly what you meant. Also
using the .Text property might simplify some other areas of my code, such as
needing to intercept NumberFormat = "General", which returns "Ge0eral" when
formatting numbers (in my experience).
--
In theory, there is no difference between theory and practice; in practice,
there is.


"Tom Ogilvy" wrote:

> to be clearer, I guess I should have said
>
> ActiveCell.Text
>
> will give you the displayed value of the activecell as an example. Apply
> this theory to your actual practice. <g>
>
> --
> Regards,
> Tom Ogilvy
>
> "LabElf" <LabElf@discussions.microsoft.com> wrote in message
> news:59AC6DF7-CD33-49BE-BF43-E15FB1FE2D66@microsoft.com...
> > Thank you. I'm not reading the active cell, but using the Text property
> of
> > the cell I'm reading does give me the displayed value, as you described.
> > --
> > Assigning guilt doesn't solve the problem
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > ActiveCell.Text
> > >
> > > will give you the displayed value of the cell.
> > >
> > > --
> > > regards,
> > > Tom Ogilvy
> > >

You sure you weren't using "General" in VBA's Format function?

Option Explicit
Sub testme()

Dim myCell As Range
Set myCell = ActiveSheet.Range("a1")

'This seems to work all the time for me.
With myCell
.NumberFormat = "General"
.Value = 123.45
MsgBox .NumberFormat & vbLf & .Text & vbLf & .Value
End With

'but I think this is the problem & solution
MsgBox Format(123.45, "General") & vbLf & _
Format(123.45, "General Number")

End Sub

("General Number" might alleviate your Ge0eral problem.)


LabElf wrote:
>
> Don't sweat it, you were quite clear. I knew exactly what you meant. Also
> using the .Text property might simplify some other areas of my code, such as
> needing to intercept NumberFormat = "General", which returns "Ge0eral" when
> formatting numbers (in my experience).
> --
> In theory, there is no difference between theory and practice; in practice,
> there is.
>
> "Tom Ogilvy" wrote:
>
> > to be clearer, I guess I should have said
> >
> > ActiveCell.Text
> >
> > will give you the displayed value of the activecell as an example. Apply
> > this theory to your actual practice. <g>
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "LabElf" <LabElf@discussions.microsoft.com> wrote in message
> > news:59AC6DF7-CD33-49BE-BF43-E15FB1FE2D66@microsoft.com...
> > > Thank you. I'm not reading the active cell, but using the Text property
> > of
> > > the cell I'm reading does give me the displayed value, as you described.
> > > --
> > > Assigning guilt doesn't solve the problem
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > ActiveCell.Text
> > > >
> > > > will give you the displayed value of the cell.
> > > >
> > > > --
> > > > regards,
> > > > Tom Ogilvy
> > > >

--

Dave Peterson

Don't understand why you say the number format does not work, the format
you actually used for correct display can be seen by selecting the
cell and then Format, cells, custom where you would see
[hh]:mm:ss
the square brackets keep the hours from overflowing into days.

Time and date are the same in Excel, time is a fraction of a day so
one hour is 1/24 of a day. The calendar in Excel begins
Jan 1, 2000 with an incorrect leap day added to the year 2000,
which is sort of corrected in the date 2004 format used mainly on Macs.

More information in
http://www.mvps.org/dmcritchie/excel/datetime.htm
you will read pretty much the same about that aspect of date and time relationship at
http://www.cpearson.com/excel/datetime.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"LabElf" <LabElf@discussions.microsoft.com> wrote in message news:A62CEE88-E606-4405-9B34-2BFD8725EC40@microsoft.com...
> So far I've only tested this in Excel 2000, but a final solution would have
> to work in Excel 2003 as well.
>
> I'm reading data from cells in an Excel sheet and using the data to
> construct ascii text files. The particular problem I have is this:
> The cell I'm reading displays as "113:00:00" in Excel
> The format of the cell is Category - Time, Type - 37:30:55 (and the sample
> area shows "113:00:00" at this point.
> The formula bar shows "1/14/1900 5:00:00 PM"
> When reading the cell in my VB program, the raw value of the cell is
> "4.70833333333333"
> The NumberFormat property of the cell is "[h]:mm:ss"
> When I read the cell with this number format (using the Format function), I
> get the string ":01:00". The user would expect to see "113:00:00", which
> Excel shows him is there.
>
> How can I get the value that Excel is displaying, since using NumberFormat
> doesn't work? (I know I could just tell the users to use straight text or
> General format, but I would like to accommodate the users' desire to use
> Excel formats to make their work easier.)
> --
> In theory, there is no difference between theory and practice; in practice,
> there is.