COUNT hours and days

[domgilberto]

Pleas see spread-sheet attached.

There are only 2 columns to look at.

Column B corresponds to the date found in Column A (which is in chronological order from oldest to newest).

I want to track every time in column B the value drops beneath ZERO and how long (duration) it takes from the row it dropped beneath ZERO to reach back to ZERO. The two corresponding dates will allow me to gauge the time it takes for every time this occurs.

Please see spread-sheet for more details.

Can anyone assist me with a formula? You can show the duration value in column C?

[domgilberto]

Hopefully that's clear? If not please ask and I will elaborate

[FDibbins]

If you have an excel workbook, why upload a csv file?

[domgilberto]

Because i had 110,000 rows (over the file size limit). After cutting it down loads I lost enough data for a decent example test sheet so i saved as csv... Is this a problem because I can give you a worksheet if that helps?

[Jack7774]

Pete UK came up with some formulas if you are familiar with how to use them if not you might be able to shoot him a pm and url link of your post and he most likely will be able to provide you with a solution.

http://www.excelforum.com/showthread...t=#post4350262

[FDibbins]

we just need to see enough data to show what you are working with, and some samples of what you want

[domgilberto]

we just need to see enough data to show what you are working with, and some samples of what you want

Attached

[domgilberto]

Pete UK came up with some formulas if you are familiar with how to use them if not you might be able to shoot him a pm and url link of your post and he most likely will be able to provide you with a solution.

http://www.excelforum.com/showthread...t=#post4350262

Thanks Jack although I am struggling to wrap my head around it. I've never been the sharpest in excel although i use it a lot for repetitive things Just a very slow learner!

[Jack7774]

Thanks Jack although I am struggling to wrap my head around it. I've never been the sharpest in excel although i use it a lot for repetitive things Just a very slow learner!

Your slow if you say your slow and will stay slow as long as you claim your slow. So your not slow but extremely fast and this comes natural to you! haha anyways um ya so I'm trying to figure it out myself at the moment. It will take a bit of experimenting myself.

[newdoverman]

There is a problem that I see with the data. There are times that have both 0 and below 0 values therefore a time can't be calculated as the times would be identical and the result would be 0. Row 315 is an example.

	A	B
314	02/12/2010 18:00	0.00%
315	02/12/2010 19:00	-0.01%
316	02/12/2010 19:00	0.00%
317	02/12/2010 19:00	0.00%

Row 315 drops below 0 and the time to recover is identical to the time that the drop occurred.

Here is another example

	A	B
358	02/15/2010 08:00	0.00%
359	02/15/2010 08:00	-0.04%
360	02/15/2010 08:00	-0.03%
361	02/15/2010 08:00	-0.04%
362	02/15/2010 08:00	-0.04%
363	02/15/2010 08:00	-0.03%
364	02/15/2010 08:00	0.00%

[domgilberto]

Ah yes I forgot to mention this. Is there a way to ignore situations like this?

What are your thoughts? The data is in the correct chronological order but in those instances you've highlighted the difference in time is either in seconds or more likely milliseconds apart (so not worth measuring). I am more interested in anything that has an hour duration or longer?

Any work around on this or is this a nightmare?

[JeteMc]

I found some another issue. I kept getting the row in which the draw down occurred as being row 21 instead of row 23. I increased the number of decimal places to six and the value of B21 was -0.000018 At any rate the formula that I used to find the difference in days hours:minutes between the draw down and recovery is:

Please Login or Register  to view this content.

Edit: here a copy of your file with the formula applied to the values only of the file from post #7: (Duration) draw-down recovery time cdf example.xlsx
Let me know if you have any questions.

[newdoverman]

Some of your indicated % values are very small negative values and not 0. 0 is indicated because of the number of decimal places displayed.

Enter this in C2 and fill down. It will include the very small values that are negative but indicate 0.00% as they are not really 0.

Please Login or Register  to view this content.

Enter this in D2 and fill down. Format as [h]:mm to give the hours of duration

Please Login or Register  to view this content.

To have the time as days and hours format as dd h:mm. The result will look like this 02 14:30 for 2 days 14 hours 30 minutes.

[newdoverman]

To easily hide 0 values use Conditional Formatting. Select column D and go to Conditional Formatting, New Rule, Use Formula..., and enter =D1=0 then format the font as white.

This could be valuable if you ever need to see the 0 values. All that you would have to do is to select the D column and format the font in a colour other than white.

[domgilberto]

I've yet to look and study both of your responses but thank you for responding and helping. I will post again to give you my feedback to your input.

In the meantime I have attached below a spread-sheet that consolidates my data into chronological days / hours (so there is no duplicate times). Hopefully this should be easier to apply the correct formula to? The questions is; how do I fix up the dates from the pivot table into a readable excel date format? I have no idea why the pivot table does that!?

[domgilberto]

I found some another issue. I kept getting the row in which the draw down occurred as being row 21 instead of row 23. I increased the number of decimal places to six and the value of B21 was -0.000018 At any rate the formula that I used to find the difference in days hours:minutes between the draw down and recovery is:

Please Login or Register  to view this content.

Edit: here a copy of your file with the formula applied to the values only of the file from post #7: Attachment 453614
Let me know if you have any questions.

I think you've got it here. I assume the results are stacked in logical order as it is just looking at the entire array? If this is right, how could you correspond these results with what the lowest draw-down was in the give result (say in column D)?

[domgilberto]

Ok so newdoverman your formula works perfectly and I also have the draw-down result in there now too (in column D).

Does anyone know why excel wont show duration (DD:HH:MM) on the scatter graph? See here: http://screencast.com/t/h5n1MF8GUGWe

[newdoverman]

The file that you created and uploaded in msg #15 is not useable as the dates and times are not in any way an Excel time format. The upload in msg # 17 is a picture and can't be worked with.

[domgilberto]

The file that you created and uploaded in msg #15 is not useable as the dates and times are not in any way an Excel time format. The upload in msg # 17 is a picture and can't be worked with.

Attached

[domgilberto]

Any advice? I can't seem to find out much information about using a duration variable (DD:HH:MM) on a scatter graph? Any ideas?

[newdoverman]

As promised.

[domgilberto]

As promised.

Nailed it as usual! Thanks mate!