Expand my Community achievements bar.

I need help Creating a Text Mode between 2 dates excluding weekends -

Avatar

Level 7
Hello ! I'm not proficient in text mode and I need a custom filter. we need to account for how many days it takes from when a request is assigned to us and how long it takes to "convert" to a project. how long does request sit etc? we are using this: resolveProject:entryDate=2018-04-09T13:30:00:000 resolveProject:entryDate_Mod=between resolveProject:entryDate_Range=2019-01-31T13:30:00:000 DE:Production Team Assignment Date and Time_Mod=notnull but it doesn't exclude weekend so stuff looks later =( I think I need WEEKDAYDIF somewhere ....see screen shots of how it looks on report ANY HELP WOULD BE GREATLY APPRECIATED!!! Tegwyn Stockdale Production Coordinator / PM BayCare Clearwater, Florida Baycare Health System, Inc.
Topics

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

6 Replies

Avatar

Level 10
Hi Tegwyn, I think it might be best to perform the WEEKDAYDIFF calculation on the issue custom form. Once you have this value defined, you can be certain that the number of business days is accurate so it can be used in the filters of your report. Without this, I can't think of a way to filter on weekdays only. Narayan Raum Workfront CoE Manager & Delivery Lead SunTrust Bank

Avatar

Level 4
Hmmm... I think you're on the right track with the WEEKDAYDIFF operation, but where I run into a roadblock is in pulling info on when an Issue was given a resolving object. I started with a simple WEEKDAYDIFF formula: WEEKDAYDIFF(Entry Date, ) The second date should be some data point of when the resolving object was assigned, but... I can't find any sort of field that relates to that info. There has to be a way! I always work with custom form fields, rather than complex filters, but it looks like in this case custom form fields can't do the job.... Greg Troester CHG Healthcare Services, Inc.

Avatar

Level 4
Actually, try this: Create a new field that reports only the date when your Production Team was assigned Then create the WEEKDAYDIFF as a Custom Form Field Pull your new Custom Form Field into the view So your WEEKDAYDIFF formula may look like this: WEEKDAYDIFF(Production Team Assignment Date,Conversion Date) If you name your assignment date field exactly as I have above, you *should* be able to pull that formula directly in. Does this help? Greg Troester CHG Healthcare Services, Inc.

Avatar

Level 7
I was hoping to see some replies here ! thank you very much I will try these suggestions and let you know if they worked....our consultant did have to put a custom field on the form and I didn't even think to mess with that but they link together so I must. it is working great (aside from it including weekends.) I will follow up ! Teg Tegwyn Stockdale Production Coordinator / PM BayCare Clearwater, Florida Baycare Health System, Inc.

Avatar

Level 4
Good luck! You can definitely have the custom form's calculation changed. Your consultant probably used "DATEDIFF" and some other time variable, mashing them together. Replacing "DATEDIFF" with "WEEKDAYDIFF" may be all that is required. Greg Troester CHG Healthcare Services, Inc.

Avatar

Level 7
oh how I wish it were that easy. the form with calculated fields to solve for this conversion time has THREE Field settings....I tried just changing the DATEDIFF to WEEKDAYDIF but it didn't work and I don't even know which one is doing the calculation / does one override the others? I'm going to have to put in a help ticket or go back to consultant I think here's what is on form label: Production Team Assignment Date and Time IF(!ISBLANK(Production Team Assignment Date and Time), Production Team Assignment Date and Time, IF(Team ID="581c613b00291fcafd17a2fa207195b5",$$NOW,"")) label: Production Assigned to Project Conversion Time IF(!ISBLANK(Resolve Project.Entry Date),ROUND(DATEDIFF(Production Team Assignment Date and Time, Resolve Project.Entry Date),2),"") label: Production Conversion Time Production Assigned to Project Conversion Time * -1 this was going to be my test calc - but do I have to delete others? should I copy form (which is pretty huge) and test on it - or try in sandbox ? IF(!ISBLANK(Resolve Project.Entry Date),ROUND(WEEKDAYDIFF(Production Team Assignment Date and Time, Resolve Project.Entry Date),2),"") this seems like math...l liked logic but it's been decades lol =( Tegwyn Stockdale Production Coordinator / PM BayCare Clearwater, Florida Baycare Health System, Inc.