Re: tt:mm:ss interferes with defined range?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

See if this is what you had in mind...

Let's get rid of the times in DateList...

Select the entire range, DateList
Goto the menu Data>Text to Columns
Select Fixed width
Click Next
In the Data preview box place the cursor on the second vertical break line
then double click. This will remove that break line.
Click Next
In the Data preview box click where is says General directly above the times
In Column data format select Do not import
Click Finish
With the DateList range still selected
Format as DATE

That will get rid of the times.

Now, let's create a summary of the Log data on the Stats sheet. This will be
the data you can use for a chart.

On the Stats sheet:

Enter these headers in A1:C1

Dates, Count, Size

To extract the unique dates from DateList enter this formula in A2 and copy
down as needed. This will extract the unique dates in ascending order.

=SMALL(DateList,SUMPRODUCT(COUNTIF(DateList,A$1:A1))+1)

Format these cells as DATE

To get the count for the dates in column A enter this formula in B2 and copy
down as needed:

=SUMIF(DateList,A2,CountList)

To get the total size enter this formula in C2 and copy down as needed:

=SUMIF(DateList,A2,SizeList)

You can use this data for you chart.

--
Biff
Microsoft Excel MVP


<ro@xxxxxx> wrote in message
news:65125093-bae2-4973-babf-0c72e060fff6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 22, 5:56 am, "T. Valko" <biffinp...@xxxxxxxxxxx> wrote:

Neither of those named ranges contain the TEXT value Count. That is why
both
formulas return 0. Another problem is that the drop downs for the dates
also
contain the times (although the times are not displayed because of the
format). It seems to me that you're not interested in the time. I would
just
extract the unique dates from DateList and use that as the source for the
drop downs. You might even want to remove the times from DateList, also.

Yes, your right, Im not interested in the time - only the dates. To
whole purpose of this Excel exercise is to be able to extract file
count for a specific date range - and have a chart drawn if possible.
Can you give me a hint or two on how to remove the times from the
datelist - and have Excel output the selected date range and specific
file count in some cells I can create a chart from (that is what the
two erroneous sumproduct forms were intented to do)?

Your help is really appreciated Valko. Unfortunately we live in very
different timezones and Im not able to answer your replies as quickly
as I would like to.. I know nothing about Excel so the project would
not have been possible without the help from you :-)

Yours sincerly,
Rasmus


.

Relevant Pages

  • Re: Interactive XY Scatter chart
    ... right Y Axis format depending on the data being plotted, ... made it impossible for Excel to adapt the format. ... >for the chart that indexes the original data and it will ...
    (microsoft.public.excel.charting)
  • Re: XL 2007 Display Question
    ... I feel like I have to do much more mouse clicking in Excel 2007 than before. ... The dialogs to format shapes and chart elements have many more tabs ... make a throwaway floating commandbar with two or three buttons, ...
    (microsoft.public.excel.misc)
  • Re: Interactive XY Scatter chart
    ... Excel, PowerPoint, and VBA add-ins, tutorials ... > right Y Axis format depending on the data being plotted, ... > made it impossible for Excel to adapt the format. ... >>for the chart that indexes the original data and it will ...
    (microsoft.public.excel.charting)
  • Re: A tough question? - about surface plots
    ... When I did a dummy chart, ... After changing the major unit of 0.2 resulted in a missing legend entry, ... In the process of trying these things, Excel crashed a number of times. ... Shape File or Shape Outline commands under Chart Tools, Format produces no ...
    (microsoft.public.excel.charting)
  • RE: extract time from text import
    ... I tried using the following visual basic statement to extract the time; ... Is "text" a new command in current versions of excel? ... Pete ... >> date and time format. ...
    (microsoft.public.excel.programming)