none
formulas using hard coded dates RRS feed

  • Question

  • Hi there

    i was wondering if it is possible to input hard coded dates into a formula. for example i want to be able to create a formula which displays a text say FY12 Q3 if [FInish] is less than 30th March 2012? Help appreciated :)

    N

    Friday, January 20, 2012 2:16 AM

Answers

  • >> below is what i tried to put in a custom field. all i can display is the field FY12 Q3. nothing else seems to work

    >> Iif( [Finish] >= "02/01/2012" And [Finish]  <= "30/3/2012","FY12 Q3")
    >> Iif( [Finish] >= "31/3/2012" And [Finish] <= "29/6/2012","FY12 Q4",
    >> Iif( [Finish] >= "30/6/2012" And [Finish]  <= "28/9/2012","FY13 Q1", "not set")))

    Try this:

    IIf( Finish >= DateValue("02/01/2012") And Finish <= DateValue("30/3/2012"),"FY12 Q3",
    IIf( Finish >= DateValue("31/3/2012")  And Finish <= DateValue("29/6/2012"),"FY12 Q4",
    IIf( Finish >= DateValue("30/6/2012")  And Finish <= DateValue("28/9/2012"),"FY13 Q1", "not set") ) )

    • Edited by Ismet Kocaman Saturday, January 21, 2012 10:19 PM
    • Marked as answer by Nikki Scott Tuesday, January 24, 2012 3:39 AM
    Saturday, January 21, 2012 10:09 PM
  • Niki,

    With regard to "table view". I don't use Project Server so apparently that is server terminology, so it just didn't make sense to me.

    I don't understand why the basic formula structure did not work. There shouldn't be any need to declare a date value when the date is expressed as a string. But, if that made it work, then so be it.

    With regard to your 20 nested IFs. I just did a quick test and apparently there is a limit of 14 nested IF statements so yes, you will need to break your formula into at least two pieces. I would put the first 13 nested IFs into one custom text field and the last 7 nested IFs into another custom text field. If you need all the quarterly labels in a single field, I would create a third custom text field with the following formula (assuming your custom fields are Text1 and Text2):

    Text3=IIf([text1]<>"not set",[text1],[text2])

    You can hide Text1 and Text2 so that only the final quarterly label shows in Text3.

    Hope this helps.

    By the way, I assume this thread addresses your original question which was initially in another post. Please mark that other post as answered. And, if we have collectively addressed your issue, please mark one or more of the responses in this thread as answered.

    John

    • Marked as answer by Nikki Scott Tuesday, January 24, 2012 3:39 AM
    Tuesday, January 24, 2012 2:59 AM

All replies

  • Niki,

    Sure, no problem. The following formula in an extra text field will do what you want, although I'm not sure I have the correct text if the date is not less than 3/30/2012.

    IIf([Finish]<"3/30/2012","FY12 Q3","FY12 Q4")

    Hope this helps.

    John

    Friday, January 20, 2012 2:40 AM
  • As usual, I agree with John.  This might also work...
     
    Switch(Month([Finish])<4,"Q1",Month([Finish])<7,"Q2",Month([Finish])<10,"Q3",Month([Finish])<13,"Q4")
    & " - " & "FY" & right(Year(Finish),2)
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Friday, January 20, 2012 4:03 AM
    Moderator
  • thanks John and Andrew for your responses. the reason i wanted to do this relates to another post i put up yesterday. basically we need to be able to group tasks in a table view in project according to which fiscal quarter they fall into. i thought it we created a task custom field to look at dates within a specific range to then populate the appropriate quarter that this would work.

    So this means that over 5 years I would have 20 different date quarters to put in a formula. Now i started with Johns formula above and started to extend it however I could not get my custom field to display  the appropriate dates as expected. what am i doing wrong? i also tried this in VBA and kind of getting same results (this has to be in project, and not sharepoint or excel services)

    below is what i tried to put in a custom field. all i can display is the field FY12 Q3. nothing else seems to work


    Iif( [Finish] >= "02/01/2012" And [Finish]  <= "30/3/2012","FY12 Q3")
    Iif( [Finish] >= "31/3/2012" And [Finish] <= "29/6/2012","FY12 Q4",
    Iif( [Finish] >= "30/6/2012" And [Finish]  <= "28/9/2012","FY13 Q1", "not set")))

     

    thanks

    Nikki

    Friday, January 20, 2012 5:23 AM
  • Take a look at the other formula I sent you.  Would that do the trick?
    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Friday, January 20, 2012 12:07 PM
    Moderator
  • Niki,

    You're welcome and thanks for the feedback but I have a few questions.

    What exactly is a "table" view? Are you talking about a Gantt Chart view, a combination view (i.e. Resource or Task Usage), or what?

    And how exactly do you intend to group your tasks once you have your custom field? Are you planning on using the grouping function based on your custom field or some other means?

    Lastly, I'm a little confused (easy to do with me). The formula results in a text field with a designation of which fiscal quarter the task belongs to, but you say that you were unable to get the custom field to display dates. Where exactly do the dates come in?

    John

    Friday, January 20, 2012 3:53 PM
  • While I think that Johns answer is explicitly what you asked for I tihnk that Andrew's is going to likely be the most flexible as it works for any year and is not hard coded at all.


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Friday, January 20, 2012 6:54 PM
    Moderator
  • Thanks all really appreciate your help.

    My client has specific dates which their fiscal quarters start and finish. These dates do not match just the end of a month therefore cannot assume that the end of Q4 is June 30th as in some years its 28th June. So I have all these dates for the next 5 years.

    Now I need to be able to group tasks that are finishing by my clients fiscal year quarters . I thought creating a custom field to look at the finish date of tasks to then display a text of Fy12 q1, Fy12 q2 and so on. Johns formula above worked but as soon as I added another If statement it won't work properly.while I have created exactly what they need in excel services they want this as a view in project pro 2010

    Friday, January 20, 2012 9:12 PM
  • Niki,

    You still didn't answer my questions but whatever.

    At first I thought there might be a limit on the number of nested IIF statements but I did a test run and took it up to 7 nested IIFs with no problem. Assuming you did not mistype one of your entries in the formula, it might be possible that there is a text limit on the number of characters in a formula. I didn't check that out. However, if you are trying to enter a lot of data using either a nested IIF structure or even a Switch statement, your formula is going to be very unwieldy and difficult to troubleshoot if something is wrong.

    I suggest you use a VBA approach using a Select Case statement to set up your 5 years worth of data. It's very similar to a Switch statement but a lot easier to troubleshoot.

    However, even if you can get your custom field set up I doubt it is going to give you the data your client wants. Sometimes the answer is "no", then you have to go with an alternate approach.

    John


    Saturday, January 21, 2012 3:07 AM
  • You could also modify the formula I sent you...something like:

    Switch(Month([Finish]*100+Day([Finish]))<415,"Q1",Month([Finish])*100+Day([Finish])<715,"Q2",Month([Finish])<10,"Q3",Month([Finish])<13,"Q4")
    & " - " & "FY" & right(Year(Finish),2)
    And so on....where I am using 415 for April 15th, etc.  That would let you set irregular fiscal quarters.

     


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Saturday, January 21, 2012 3:56 AM
    Moderator
  • Nice.
    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Saturday, January 21, 2012 5:13 AM
    Moderator
  • >> below is what i tried to put in a custom field. all i can display is the field FY12 Q3. nothing else seems to work

    >> Iif( [Finish] >= "02/01/2012" And [Finish]  <= "30/3/2012","FY12 Q3")
    >> Iif( [Finish] >= "31/3/2012" And [Finish] <= "29/6/2012","FY12 Q4",
    >> Iif( [Finish] >= "30/6/2012" And [Finish]  <= "28/9/2012","FY13 Q1", "not set")))

    Try this:

    IIf( Finish >= DateValue("02/01/2012") And Finish <= DateValue("30/3/2012"),"FY12 Q3",
    IIf( Finish >= DateValue("31/3/2012")  And Finish <= DateValue("29/6/2012"),"FY12 Q4",
    IIf( Finish >= DateValue("30/6/2012")  And Finish <= DateValue("28/9/2012"),"FY13 Q1", "not set") ) )

    • Edited by Ismet Kocaman Saturday, January 21, 2012 10:19 PM
    • Marked as answer by Nikki Scott Tuesday, January 24, 2012 3:39 AM
    Saturday, January 21, 2012 10:09 PM
  • thanks for this !!! that formula is perfect and works really well , it is exactly what I needed.   

    John in answer to your questions:

    - the table view; what I meant by this was that my formula relates to a custom text called "Fiscal Year" field which displays text such as FY11Q1 based on the "Finish Dates" of tasks. I wanted to include this column Fiscal Year in a table and an enterprise view for all Project Managers to use. This gives my client a very clear picture of what tasks are due to be finishing in each Fiscal Quarter.

    - i was unable to get the text field to display the appropriate text based on the dates in the formula - this is now resolved as I used the formula posted above - works a treat!!

    Now i have 1 more problem I am hoping you gurus can help with!! The formula I have written contains approx 20 IF statements and I think I have run out of text space or something? Is there a limit as to how much text I can put in a formula? I have what I need using VBA (however client doesnt like the idea of VBA). I wonder if I can split this into 2 formulas possibly that both point to 1 text field? Does anyone have any other way I can reduce the text of the formula. My formula contains 20 IF statements (4 for each year (ie 4 Quarters in each year) over 5 years.

    appreciate again all your help!!

    N

    Tuesday, January 24, 2012 12:40 AM
  • Niki,

    With regard to "table view". I don't use Project Server so apparently that is server terminology, so it just didn't make sense to me.

    I don't understand why the basic formula structure did not work. There shouldn't be any need to declare a date value when the date is expressed as a string. But, if that made it work, then so be it.

    With regard to your 20 nested IFs. I just did a quick test and apparently there is a limit of 14 nested IF statements so yes, you will need to break your formula into at least two pieces. I would put the first 13 nested IFs into one custom text field and the last 7 nested IFs into another custom text field. If you need all the quarterly labels in a single field, I would create a third custom text field with the following formula (assuming your custom fields are Text1 and Text2):

    Text3=IIf([text1]<>"not set",[text1],[text2])

    You can hide Text1 and Text2 so that only the final quarterly label shows in Text3.

    Hope this helps.

    By the way, I assume this thread addresses your original question which was initially in another post. Please mark that other post as answered. And, if we have collectively addressed your issue, please mark one or more of the responses in this thread as answered.

    John

    • Marked as answer by Nikki Scott Tuesday, January 24, 2012 3:39 AM
    Tuesday, January 24, 2012 2:59 AM