Return a custom date value

[Colejweinman]

Hello!

I am working on a request, with the report returning one weeks worth of data. I have a "tool" that returns the most recent day (picture attached,) and I am looking for it to have the same functionality, but allow me to use different time series values. i.e., most recent week, month, etc.

Any hlep is appreciated!

[MarqueeCrew · Accepted solution]

If you parsed the date, you could find most recent month in a similar fashion (datetimeformat).

Here is a trick that might help you also:

Say you have the date:  2016-04-22

You can use DateTimeFormat([MyDateField],"%W"), this will return the week number as 00-53, with the beginnin of weeks as Monday (%U returns weeks beginning with Sunday).

[JoeM · Accepted solution]

@Colejweinman

Attached is my sample of what you might be looking for. This workflow utilized the datetimetrim() function  and can return records based on "max" month, day, year, hour, and minute. 

Note that this will not capture max week like @MarqueeCrew

[michael_treadwell · Accepted solution]

Since you specifically asked for the ability to choose by max week and you cannot use week as a date part in the DATETIMETRIM() function, it makes the example a little tricky.

I've attached a macro that let's you choose a date part and it returns all relevant rows. This example focuses on Date parts and not Time parts so it would have to be modified if that is a requirement.

[Colejweinman]

All three solutions worked for me! Thank you to all who helped out. I do appreciate you taking the time to write the macro, Michael!