Search code examples
excelvbablueprism

Filter Excel Based On Date

I want to use Blueprism to filter a dataset based on a particular date. The I column want to filter is a date time column.

When I pass a value of 01/05/2020 * no values are returned. There are values such as 01/05/2020 03:17:42

Code:

GetInstance(handle).Range(range). _ 
AutoFilter(Field:=Field,Criteria1:=SearchInput)
Solution

  • Because 01/05/2020 03:17:42 is not the same as 01/05/2020 it is bigger. Dates are saved as numeric values (amount of days since 1900-01-01).

    For example 2020-05-01 03:17:42 is the numeric value 43951.13729 where 43951 is the amount of days since 1900-01-01 and 0.13729 is the part of a day (the time)

    So if you have dates including times you need to a filter that includes all dates >=2020-05-01 and <2020-05-02. In Excel filters on dates should use the numeric values you can convert the date in SearchInput using CDbl().