Date Conversion on Different Computer 1

[shelby55]

Hello

I am using Excel 2002.

I created a workbook document that updates from a query in Access 2002. I have named ranges for DateSelected and Min_Date.

Everything on my computer works great but on another computer (which I don't have control over the date/time settings) the workbook isn't working. The format for dates on this new computer is dd/mm/yyyy while my computer is mm/dd/yyyy...would that have anything to do with it?

Please advise how I can get this to work...thanks.

 

[shelby55]

Hi

Further to my last post, the code I have for the filtering of my data is:

Sub All_Filt()

   With Sheets("Finance_Raw").Range("A1").CurrentRegion
   
        .AutoFilter
        If Sheets("Indi_Report").Range("H4") <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").Range("H4")
        End If
        If Sheets("Indi_Report").Range("H5") <> "" Then
            .AutoFilter Field:=2, Criteria1:="<=" & Sheets("Indi_Report").Range("H5"), _
           Operator:=xlAnd, Criteria2:=">=" & Sheets("Indi_Report").Range("H6")
         
        End If
    
    End With

End Sub

If I take out the second portion then it does filter on department...so why would the settings of the computer affect the date filter? Thanks for any assistance.

 

[dhulbert]

Could do with a bit more information when you say "It Isn't working"

Any error messages, what does it do or not do.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[shelby55]

HI

Sorry. On my computer, the filter does as I require which is filter the data on my "Finance_Raw" between the dateselected and min_date and I have named ranges for the results that are graphed.

However, on the new computer, the data isn't filtering and the graphs are blank and there are no results on the "Finance_Raw" page. When I took out the date filter of above, then it shows all months but it sorts by department so that is why I know it is a problem with the date portion of the filter.

It doesn't make sense to me because no matter what the computer settings, it's still a date. Thanks.

 

[SkipVought]

What is the display structure of the date in the table in question?

When you manually assign the date filter, how are the dates structured in the filter? Can you assign the filter manually to meet your requirements?

The sure fire way for the filter to work under any conditions is to change the criteria value to the DateSerial value, using the DateSerial function...

Criteria1:="<=" & DateSerial(Year(Sheets("Indi_Report").Range("H5")),Month(Sheets("Indi_Report").Range("H5")),Day(Sheets("Indi_Report").Range("H5"))), _
           Operator:=xlAnd, Criteria2:=">=" & DateSerial(Year(Sheets("Indi_Report").Range("H6")
),Month(Sheets("Indi_Report").Range("H6")
),Day(Sheets("Indi_Report").Range("H6")
))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Hey, I thought you'd be sick of me by now!! Thanks for the awesome reply...I just got back in my office and will try it out and let you know how it goes.

Have a great rest of the day!

 

[SkipVought]

Not at all!

Hope it works for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Okay I tried it but the graphs still aren't showing any data.

Each graph has a named range as the data source. I'll use one for an example and the following are each of the data series:

=Clinical_Indicators_Graphs_Smaller.xls!PC_Sick_MOS
=Clinical_Indicators_Graphs_Smaller.xls!Sick_MOS_Bsln
=Clinical_Indicators_Graphs_Smaller.xls!Sick_MOS_Trgt

X-Axis of dates is:

=Clinical_Indicators_Graphs_Smaller.xls!MyDates

MyDates is a named range for:

=OFFSET(Finance_Raw!$U$2,0,0,COUNTA(Finance_Raw!$U:$U)-1,1)

The column of U is the "month ending" dates that come in from the Access database.

The list of dates in the validation list of (H5) show as 30/09/2020 though I have the format showing as September 30, 2010 and this is also how the data shows in "U" for the MyDates named range.

As I mentioned, the department filter is still working on the new computer, just the date filter isn't.

Any assistance greatly appreciated - thanks!!

 

[SkipVought]

Your MyDates is in column U.

What column is Dept in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI Skip

Sorry: Column A in Finance_Raw is Department.

The named range for the data validation of Department from F4 is DeptSelected. This part of the filter is working well.

 

[SkipVought]

Your filter code says that dept is in the FIRST column of the contiguous table and that the date is in the SECOND column!!!

Sub All_Filt()

   With Sheets("Finance_Raw").Range("A1").CurrentRegion
   
        .AutoFilter
        If Sheets("Indi_Report").Range("H4") <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").Range("H4")
        End If
        If Sheets("Indi_Report").Range("H5") <> "" Then
            .AutoFilter Field:=[b][red]2[/red][/b], Criteria1:="<=" & Sheets("Indi_Report").Range("H5"), _
           Operator:=xlAnd, Criteria2:=">=" & Sheets("Indi_Report").Range("H6")
         
        End If
    
    End With

End Sub

Here's what you ought to do...

        If Sheets("Indi_Report").Range("H5") <> "" Then
            .AutoFilter Field:=[b].find("[red]YourDateHeadingValue[/red]").column[/b], Criteria1:="<=" & Sheets("Indi_Report").Range("H5"), _
           Operator:=xlAnd, Criteria2:=">=" & Sheets("Indi_Report").Range("H6")
         
        End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks. Just an FYI that the column of MyDates is the way I wish the date to be shown on the report. So Column 2(B) is month ending date and U is the format to use elsewhere in the report but both are the same dates.

Anyway, I did as you suggested and still not working on the new computer. I don't know if this makes a difference but the graphs "flash with data" but still remain blank (no axis or anything).

 

[SkipVought]

What does the DATE FILTER CRITERIA show after you run the code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The filter is applied to the finance_raw worksheet and nothing shows up. The "autofilter" arrows on each column are there but no data is showing. If I remove the autofilters then all the data shows up.

 

[shelby55]

Hi

Just an FYI that I fixed the problem thanks to a post on another website:

Sub All_Filt()
   With Sheets("Finance_Raw").Range("A1").CurrentRegion
 
        .AutoFilter
        If Sheets("Indi_Report").Range("H4") <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").Range("H4")
        End If
        If Sheets("Indi_Report").Range("H5") <> "" Then
            .AutoFilter Field:=2, Criteria1:="<=" & Format(Sheets("Indi_Report").Range("H5"), "mm/dd/yyyy"), _
           Operator:=xlAnd, Criteria2:=">=" & Format(Sheets("Indi_Report").Range("H6"), "mm/dd/yyyy")
         End If
    End With
End Sub

Thanks for your help, Skip!

 

[SkipVought]

Congrats!

BTW, you did not answer my question. I did not ask what DATA was showing. I asked that the FILTER CRITERIA values were being displayed in the filter, not on the sheet!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}