none
Flag Formula (need a hand) RRS feed

  • Question

  • I am looking for a formula to put into a flag field that will -

    Flag is set if the date range is one day after the status date through 3 weeks after that status date

    Monday, February 18, 2019 5:25 PM

Answers

  • SmokeyJoe101,

    This formula should do it.

    IIf((ProjDateDiff([Status Date],[Finish])/[Minutes Per Day]-1)>0 And (projdatediff([Status Date],[Finish])/[Minutes Per Day]-1)<=15,Yes,No)

    The "-1" factor (2 places) takes in account the fact that the Status Date time is 8:00AM whereas the Finish date time is 5:00PM, assuming a normal standard work day. If a task's finish date is the following Monday, that will be within the one day window of a Friday status date. Likewise, assuming you meant working days when you specified a range of 3 weeks, any task that finishes within 15 days meets your requirement

    Hope this helps.

    John

    • Marked as answer by SmokeyJoe101 Tuesday, February 19, 2019 4:43 PM
    Tuesday, February 19, 2019 1:55 AM

All replies

  • SmokeyJoe101,

    Okay, what date range? The start/finish dates, baseline start/finish dates or what?

    John

    Monday, February 18, 2019 8:50 PM
  • Sorry thought I said so - I would like it to be using the Finish date as the varrible
    Monday, February 18, 2019 9:15 PM
  • SmokeyJoe101,

    This formula should do it.

    IIf((ProjDateDiff([Status Date],[Finish])/[Minutes Per Day]-1)>0 And (projdatediff([Status Date],[Finish])/[Minutes Per Day]-1)<=15,Yes,No)

    The "-1" factor (2 places) takes in account the fact that the Status Date time is 8:00AM whereas the Finish date time is 5:00PM, assuming a normal standard work day. If a task's finish date is the following Monday, that will be within the one day window of a Friday status date. Likewise, assuming you meant working days when you specified a range of 3 weeks, any task that finishes within 15 days meets your requirement

    Hope this helps.

    John

    • Marked as answer by SmokeyJoe101 Tuesday, February 19, 2019 4:43 PM
    Tuesday, February 19, 2019 1:55 AM
  • John

    Thank you - it worked as I asked, but I left something out.

    The flag must state "Yes" if the start date is the status date or greater, but the finish is less than 3 weeks out.

    So basically, I am trying to flag task that are starting on a given Status Date and finishing within the next 3 weeks as well.

    Tuesday, February 19, 2019 1:58 PM
  • I am tried this and it does not quite work

    IIf((ProjDateDiff([Status Date],[Finish])/[Minutes Per Day]-1)>0 And (projdatediff([Status Date],[Finish])/[Minutes Per Day]-1)<=15 And (projdatediff([Status Date],[Start])>1),Yes,No)


    Then I tried this and it looks like it is working, does it look correct to you?

    IIf((ProjDateDiff([Status Date],[Start])/[Minutes Per Day]+1)>0 And (projdatediff([Status Date],[Finish])/[Minutes Per Day]-1)<=15,Yes,No)

    • Edited by SmokeyJoe101 Tuesday, February 19, 2019 3:40 PM new attempt
    Tuesday, February 19, 2019 2:36 PM
  • smokeyJoe101,

    Well, yeah that's one way to get there but it kind of like going from New York to LA heading across the Atlantic. How about we go from New York to LA heading west.

    IIf([Status Date]=[Start] And (projdatediff([Status Date],[Finish])/[Minutes Per Day]-1)<=15,Yes,No)

    The caveat here is that if the task does not start at 8:00AM (e.g. starts at 9:00aM) then the test will fail and the flag will be "no". If you want the Flag field to be a "yes" for any task that starts the same day as the status date and finishes within the next 3 weeks, then the formula will get a little more complex.

    John

    Tuesday, February 19, 2019 6:53 PM
  • John

    This formula only returns a flag if the Start and the Finish dates are both a match to the Status Date - I never left New York... :)

    Tuesday, February 19, 2019 8:09 PM
  • SmokeyJoe101,

    ??? You better bail out of the big apple cause the formula works for me.

    task a: starts before status date and finishes on status date - Flag = no

    task b: starts on status date and finishes within the 15 day window - Flag = yes

    task c: starts on status date but finishes beyond the 15 day window - Flag = no

    task d: starts before status date and finishes within the 15 day window - Flag = no

    task e: starts on status date and finishes on the 15th day = Flag = yes

    Did I miss something?

    John

    Tuesday, February 19, 2019 8:36 PM
  • Strange - It certainly is working for you

    I tried it again and it still provided me with the same result - Stuck in NY

    Wednesday, February 20, 2019 12:14 AM
  • Just FYI, the default time for the status date is 17:00 (it used to be 08:00 as John says), but this was updated in the last few years....  You can check the actual time of the status date by setting the time to be dd/mm/yy hh:mm in the settings....

    I'm still in the UK btw...


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Wednesday, February 20, 2019 1:39 PM
    Moderator
  • SmokeyJoe101,

    Okay, what exactly is it doing in your plan. If you can show the formula and a screen shot of a few sample tasks, like I did, maybe I can figure something out. Or if you want to send me your file, we can go that route.

    John

    Wednesday, February 20, 2019 8:52 PM
  • Ben,

    If I understand correctly you are saying that the developers set the status date at the end of a normal work day (i.e. 5:00PM or 17:00). Correct? That makes a lot more sense to me than having a status time at the beginning of the workday like it has been in Project 2010 and earlier.

    And, being that the case, my formula needs to be tweaked to put the "adjustment" factor back in when comparing status date with start date. It would be better then to just base the comparison on the date alone and not consider the time.

    John

    Wednesday, February 20, 2019 8:57 PM
  • That's correct.  In fact I think the bug crept in if you set the status date in the project ribbon in 2010 then it defaulted to 8:00am, if you set it in the Project Information Dialogue box then it always set it to 17:00.  The bug was corrected I think in the 2013 release...

    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Wednesday, February 20, 2019 9:32 PM
    Moderator
  • Ben,

    Well that's something I didn't know. I rarely use the status date so if I did "know" this previously I've forgotten (like a lot of things :-).

    A little testing on Project 2010 gave some very interesting results, it appears to depend on how many files are open and whether the status date has previously been set or not.

    At any rate, it looks like I need to re-structure the formula I gave to SmokeyJoe and base it solely on the date (day) independent of the time. I recall doing something like that either for him or somebody else in another post.

    Thanks for jumping in and clearing some air.

    John

    Thursday, February 21, 2019 3:57 PM
  • SmokeyJoe101,

    Okay, this is a little simpler than I thought it would be. Does this formula do what you want?

    IIf(day([Status Date])=day([Start]) And (projdatediff([Status Date],[Finish])/[Minutes Per Day]-1)<=15,Yes,No)

    John

    Thursday, February 21, 2019 9:29 PM
  • The one you sent did not work.  But this one works pretty good, except for if the Status is the same as finish or start date - then it does not know what to do and returns a "NO"

    IIf((ProjDateDiff([StatusDate],[Start])/[Minutes Per Day]+1)>0 And (projdatediff([Status
    Date],[Finish])/[Minutes Per Day]-1)<=15,Yes,No)

    Wednesday, May 1, 2019 1:55 PM