Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Calculations with date time interval | Excel Worksheet Functions | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions | |||
Time and date calculations | Excel Worksheet Functions |