How to track between two dates and keep it from continuing when it reaches a specific status

Platform discussions Special workflows & use cases
, , ,
1

I am trying to figure out how to stop tracking time (in weeks) between 2 columns when the status of the job reaches a specific status.

I have my project start date (in my board I call this the “Go Ahead”), and then I have a “Production Status” column that tells me whether the job is in progress or has been completed. This can go from “In Production” to “Installed”.

I currently am able to track how many weeks it has been up until today since the Go Ahead date using the following formula.

ROUNDDOWN(SUM(DAYS(TODAY(), {Go Ahead}),1)/7,0)&IF(ROUNDDOWN(SUM(DAYS(TODAY(), {Go Ahead}),1)/7,0)=1," week “,” weeks “)&IF(MOD(SUM(DAYS(TODAY() , {Go Ahead}),1),7)<1,”“,”& “&ROUNDDOWN(MOD(SUM(DAYS(TODAY(), {Go Ahead}),1),7),0)&” days")

However - this tracker keeps going continuously. I would like it to stop tracking and keep the week count once my production status is changed to installed.

I am able to do this but only in DAYS, whereas I want this in WEEKS as my initial formula does.
There is a formula to track and then stop tracking and keep the data but this requires me to add an extra column that states the date i have changed my production status to installed - which is this:

IF({Production Status}=“Installed”,ROUNDUP(DAYS(TODAY(),{Date Changed to Installed}),0),ROUNDUP(DAYS(TODAY(),{Go Ahead}),0))

However, as mentioned above, this formula does this in DAYS.
Is there a way for me to combine the 2 formulas so that I can track the number of weeks a project is going on for up UNTIL I change the status to installed? I have attempted to combine them myself in various ways but I either get incorrect data or error.

2

Hi @MichelleMikula - this sounds like a @JCorrell type of question! Jim - all yours :slight_smile:
Mark

3

@mark.anley

Come on Mark, you can do this one!

I’ll give you a hint… take the first formula and double it.

4

@MichelleMikula

Try this, untested but correct conceptually:

IF({Production Status} = "Installed",
   ROUNDDOWN(SUM(DAYS(TODAY(), {Date Changed to Installed}),1)/7,0) & IF(ROUNDDOWN(SUM(DAYS(TODAY(), {Date Changed to Installed}), 1)/7, 0)=1," week "," weeks ") & IF(MOD(SUM(DAYS(TODAY() , {Date Changed to Installed}),1),7)<1,"","& " & ROUNDDOWN(MOD(SUM(DAYS(TODAY(), {Date Changed to Installed}),1),7),0)&" days"),
   ROUNDDOWN(SUM(DAYS(TODAY(), {Go Ahead}),1)/7,0)&IF(ROUNDDOWN(SUM(DAYS(TODAY(), {Go Ahead}),1)/7,0)=1," week "," weeks ")&IF(MOD(SUM(DAYS(TODAY() , {Go Ahead}),1),7)<1,"","& "&ROUNDDOWN(MOD(SUM(DAYS(TODAY(), {Go Ahead}),1),7),0)&" days")
)

Jim - The Monday Man
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

5

Hi Jim!

Unfortunately, it says illegal formula. I have an actual date column for the installation of projects.
would it be easier to incorporate that date instead of the date the status changes to installed into the formula?

My columns are as follows:

  • Item
  • Go Ahead (essentially our start date)
  • Production Status (not started, in production, or installed)
  • Timeline (the count I am attempting to create with the formula)
  • Installation Date
7

I should mention that the installation dates are often more than just one day in length

8

@MichelleMikula

Did you get it working?

9

No unfortunately it is not calculating properly. would it be better to use the installation date within the formula instead of the production status?

10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.