Expand my Community achievements bar.

How do I know whether a date is a Monday or a Sunday?

Avatar

Level 10
Hi: I'd like to create a report and filter out weekdays, namely all dates that fall on a Monday through Friday. I only want the report to show hours for Saturday and Sundays. How can I do that? Is there a function I can use in a filter Text Mode to select Saturdays and Sundays? Thanks! Eric Lucas Eric MPM eric.lucas@crowley.com Crowley Information Technology
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

9 Replies

Avatar

Level 10
Hi Eric, Interesting question. I tried a few ideas to filter out Weekdays, but no joy: in textmode, the patter is "field=...,field_Mod=..." (e.g. entryDate=$$TODAY-7d, entryDate_Mod=gt to filter out all but the hours entered within (gt = greater than) the last 7 days ($$TODAY-7d). There's no opportunity in that pattern to restrict a portion of the field (such as the day of the week). I then thought about using bringing back all rows, and then (not even in text mode) use conditional formatting to "zero out" the week day hours...but alas, the conditional formatting operates on the same pattern. So, the only remaining Workfront based solutions I can think of is to either: make the report specific to weekends only, either by adding a prompt on Entry Date (e.g. picking a particular weekend to run against, to look for any hours entered on that weekend), or if use a tricky bit of OR logic in the filter to start from today and go back several weekends, if you always are interested in a particular time frame relative to today, again, no textmode required, just some noodling, working backwards to pick off each Sunday and Saturday for as many weeks back as you need (e.g. entryDate between $$TODAYbw and $$TODAYbw+24h OR entryDate between $$TODAYbw+6d and $$TODAYbw+7d OR entryDate between $$TODAYbw-7d and $$TODAYbw-6dh OR entryDate between $$TODAYbw-1d and $$TODAYbw OR entryDate between $$TODAYbw-14d and $$TODAYbw-13dh OR entryDate between $$TODAYbw-8d and $$TODAYbw-7d OR ...etc.) Aside from that, if you need an exhaustive time frame (e.g. any weekend, ever), I'd suggest the the painful (but practical) suggestion to export to Excel and Do Your Thing. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

This response is from 2018 - any new update on getting this to work in Classic Mode? I need to do this same report to show my managers how often their people are working on weekends. I'm really surprised that Workfront doesn't allow for this function - we would always want to know about overtime or days worked. Thank you!

Avatar

Level 3

Also - would you create this as a report filter? I'm trying to enter this as text mode for a new filter and it isn't working. Thank you!

ntryDate between $$TODAYbw and $$TODAYbw+24h OR entryDate between $$TODAYbw+6d and $$TODAYbw+7d OR entryDate between $$TODAYbw-7d and $$TODAYbw-6dh OR entryDate between $$TODAYbw-1d and $$TODAYbw OR entryDate between $$TODAYbw-14d and $$TODAYbw-13dh OR entryDate between $$TODAYbw-8d and $$TODAYbw-7d OR

Avatar

Level 10

Hi Denise,

To confirm, this approach was indeed a report filter (in classic), but from what you pasted, I suspect you might have missed an "e" and added an "OR", so invite you to try this:

entryDate between $$TODAYbw and $$TODAYbw+24h OR entryDate between $$TODAYbw+6d and $$TODAYbw+7d OR entryDate between $$TODAYbw-7d and $$TODAYbw-6dh OR entryDate between $$TODAYbw-1d and $$TODAYbw OR entryDate between $$TODAYbw-14d and $$TODAYbw-13dh OR entryDate between $$TODAYbw-8d and $$TODAYbw-7d

Regards,

Doug

Thank you so much Doug! I did try this as a filter - but it still shows me all days, not just Saturday and Sunday. I've attached a snapshot picture of my filter - can you take a look and see if you can spot an issue? Thank you so much!!!!

Avatar

Level 10

Hi Denise,

I suspect the difference is that in the filter I'd suggested originally to Eric, the entryDate refers to each Hour that was entered (and finding only those entered on weekends), but in your filter, you're using the End Date of the Time Sheet upon into which such Hours are entered....which, suspecting your Time Sheets normally end on a weekend, is then returning all days. Instead, I'd suggest you switch to an Hours report and use entryDate; noting that you could (and by could, I really mean "would have to") then also repeat your top criteria in every OR clause to ensure it is always enforced.

@Matt Thomas‚ in case you'd like to elaborate, I suspect the latter point could illustrate how the concept of the indented "Rule Sets" (pardon me if I've got the name wrong) you and your team are designing in New Reporting Experience could make this type of filter easier to construct.

Regards,

Doug

@Doug Den Hoed‚ You are exactly correct the rule set feature in New Reporting Experience would make it so you do not have to repeat the top criteria and also make it easier to "read" what the filter is actually doing. Thanks for another clear example of why this functionality is needed.

Thank you again for all your help! I created a new "hours" report with only the filters for "end date" that you provided. When I complete the prompt for the managers name and hit enter, I get all days worth of work unfortunately. See attached. I'm not sure why this isn't working for me. Ugh.

Avatar

Level 10

Hi Denise,

I'm assuming you meant "entryDate" (on each Hour entry) rather than "end date" (of a Timesheet), but to be sure, invite you to copy and paste your filter here, and to send full screenshots of a) your Prompt, b) the report results with no Prompt, and c) the report results with a Manager's name selected (provided, of course, that Manager is correctly set up with subordinates who have hours entered that then ought to appear).

Regards,

Doug