Quick way to capture days in flight for a task

1

Hi everyone,

I’m trying to improve my current formula for calculating task in flight.
NetWorkingDays(thisRow.[Created on],Today() )

The issue i now have is when the task is changing Status to “Done” this formula is still applying therefore I can’t look at how long the task was in flight as it still applies the today() function

So I would like something like:
NetWorkingDays(ThiRow/[Created on], if thisRow.State is "done" then Date it was modified to "done" or Today()

But I’m struggling to have a valid formula. If anyone could help that would be much appreciated.

All the best

2

Welcome in the community @Edward_Battistini :slight_smile:

I would use a button to make the task completed, that button will set the status to “done” and it write in a new column the ‘now()’ {not sure a now formula exist, maybe a today() } value, named “end date”
In this way you have a “start date” and an “end date” column, the last one exist only if status=done

I would prefer to use this method, but i’m 100% sure that someone else have figured out more advanced setup to allow other situation or more complex calculi, let’s see what other have made :slight_smile:

3

thanks for your help! The thing is there is already a time stamp for that event, I would like to avoid having to create a new column for writing the time… I’ll wait a bit more and see if I have to resolve to your solution!

Thanks for you support anyway

1 Like
4

Now() exists :wink: but it’s a slow formula to use with parsimony. Today() is recommended instead, if Now()can be avoided.

As for your problem @Edward_Battistini (Edit : And Welcome to you :tada:), is this what you were after ?

I’m not entirely sure, without a mocking doc and as I used Properties fields for Created On and Modified On I couldn’t really test the dates and times in there.

The formula in the If(Not done) field might also be incomplete (depending of your needs) but it should give you a lead :wink: .

And the formula actually is :

If(thisRow.Status!="Done",NetWorkingDays(thisRow.[Created On],Today()),NetWorkingDays(thisRow.[Modified On],Today()))

It states : If( Status is not = Done, then, NetworkingDays([Created On], Today()), otherwise, in any other cases, NetworkingDays([Modified On], Today())

If you really need to put condition for each status of a task, I suggest you to use a SwitchIf() formula instead of long series of If() :wink: .
(I might be wrong there, but that’s what I’d do :innocent: )

2 Likes
5

ohhh wow thank you so much! I think you gave a lot of info and I will try and feedback

1 Like
6

You’re welcome :wink: ! Always glad to help when I can :grin:

And of course, don’t hesitate to come back if you run into a problem with this :wink: .

7

So @Pch this is the solution I have now implemented

if(thisRow.[🚦Status]!=[✅ Done],NetWorkingDays(thisRow.[Created on],Today()),NetWorkingDays(thisRow.[Created on],Modified(thisRow.[🚦Status]) ))

in the Else I compare the dateCreatedOn with dateModified instead of today()

Works perfectly!

EDIT: Little caveat, I assume that “DONE” is the last status to be used

1 Like
8

I’m glad to know you’ve found a working solution :grin: !

This might effectively become a problem at some point :confused: .
The idea of using a button, as suggested in this post, to mark the task as done might be something you’ll need to try at some point though (there might be other ways too, still depending of your actual needs/goal).