Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Is there a way to calculate business days/hours between 2 dates with Fusion 2.0?

Avatar

Level 5
 
Topics

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

1 Accepted Solution

Avatar

Correct answer by
Level 4

If you have access to Google Sheets, then you can think of it as a very powerful calculation API. In this case, you could send the two dates to a 3-cell row that uses the NETWORKDAYS function to return the value that you need in the format that you want. ~Jeff

View solution in original post

4 Replies

Avatar

Level 3

HI Krissy

This is something that i have asked about in the past, and because you cannot perform a calculation such as WEEKDAYDIFF in FUSION i hit a bit of a brick wall.

However there are two ways that this can be addressed (that i am aware of)

  1. You would have to spin up a calculated field and two date fields within Workfront itself (somewhere). If you populate the two date fields using FUSION and another module to read back the calculation using FUSION then you can take that result and use it where you need to. I use a final module to clear out the data ready for the next time. I have to do something similar where at the end of a month i want to calculate elapsed time at a certain date/time point, so the only way i could do this is to have FUSION stamp the current time in a custom field on a Project and use a calculated field to give me a figure as to how many working days/hours have elapsed since Project Entry Date and the custom timestamp field.
  2. The second option that i have seen (which looks incredible complicated) is that you have to get FUSION to read off your Workfront schedule, of which then it will iterate through each day between your two dates and that way it can calculate the same figure. I'm not entirely sure exactly how this is done as i only saw this at a glance, however it did seem to be very complex hence going for the first option in my case!

Hopefully others may have a better solution, but you can just set up a custom form with these fields in an admin area with the sole purpose being to populate > calculate > clear ready for next use.

Interested to also hear what other options people may suggest

Thanks

Avatar

Level 4

Hi Krissy,

Great question.

Jonathan, thanks for thoughtful approaches.

I don't know if it is "incredibly" complicated, but I suppose you wouldn't want to tackle this on your first day of Fusion. 😃 I'll admit in in my approach, I've taken a very basic understanding of what a "business day" is (as just a weekday). If you need to take into account holidays or other types of things that would not qualify as a "business day"...then, yes -- it will certainly get more complicated.

I've attached a scenario blueprint that shows how to do this and it includes some notes to provide some clarity. In some ways, it helps enforce some great concepts and what is possible with Fusion.

0694X00000DTYKiQAP.pngEssentially, you can use a "repeater" to iterate over the days between dates. For each iteration, determine whether that day is a weekday and then use a numeric aggregator to sum the number of days that meet your criteria. (of course, if you used a more nuanced definition of a "business day", then then in the middle you could add additional logic here to filter out days, etc..

Hopefully this helps -- you can import the blueprint and play around with it without hurting anything (also be sure to check out the notes). Cheers.

Avatar

Correct answer by
Level 4

If you have access to Google Sheets, then you can think of it as a very powerful calculation API. In this case, you could send the two dates to a 3-cell row that uses the NETWORKDAYS function to return the value that you need in the format that you want. ~Jeff