Calculations from date and time values
 

I have data from a CSV file that lists several thousand date and time values.
It extends over a 72 hour period. The values listed in the cells are the
standard date and time values (i.e., 11/15/05; and 00:00:0). The time values
are reported in 1 second increments. In a nearby column, I would like to
create a rolling sum, starting with time "zero", and ending with total time
reported in hours (approximately 72). I need it in this format so I can
graph it logarithmically.

Can't seem to get any calc to work.

kp

Calculations from date and time values
 

On Mon, 21 Nov 2005 12:59:06 -0800, "kp" wrote:

I have data from a CSV file that lists several thousand date and time values.
It extends over a 72 hour period. The values listed in the cells are the
standard date and time values (i.e., 11/15/05; and 00:00:0). The time values
are reported in 1 second increments. In a nearby column, I would like to
create a rolling sum, starting with time "zero", and ending with total time
reported in hours (approximately 72). I need it in this format so I can
graph it logarithmically.

Can't seem to get any calc to work.

kp

What formulas have you used?
What results are you seeing?

When you say you "need it in this format", what does "it" mean?

Decimal hours?

Excel stored time displayed as hours? (e.g. 72 hours would be 72:00:00)

Or something else?


--ron

Calculations from date and time values
 

"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 12:59:06 -0800, "kp" wrote:

I have data from a CSV file that lists several thousand date and time values.
It extends over a 72 hour period. The values listed in the cells are the
standard date and time values (i.e., 11/15/05; and 00:00:0). The time values
are reported in 1 second increments. In a nearby column, I would like to
create a rolling sum, starting with time "zero", and ending with total time
reported in hours (approximately 72). I need it in this format so I can
graph it logarithmically.

Can't seem to get any calc to work.

kp

What formulas have you used?
What results are you seeing?

When you say you "need it in this format", what does "it" mean?

Decimal hours?

Excel stored time displayed as hours? (e.g. 72 hours would be 72:00:00)

Or something else?


--ron

Ron:

The data looks like this:

11/15/2005 00:00.0
11/15/2005 00:01.0
11/15/2005 00:02.0
11/15/2005 00:03.0

When you click on the cell, the 00:00.0 shows 9:00 AM.

I created a column and added the date to time value =b1+c1 to come up with
one cell of combined time/data (d1). I block copied that down for the entire
data set. I added another column (e) and inserted a formula (=d1-d1) into e1
to tally my running total. My intention was to block copy this down to the
entire dataset but it is not working.

I actually need my data in decimal minutes, not hours.

Thanks,

KP

Calculations from date and time values
 

On Mon, 21 Nov 2005 13:46:05 -0800, "kp" wrote:

"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 12:59:06 -0800, "kp" wrote:

I have data from a CSV file that lists several thousand date and time values.
It extends over a 72 hour period. The values listed in the cells are the
standard date and time values (i.e., 11/15/05; and 00:00:0). The time values
are reported in 1 second increments. In a nearby column, I would like to
create a rolling sum, starting with time "zero", and ending with total time
reported in hours (approximately 72). I need it in this format so I can
graph it logarithmically.

Can't seem to get any calc to work.

kp

What formulas have you used?
What results are you seeing?

When you say you "need it in this format", what does "it" mean?

Decimal hours?

Excel stored time displayed as hours? (e.g. 72 hours would be 72:00:00)

Or something else?


--ron

Ron:

The data looks like this:

11/15/2005 00:00.0
11/15/2005 00:01.0
11/15/2005 00:02.0
11/15/2005 00:03.0

When you click on the cell, the 00:00.0 shows 9:00 AM.

I created a column and added the date to time value =b1+c1 to come up with
one cell of combined time/data (d1). I block copied that down for the entire
data set. I added another column (e) and inserted a formula (=d1-d1) into e1
to tally my running total. My intention was to block copy this down to the
entire dataset but it is not working.

I actually need my data in decimal minutes, not hours.

Thanks,

KP

A few comments.

1. I'm still not entirely sure of what you are trying to do, but I am less
confused!
2. d1-d1 will always = 0 <smile
3. The reason the formula bar shows 9:00 AM is because your time is 09:00:00.0
but formatted as mm:ss.0. So what is being displayed as 00:01.0 is equivalent
to 1.0 seconds after 9AM.

4. If you are trying to display the difference between Dn and D1, and display
in decimal minutes, then you need to use one absolute reference to keep D1
fixed, so your formula should be:

E1: =(D1-$D$1)*1440

and copy/drag down. The D1 will change to reflect the current row, and the
$D$1 will remain unchanged. The 1440 will convert the "Excel Time" which is
really stored as decimal *days* to decimal minutes (1440 minutes in a day).

If I'm still missing the point, post back with more detail.

Best,

--ron

Calculations from date and time values
 

"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 13:46:05 -0800, "kp" wrote:

"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 12:59:06 -0800, "kp" wrote:

I have data from a CSV file that lists several thousand date and time values.
It extends over a 72 hour period. The values listed in the cells are the
standard date and time values (i.e., 11/15/05; and 00:00:0). The time values
are reported in 1 second increments. In a nearby column, I would like to
create a rolling sum, starting with time "zero", and ending with total time
reported in hours (approximately 72). I need it in this format so I can
graph it logarithmically.

Can't seem to get any calc to work.

kp

What formulas have you used?
What results are you seeing?

When you say you "need it in this format", what does "it" mean?

Decimal hours?

Excel stored time displayed as hours? (e.g. 72 hours would be 72:00:00)

Or something else?


--ron

Ron:

The data looks like this:

11/15/2005 00:00.0
11/15/2005 00:01.0
11/15/2005 00:02.0
11/15/2005 00:03.0

When you click on the cell, the 00:00.0 shows 9:00 AM.

I created a column and added the date to time value =b1+c1 to come up with
one cell of combined time/data (d1). I block copied that down for the entire
data set. I added another column (e) and inserted a formula (=d1-d1) into e1
to tally my running total. My intention was to block copy this down to the
entire dataset but it is not working.

I actually need my data in decimal minutes, not hours.

Thanks,

KP

A few comments.

1. I'm still not entirely sure of what you are trying to do, but I am less
confused!
2. d1-d1 will always = 0 <smile
3. The reason the formula bar shows 9:00 AM is because your time is 09:00:00.0
but formatted as mm:ss.0. So what is being displayed as 00:01.0 is equivalent
to 1.0 seconds after 9AM.

4. If you are trying to display the difference between Dn and D1, and display
in decimal minutes, then you need to use one absolute reference to keep D1
fixed, so your formula should be:

E1: =(D1-$D$1)*1440

and copy/drag down. The D1 will change to reflect the current row, and the
$D$1 will remain unchanged. The 1440 will convert the "Excel Time" which is
really stored as decimal *days* to decimal minutes (1440 minutes in a day).

If I'm still missing the point, post back with more detail.

Best,

--ron


Ron:

Using just the time column (0:00:00) and your formula, it seems to be
calculating perfectly, until it gets down to the 900th minute (or just shy)
and it goes bonkers. Then starts giving me integers. Not sure why.

Calculations from date and time values
 

On Mon, 21 Nov 2005 16:15:07 -0800, "kp" wrote:

Ron:

Using just the time column (0:00:00) and your formula, it seems to be
calculating perfectly, until it gets down to the 900th minute (or just shy)
and it goes bonkers. Then starts giving me integers. Not sure why.

I don't know what "bonkers" means in this context.
I don't know what data you have in the time column (only the format and a few
samples).

Finally, the formula was not designed to be applied to just the time column. I
would expect that under certain circumstances, when used in that fashion, you
would obtain results different from what you expect.

Do you still have a date column?

If so, just combine the two columns within the formula:

=((B1+C1)-($B$1+$C$1))*1440

Be sure to format the result as General, or as Number with the desired number
of decimal places.


--ron

Calculations from date and time values
 

Finally, the formula was not designed to be applied to just the time column. I
would expect that under certain circumstances, when used in that fashion, you
would obtain results different from what you expect.

Do you still have a date column?

If so, just combine the two columns within the formula:

=((B1+C1)-($B$1+$C$1))*1440

Be sure to format the result as General, or as Number with the desired number
of decimal places.


--ron

Ron:

Worked perfect - thanks!

kp

Calculations from date and time values
 

On Wed, 23 Nov 2005 08:11:01 -0800, "kp" wrote:


Finally, the formula was not designed to be applied to just the time column. I
would expect that under certain circumstances, when used in that fashion, you
would obtain results different from what you expect.

Do you still have a date column?

If so, just combine the two columns within the formula:

=((B1+C1)-($B$1+$C$1))*1440

Be sure to format the result as General, or as Number with the desired number
of decimal places.


--ron

Ron:

Worked perfect - thanks!

kp

You're welcome. Glad you got it to work!

--ron