keep digits on right of decimal

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

just finished a wack of datamining for times and have run into a problem with
time formatting. Whoever build the db had time stamps with the dates in some
fields and without in others. I'm analyzing everything in excel so some of
the time stamps are 51758.423 (10:30 on a date) whereas other are just 0.423
(10:30). The easiest thing (I think) is to make a column with corrected
values where I just keep the digits to the right of the decimal for the
date/time fields but don't know how to do it. I'm formatting the cells as
[mm]. I think I want a formula that will convert 51758.423 to 0.423. Any
ideas?
 
If 51748.423 is in cell A1 then in some other cell: =A1-INT(A1) will return
0.423.
The number 51748 formatted as a date is Sept.14, 2041 and 0.423 formatted as
time is 10:09:07 AM

Tyro
 
I should have added that if A1 is 51748.423 then in some other cell:
=INT(A1) will return 51748.

Tyro

Tyro said:
If 51748.423 is in cell A1 then in some other cell: =A1-INT(A1) will
return 0.423.
The number 51748 formatted as a date is Sept.14, 2041 and 0.423 formatted
as time is 10:09:07 AM

Tyro

Ian said:
just finished a wack of datamining for times and have run into a problem
with
time formatting. Whoever build the db had time stamps with the dates in
some
fields and without in others. I'm analyzing everything in excel so some
of
the time stamps are 51758.423 (10:30 on a date) whereas other are just
0.423
(10:30). The easiest thing (I think) is to make a column with corrected
values where I just keep the digits to the right of the decimal for the
date/time fields but don't know how to do it. I'm formatting the cells
as
[mm]. I think I want a formula that will convert 51758.423 to 0.423.
Any
ideas?
Click to expand...
Click to expand...
 
thanks guys - both work great. Ian.

Tyro said:
If 51748.423 is in cell A1 then in some other cell: =A1-INT(A1) will return
0.423.
The number 51748 formatted as a date is Sept.14, 2041 and 0.423 formatted as
time is 10:09:07 AM

Tyro

Ian said:
just finished a wack of datamining for times and have run into a problem
with
time formatting. Whoever build the db had time stamps with the dates in
some
fields and without in others. I'm analyzing everything in excel so some
of
the time stamps are 51758.423 (10:30 on a date) whereas other are just
0.423
(10:30). The easiest thing (I think) is to make a column with corrected
values where I just keep the digits to the right of the decimal for the
date/time fields but don't know how to do it. I'm formatting the cells as
[mm]. I think I want a formula that will convert 51758.423 to 0.423. Any
ideas?
Click to expand...
Click to expand...
 
some of the time stamps are 51758.423 (10:30 on a date) whereas
other are just 0.423 (10:30).  [....] I think I want a formula that will
convert 51758.423 to 0.423.
Click to expand...

I would use one of the following formulas, whichever suits your fancy:

=hour(A1)/24 + minute(A1)/1400 + second(A1)/86400

=(hour(A1) + (minute(A1) + second(A1)/60)/60)/24

(You might consider omitting the term with SECOND.)

The problem with A1-int(A1) is that the binary result might not
compare equal to the timestamp that was just hh:mm:ss.

I presume you see ".423" only you formatted the cell to 3 decimal
places. The actual time of day might have been between 10:08:24.24
and 10:09:50.50. The date (51758) is 9/14/2041.

If you enter, say, 9/14/2041 10:09 in A1 and just 10:09 in A2, then
compute A1-int(A1) in B1, you will find that if(A2=B1,true,false) is
false.

The reason has to do with the way most binary computer do arithmetic.

But if you compute hour(A1)/24+minute(A1)/1440 in B1, you will find
that if(A2=B1,true,false) is true.

The reason is because that is probably close to the way the time of
day was converted to a fraction of a day in the first place.
 
The general question was separating the integral from the fractional portion
of a number. The number 51758.423 is a date/time, only if formatted as such
for human consumption, otherwise, it's just a number.

Tyro

some of the time stamps are 51758.423 (10:30 on a date) whereas
other are just 0.423 (10:30). [....] I think I want a formula that will
convert 51758.423 to 0.423.
Click to expand...

I would use one of the following formulas, whichever suits your fancy:

=hour(A1)/24 + minute(A1)/1400 + second(A1)/86400

=(hour(A1) + (minute(A1) + second(A1)/60)/60)/24

(You might consider omitting the term with SECOND.)

The problem with A1-int(A1) is that the binary result might not
compare equal to the timestamp that was just hh:mm:ss.

I presume you see ".423" only you formatted the cell to 3 decimal
places. The actual time of day might have been between 10:08:24.24
and 10:09:50.50. The date (51758) is 9/14/2041.

If you enter, say, 9/14/2041 10:09 in A1 and just 10:09 in A2, then
compute A1-int(A1) in B1, you will find that if(A2=B1,true,false) is
false.

The reason has to do with the way most binary computer do arithmetic.

But if you compute hour(A1)/24+minute(A1)/1440 in B1, you will find
that if(A2=B1,true,false) is true.

The reason is because that is probably close to the way the time of
day was converted to a fraction of a day in the first place.
 
The general question was separating the integral from the fractional
portion of a number.
Click to expand...

It's a matter of interpretation. Yours is no more nor less right than
mine; reasonable people can differ reasonably. On the one hand, the
OP wrote: "the time stamps are 51758.423 (10:30 on a date) whereas
other are just 0.423 (10:30)". On the other hand, he wrote: "I'm
formatting the cells as [mm]" -- which seems odd for time of day,
IMHO.

In either case, it seems clear that the OP wants to consider the two
expressions of time as equivalent. My only point was: they might not
be equal if he does A1-int(A1). (They are not equal for the OP's
example.) That fact is not altered by your interpretation.

There is no harm in presenting the OP with two solutions and an
explanation of the difference. The OP can decide which is better for
his purpose.
 
You bicker over something like 6.14e-13. I'm sure you will agree that this
will not affect one second of your lifetime.

Tyro

The general question was separating the integral from the fractional
portion of a number.
Click to expand...

It's a matter of interpretation. Yours is no more nor less right than
mine; reasonable people can differ reasonably. On the one hand, the
OP wrote: "the time stamps are 51758.423 (10:30 on a date) whereas
other are just 0.423 (10:30)". On the other hand, he wrote: "I'm
formatting the cells as [mm]" -- which seems odd for time of day,
IMHO.

In either case, it seems clear that the OP wants to consider the two
expressions of time as equivalent. My only point was: they might not
be equal if he does A1-int(A1). (They are not equal for the OP's
example.) That fact is not altered by your interpretation.

There is no harm in presenting the OP with two solutions and an
explanation of the difference. The OP can decide which is better for
his purpose.
 
You bicker over something like 6.14e-13. I'm sure you will
agree that this will not affect one second of your lifetime.
Click to expand...

No, that's not what I am concerned about (for the OP). As usual, you
missed the point entirely.

As I wrote, what I am concerned about is that a comparison of two
timestamps -- one converted, another data entry -- will not be equal,
even thought that might have been the intent.

Whether or not that is something to be concerned about is up to the OP
to decide, based on his unique knowledge of his application. I was
merely informing the OP of the potential problem and workaround.
 
Back
Top