Error in Retrieving Formatted Cell Value

[Jay]

Hello all,

I've trying to retrieve a cell value and pass it to a method, but the number
I'm expecting and the number I'm getting don't match.

The cell is a sum of a column of cells, but the format is [hh]:mm. The
columns being summed are hour and minute values. They continue counting if
the number goes above 24 (e.g., 89:54).

So I try to retrieve the cell value, but it displays as a decimal number,
representing the days. So 134:33 is retrieved as 5.55621 (actualy decimal
values are different, I'm just using dummy data for the example).

If I multiply that number by 24 to obtain the hours, I get the number of
hours, but I still get a decimal number. So 134:33 now equals 134.5314.

Now, I can live with the multiplication by 24. I just want to know WHY it
retrieves the value this way, and how I can force it to give me it in hours.
My end goal it to retrieve the value of the cell and only pass to a function
the number of hours.

So if I read 134:33, I send 134 (or round up to 135). Not have to do this
extra work.

To retrieve the values I've been using:
Sheets(shtName).Cells(row, column).Value

I tried Format(Sheets(shtName).Cells(row, column).Value, "h") but that
doesn't change the value. I also used "hh" and "[hh]" as the format with no
success.

So I'm hoping someone knows a way to accomplish what I'm trying (and
failing) to do. Any thoughts/suggestions are appreciated.

Cheers,
Jay

 

[Trevor Shuttleworth]

Jay

If you start with 89:54 ...

formatted as [h]:mm:ss you would see 89:54:00
formatted as dd h:mm:ss you would see 03 17:54:00
formatted as general you would see 3.745833

your routine will be picking up 3.745833 which is 3 whole days plus 0.745833
of a day.

If you multiply .745833 by 24 you will get 17.9 hours ... a tenth of an hour
being 6 minutes

Hope this clarifies what you are seeing

Regards

Trevor

 

[Jay]

Thanks for the reply Trevor.

Now I understand what I'm seeing, but I can't understand how to deal with it.

If I have a decimal number, I want to find a way to convert it back into a
time value. Still trying to figure that one out.

This is mostly for my own interest, as the multiplication by 24 corrects my
problem.

Cheers,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

Jay

If you start with 89:54 ...

formatted as [h]:mm:ss you would see 89:54:00
formatted as dd h:mm:ss you would see 03 17:54:00
formatted as general you would see 3.745833

your routine will be picking up 3.745833 which is 3 whole days plus 0.745833
of a day.

If you multiply .745833 by 24 you will get 17.9 hours ... a tenth of an hour
being 6 minutes

Hope this clarifies what you are seeing

Regards

Trevor

Hello all,

I've trying to retrieve a cell value and pass it to a method, but the
number
I'm expecting and the number I'm getting don't match.

The cell is a sum of a column of cells, but the format is [hh]:mm. The
columns being summed are hour and minute values. They continue counting
if
the number goes above 24 (e.g., 89:54).

So I try to retrieve the cell value, but it displays as a decimal number,
representing the days. So 134:33 is retrieved as 5.55621 (actualy decimal
values are different, I'm just using dummy data for the example).

If I multiply that number by 24 to obtain the hours, I get the number of
hours, but I still get a decimal number. So 134:33 now equals 134.5314.

Now, I can live with the multiplication by 24. I just want to know WHY it
retrieves the value this way, and how I can force it to give me it in
hours.
My end goal it to retrieve the value of the cell and only pass to a
function
the number of hours.

So if I read 134:33, I send 134 (or round up to 135). Not have to do this
extra work.

To retrieve the values I've been using:
Sheets(shtName).Cells(row, column).Value

I tried Format(Sheets(shtName).Cells(row, column).Value, "h") but that
doesn't change the value. I also used "hh" and "[hh]" as the format with
no
success.

So I'm hoping someone knows a way to accomplish what I'm trying (and
failing) to do. Any thoughts/suggestions are appreciated.

Cheers,
Jay

 

[Trevor Shuttleworth]

Jay

Excel stores dates and times as decimal numbers. Before the decimal is the
days, after the decimal is the hours or part of an hour.

Pick any date on your spreadsheet and change the format to General to see
what you get.

Regards

Trevor

Thanks for the reply Trevor.

Now I understand what I'm seeing, but I can't understand how to deal with
it.

If I have a decimal number, I want to find a way to convert it back into a
time value. Still trying to figure that one out.

This is mostly for my own interest, as the multiplication by 24 corrects
my
problem.

Cheers,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

Jay

If you start with 89:54 ...

formatted as [h]:mm:ss you would see 89:54:00
formatted as dd h:mm:ss you would see 03 17:54:00
formatted as general you would see 3.745833

your routine will be picking up 3.745833 which is 3 whole days plus
0.745833
of a day.

If you multiply .745833 by 24 you will get 17.9 hours ... a tenth of an
hour
being 6 minutes

Hope this clarifies what you are seeing

Regards

Trevor

Hello all,

I've trying to retrieve a cell value and pass it to a method, but the
number
I'm expecting and the number I'm getting don't match.

The cell is a sum of a column of cells, but the format is [hh]:mm. The
columns being summed are hour and minute values. They continue
counting
if
the number goes above 24 (e.g., 89:54).

So I try to retrieve the cell value, but it displays as a decimal
number,
representing the days. So 134:33 is retrieved as 5.55621 (actualy
decimal
values are different, I'm just using dummy data for the example).

If I multiply that number by 24 to obtain the hours, I get the number
of
hours, but I still get a decimal number. So 134:33 now equals
134.5314.

Now, I can live with the multiplication by 24. I just want to know WHY
it
retrieves the value this way, and how I can force it to give me it in
hours.
My end goal it to retrieve the value of the cell and only pass to a
function
the number of hours.

So if I read 134:33, I send 134 (or round up to 135). Not have to do
this
extra work.

To retrieve the values I've been using:
Sheets(shtName).Cells(row, column).Value

I tried Format(Sheets(shtName).Cells(row, column).Value, "h") but that
doesn't change the value. I also used "hh" and "[hh]" as the format
with
no
success.

So I'm hoping someone knows a way to accomplish what I'm trying (and
failing) to do. Any thoughts/suggestions are appreciated.

Cheers,
Jay