cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gjeh
Resolver III
Resolver III

DataTable filter excel source on today()

Hello all,

I have a DataTable object in my app, and the source is an excel sheet with date/product/quantity.

In the Datatable I can show all the information however once I want to apply Filter(source;DATE = Today() )  I am not returned any data.

My guess is that perhaps the dates mismatch due to exact formatting or something? but I couldn't figure it out yet so decided to ask here.

 

sales.png

1 ACCEPTED SOLUTION

Accepted Solutions
eka24
Super User III
Super User III

Try

Filter(source;Text(DateValue (DATE); "dd-mmm-yy") = Text(Today();"dd-mmm-yy" ))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

View solution in original post

5 REPLIES 5
eka24
Super User III
Super User III

Try

Filter(source;Text(DATE;"dd-mmm-yy") = Text(Today();"dd-mmm-yy" ))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

gjeh
Resolver III
Resolver III

Thanks for your suggestion. It makes a lot of sense but sadly (unless i did something wrong) it is not providing me with results from excel. 

newcode.PNG

perhaps my excel sheet formats mm-dd-yyyy ? I updated some rows to make sure todays date (9-8-2020  dd-mm-yyyy) is in there.

newcode.PNG

eka24
Super User III
Super User III

Try

Filter(source;Text(DateValue (DATE); "dd-mmm-yy") = Text(Today();"dd-mmm-yy" ))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

View solution in original post

gjeh
Resolver III
Resolver III

@eka24  ("dd-mmm-yy")    you wrote this in both replies, i assumed first it was an error meant to be "dd-mm-yyyy". is this correct or is my assumption false? 

 

Edit:  I applied your new suggestion  (but as dd-mm-yyyy) but unfortunately it didn't work because DATE was an invalid input for DateValue()  ;  however   Text(DateValue(Text(DATE))  did the trick! thanks a million for your input!!

eka24
Super User III
Super User III

Yes it an error. It has to be the same on both sides;

Filter(source;Text(DateValue (DATE); "dd-mm-yy") = Text(Today();"dd-mm-yy" ))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (62,797)