none
Sharepoint 2007 - Weekdays/NetworkedDays Calculated Field Issues

    Question

  • I have a problem using a previous posts formula that works for 30% of my requirements.

    I am trying to populate a newly created calc [Date Var] column to retrieve data based upon the efforts shown in the formula provided in the formula below.

     

    The formula below does work for the 30% of the output that I need to see. However the only thing I would prefer to see that needs re-coding in the current configuration formula is:

    1. I need the numeration output of the current formula which is giving me the out put value of (1) to be changed to (0) zero: If both [New-BL-End-Date] & [BL 0 F-Date] columns contain the same dates or either columns contain nothing (blank) I want a zero to appear.
    2. I also need the [Date Var] column to give me a +(#) value if the [New-BL-End-Date] is earlier then the [BL 0 F-Date] column date

    What am I trying to achieve: I want the value of the total working days between two given periods to be reflected as a plus or minus or a zero.

    Here is the code I have to date from a previous post

    =IF(AND((WEEKDAY([New-BL-End-Date],2))<(WEEKDAY([BL 0 F-Date],2)),((WEEKDAY([BL 0 F-Date],2))-(WEEKDAY([New-BL-End-Date],2)))>0),(((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+0))-(FLOOR((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+0)/7,1)*2)-2),(((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+0))-(FLOOR((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+0)/7,1)*2)))

    Thanks

    Mike





    Monday, February 20, 2012 1:52 PM

Answers

  • Hi Michael,

    Just wanted to clarify if I understand you correctly.

    1) if both dates are the same you want a zero instead of the normal result of 1

    2) if [New-BL-End-Date] is earlier than [BL 0 F-Date] you want +(the number of days)

    3) if [New-BL-End-Date] is after [BL 0 F-Date] you want -(the number of days)

    Cheers

    B

    Wednesday, February 22, 2012 4:36 PM
  • Hi Michael,

    There seems to be a - missing in the code.  I have the corrected code below.

    =IF(Rev_BL_End_Date=BL_End_Date,0,IF(Rev_BL_End_Date<BL_End_Date,CONCATENATE("+(",IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2))),")"),CONCATENATE("-(",IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2))),")")))

    But I just realised that I haven't allowed for all situations when it could be 0.  I just have it if both dates are equal.  But that doesn't count for non working days.  I'll try include that into it and get back to you.  But try the above for the moment.

    Monday, February 27, 2012 10:40 AM
  • Hi Michael,

    Yes we can remove the brackets and we can just show it when negative.

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,CONCATENATE("-",IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))-1)))

    Cheers

    B

    Tuesday, February 28, 2012 3:09 PM
  • Ahh I see.  This shouldn't be a problem.  We can just multiply the result by -1 instead of the concat, which is resulting in a text value and is probably why its not being copied.  This should solve that.

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))*(-1)))

    Wednesday, February 29, 2012 9:07 AM
  • Hi Brian

    Problem 1 - Yes could we show it as 1 day rather then 2 days if possible please

    Problem 2 - is working great!

    Cheers

    Michael

    Monday, February 27, 2012 3:33 PM
  • Oops!  Deleted a little bit when i put in the * -1

    here you go.

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,(IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))-1)*(-1)))

    • Proposed as answer by bmcomp Friday, March 02, 2012 5:54 PM
    • Marked as answer by Michael Gowlett Friday, March 02, 2012 7:06 PM
    Wednesday, February 29, 2012 11:04 AM

All replies

  • Hi Michael,

    Just wanted to clarify if I understand you correctly.

    1) if both dates are the same you want a zero instead of the normal result of 1

    2) if [New-BL-End-Date] is earlier than [BL 0 F-Date] you want +(the number of days)

    3) if [New-BL-End-Date] is after [BL 0 F-Date] you want -(the number of days)

    Cheers

    B

    Wednesday, February 22, 2012 4:36 PM
  • Hi B 

    Yes that is exactly what I would like to do...I appeciate your help.

    I look forward to your response.

    Thanks

    Mike

    Wednesday, February 22, 2012 7:24 PM
  • Hi Michael,

    This should do the job for you.  All I did was used 2 more if statements to compare the dates and then performed the same code above and concatenated it with your required format.

    =IF([New-BL-End-Date]=[BL 0 F-Date],0,IF([New-BL-End-Date]<[BL 0 F-Date],CONCATENATE("+(",IF(AND((WEEKDAY([BL 0 F-Date],2))<(WEEKDAY([New-BL-End-Date],2)),((WEEKDAY([New-BL-End-Date],2))-(WEEKDAY([BL 0 F-Date],2)))>1),(((DATEDIF([New-BL-End-Date],[BL 0 F-Date],"D")+1))-(FLOOR((DATEDIF([New-BL-End-Date],[BL 0 F-Date],"D")+1)/7,1)*2)-2),(((DATEDIF([New-BL-End-Date],[BL 0 F-Date],"D")+1))-(FLOOR((DATEDIF([New-BL-End-Date],[BL 0 F-Date],"D")+1)/7,1)*2))),")"),CONCATENATE("-(",IF(AND((WEEKDAY([New-BL-End-Date],2))<(WEEKDAY([BL 0 F-Date],2)),((WEEKDAY([BL 0 F-Date],2))-(WEEKDAY([New-BL-End-Date],2)))>1),(((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+1))-(FLOOR((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+1)/7,1)*2)-2),(((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+1))-(FLOOR((DATEDIF([BL 0 F-Date],[New-BL-End-Date],"D")+1)/7,1)*2))),")")))

    Let me know if it works out ok for you.

    Cheers

    Brian



    • Edited by bmcomp Thursday, February 23, 2012 3:40 PM
    Thursday, February 23, 2012 3:07 PM
  • Hi Brian

    I have checked this morning and I get the following error "The formula contains a syntax error or is not supported" Not very helpful I know. FYI I have amended your code to associate the new named Sharepoint fields because these changed last week, but this shouldn’t have impacted the general use of the code because I only changed the field names.

    Here is a copy of your code with the new field names FYI

    =IF([Rev_BL_End_Date]=[BL_End_Date],0,IF([Rev_BL_End_Date]<[BL_End_Date],CONCATENATE("+(",IF(AND((WEEKDAY([BL_End_Date],2))<(WEEKDAY([Rev_BL_End_Date],2)),((WEEKDAY([Rev_BL_End_Date],2))-(WEEKDAY([BL_End_Date],2)))>1),(((DATEDIF([Rev_BL_End_Date],[BL_End_Date],"D")+1))-(FLOOR((DATEDIF([Rev_BL_End_Date],[BL_End_Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Rev_BL_End_Date],[BL_End_Date],"D")+1))-(FLOOR((DATEDIF([Rev_BL_End_Date],[BL_End_Date],"D")+1)/7,1)*2))),")"),CONCATENATE("-(",IF(AND((WEEKDAY([Rev_BL_End_Date],2))<(WEEKDAY([BL_End_Date],2)),((WEEKDAY([BL_End_Date],2)) (WEEKDAY([Rev_BL_End_Date],2)))>1),(((DATEDIF([BL_End_Date],[Rev_BL_End_Date],"D")+1))-(FLOOR((DATEDIF([BL_End_Date],[Rev_BL_End_Date],"D")+1)/7,1)*2)-2),(((DATEDIF([BL_End_Date],[Rev_BL_End_Date],"D")+1))-(FLOOR((DATEDIF([BL_End_Date],[Rev_BL_End_Date],"D")+1)/7,1)*2))),")")))

    Information you may need to know to help you resolve the error:

    I thought it might be useful for you to know the definitions of the fields because this may provide you with any additionally knowledge/help you may need to A) Understanding the field terminology and B) to helping me achieve what I am trying to achieve.

    Date_Var = Date Variance

    BL_End_Date = Baseline end date

    Rev_BL_End_Date = Revised baseline end date of a given task

    If you need anything else let me know

    Monday, February 27, 2012 10:04 AM
  • Hi Michael,

    There seems to be a - missing in the code.  I have the corrected code below.

    =IF(Rev_BL_End_Date=BL_End_Date,0,IF(Rev_BL_End_Date<BL_End_Date,CONCATENATE("+(",IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2))),")"),CONCATENATE("-(",IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2))),")")))

    But I just realised that I haven't allowed for all situations when it could be 0.  I just have it if both dates are equal.  But that doesn't count for non working days.  I'll try include that into it and get back to you.  But try the above for the moment.

    Monday, February 27, 2012 10:40 AM
  •  

    Hi Brian

    Thanks for your swift reply. I think the - missing error was my personal copy & pasting abilities because when I look at your original post, the - it was included, my apologies.

    I couldn't copy and paste your last post of code because I can't copy & paste to the end of the movable bar. So I compared the differences and found the missing - from the code and added it manually, I hope there was no other code change :-) (I only found the missing minus) 

    So I'm now seeing a number in the [Date_Var] field which is a good start.

    Below are three scenarios I’ve tested and 1 out of the 3 provides the correct number in the [Date_Var] field based upon my expectation. I’ve described all three so you can see the outputs.

    Scenario 1 - Resolved

    The variances +/- days shown in the [Date_Var] field are correct if the variance days between [BL_END_Date] & [Rev_BL_End_Date] fall during a weekday.

    Scenario 2 - Problem 1

    I receive the following output if I test the output of the [Date_Var] over a weekend

    [BL_END_Date] is Mon - 30/4/2012

    [Rev_BL_End_Date] is Fri - 27/4/2012

    [Date_Var] = +2

    I would expect the answer in the [Date_Var] to be +1 because the task owner has said he will finish the work +1 working day earlier and not +2

    Scenario 3 - Problem 2

    I think you mentioned this scenario in your in the last sentence of your last post.

    Normally I would expect to have nothing (blank) data in the [Rev_BL_End_Date] field until it has been populated by the resource doing the work, meaning if field is blank then set [Date_Var] to 0 (zero) or if a date exists then show the +/- deviation

    At the moment the [Rev_BL_End_Date] field shows a +(Integer Format Number)

    If the [Rev_BL_End_Date] is blank, I would like the [Date_Var] to equal zero if that is possible.

    Cheers

    Mike

    Monday, February 27, 2012 1:52 PM
  • Hi Michael,

    I made no changes other than adding the - in.  I taught the code box might have made it easier for you to copy.

    Problem 1 - The result of the code to calculate the working days is inclusive of both dates.  So that is why you are getting +2 (the Friday & Monday).  It is the same if the days are during the week.

    Maybe you would prefer the result to be 1 day less in both situations?

    Problem 2 - This actually makes it easier for me, if you change the code at the very beginning from:

    =IF(Rev_BL_End_Date=BL_End_Date,0..............

    to

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0.....

    That should give you zero if you have no Rev date or both dates are the same.

    Monday, February 27, 2012 3:15 PM
  • Hi Brian

    Problem 1 - Yes could we show it as 1 day rather then 2 days if possible please

    Problem 2 - is working great!

    Cheers

    Michael

    Monday, February 27, 2012 3:33 PM
  • But you do realise that in scenario 1 the value will also be 1 less?

    Here is the updated code:

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,CONCATENATE("+(",IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,")"),CONCATENATE("-(",IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))-1,")")))

    Monday, February 27, 2012 3:52 PM
  • Hi Brian

    Are you advising that the man-days between Rev_BL_End_Date & BL_End_Date in Sce 1 will be incorrect if I use the new code?

    If so is there anyway round meeting all three scenario's so that the man days difference in all scenarios would be correct?

    Cheers

    Mike


    Monday, February 27, 2012 7:26 PM
  • I think the new code will be correct in all scenarios.  But at the moment between two week days it gives the number of days from BL to Rev_BL inclusive.  So Mon-Thur = 4.  Were the new code it will be = 3.  Which to me makes more sense as Mon +4 days = Fri but Rev_BL is Thur.  And of course this will also solve the issue of Fri-Mon as both days will not be counted.  Do you need the BL_End_Date to be counted?

    But this depends on what you want.  But I can't do both.  I'd imagine it would take a lot more code to try work it out!

    Monday, February 27, 2012 9:33 PM
  • Hi Brian

    I did a test this morning and I have two questions:

    • Would it be possible to remove the brackets from the numerical number value recieved in the Date_Var field? i.e (if positive/negative, just show the number only without the open and closed bracket)
    • Looking at the formula is the +/- symbol in the answer or is it a result of the calculation or is it being populated because of the concatenation in the formula? Preferably, I would like to the calculation to drive a positive or negative value and not see + symbol only, for example if the output is a positive number then show the number only and if it is negative then put the minus infront of the number -3 

    I would always want to see the - symbol in front of the negative values, but I am not to worried about the + symbol in front of the positive date variances. I am not sure if this can be achieved?

    Cheers

    Mike



    • Edited by Michael Gowlett Tuesday, February 28, 2012 1:19 PM Improved explaination
    Tuesday, February 28, 2012 12:58 PM
  • Hi Michael,

    Yes we can remove the brackets and we can just show it when negative.

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,CONCATENATE("-",IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))-1)))

    Cheers

    B

    Tuesday, February 28, 2012 3:09 PM
  • Hi Brian

    Ok that’s what I wanted to see, so in terms of code it does the trick, many thanks for that, your efforts have been tremendous, and I can't thank you enough.
     
    I have one small problem which you may or may not be able to help me with which is related to the final expectation of the output to your code and an additional function/field I am using in Sharepoint Designer, which you may or not be able to help me with. It has a relationship to your code, but I’m not sure how directly it’s related.

    In my previous posts I referred to the name of the field [Date_Var] as the field name that I was using to put your calculation into, when in actual fact the name of the field I copied your code into is called [NWF_Date_Var] which basically stands for "Non_Workflow_Field_Date_Variance"

    Now within Sharepoint I also have an additional field called [Date_Var] that uses a workflow to retrieve its values from the [NWF_Date_Var] field to the [Date_Var] field whenever a value appears/changes in the [NWF_Date_Var] field.
    Using your code the workflow works perfectly when the positive/negative numerical values appear in the [NWF_Date_Var] field.

    However when a negative value appears in the [NWF_Date_Var] field I.e. -12 (if the Rev_BL_End_Date is later than the BL_End_Date) then the workflow doesn't copy the negative values into the [Date_Var] field like I want it too.
    At first I thought it was because of the minus sign in the code, but I need to see a minus symbol, so I don’t want to change that.  If I manually put a minus (number) in the [Date_Var] field it will show as a minus, but it won't seem to copy the minus values from the correctly coded [NWF_Date_Var] field to the [Date_Var] field using the workflow! The only reason why I am using the [Date_Var] field is because I need to see a SUM at the top of the list which shows my SUM based upon both a positive and negative effects across the list data. FYI - I am led to believe that Sharepoint is unable to show SUMS from calculated fields so this is why I use a workflow to copy the values from one field [NWF_Date_Var] to a another  numerical field [Date_Var]

    I was wondering if in anyway Sharepoint was interpreting the minus values differently i.e. not as numerical values, rather then text, because this could be one of the reasons why the minus values aren’t copying across to the [Date_Var] field because this field has specifically been created as a numerical field to retrieve numerical values only, the output of workflow produces a zero 0 for minus values. Its strange thou because it copies the positive values across perfectly, Any thoughts?

    Cheers
    Mike

    Wednesday, February 29, 2012 8:39 AM
  • Ahh I see.  This shouldn't be a problem.  We can just multiply the result by -1 instead of the concat, which is resulting in a text value and is probably why its not being copied.  This should solve that.

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))*(-1)))

    Wednesday, February 29, 2012 9:07 AM
  • Hi Brian

    Yep that does the trick, I can see the minus value in my [NWF_Date_Var] field & my [Date_Var] field. but my testing example shown below now ersults with the incorrect minus days result for some reason.

    [BL_End_Date] = 30/4

    [Rev_BL_End_Date] = 16/5

    [NWF_Date_Var] = -13

    I believe it should read -12 when I do a manual calendar check, I believe it is counting the 30/4 as well, I don't think it should be...but who knows

    Any thoughts...if we get this one I think, you have nailed it :-)


    • Edited by Michael Gowlett Wednesday, February 29, 2012 9:53 AM additional info, to be helpful
    Wednesday, February 29, 2012 9:37 AM
  • Oops!  Deleted a little bit when i put in the * -1

    here you go.

    =IF(OR(Rev_BL_End_Date="",Rev_BL_End_Date=BL_End_Date),0,IF(Rev_BL_End_Date<BL_End_Date,IF(AND((WEEKDAY(BL_End_Date,2))<(WEEKDAY(Rev_BL_End_Date,2)),((WEEKDAY(Rev_BL_End_Date,2))-(WEEKDAY(BL_End_Date,2)))>1),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1))-(FLOOR((DATEDIF(Rev_BL_End_Date,BL_End_Date,"D")+1)/7,1)*2)))-1,(IF(AND((WEEKDAY(Rev_BL_End_Date,2))<(WEEKDAY(BL_End_Date,2)),((WEEKDAY(BL_End_Date,2))-(WEEKDAY(Rev_BL_End_Date,2)))>1),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)-2),(((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1))-(FLOOR((DATEDIF(BL_End_Date,Rev_BL_End_Date,"D")+1)/7,1)*2)))-1)*(-1)))

    • Proposed as answer by bmcomp Friday, March 02, 2012 5:54 PM
    • Marked as answer by Michael Gowlett Friday, March 02, 2012 7:06 PM
    Wednesday, February 29, 2012 11:04 AM
  • Hi Brian

    I am just going to do a test over the next 24 hours but I am confident that your last code entry provided me (above) with exactly what I needed to finalizes my original request. I will send you one final update tomorrow in the afternoon, if thats ok?..Once again thanks for your hard work, this really has helped me with the day to day running and automation of my project schedule performance.

    How do I commend you for your work and contribution, from within this site? In addtion to the votes I have already committed for your previous posts!

    Thanks

    Mike Gowlett

    Wednesday, February 29, 2012 5:12 PM
  • HI Mike,

    I hope it is going well for you.  Any problems let me know.

    All that is left to do is mark one or more of my comments you feel is the answer to your question(s).

    Cheers

    Brian

    Thursday, March 01, 2012 1:31 PM
  • Brian <u1:p></u1:p><o:p></o:p>

    Thanks very much for your help, patience, fast replies that were accurate, clear & concise.<u1:p></u1:p><o:p></o:p>

    On behalf of BM computers you resolved all of my escalated problems speedily and effectively...I must say I have never experienced that kind of service for something that I was sure not to find a resolution based upon all the other posts I saw within this site.<u1:p></u1:p><o:p></o:p>

    I would recommend BM-Computers for their IT solution services to anyone wishing to acquire fast and accurate IT advice inside and outside this forum. The finality of the last code provided exceeded my requirements. I commend Brian for his support & guidance.<u1:p></u1:p><o:p></o:p>

    Thanks<u1:p></u1:p><o:p></o:p>

    Mike Gowlett<u1:p></u1:p><o:p></o:p>

    Banking-Alliance.com

    Friday, March 02, 2012 7:24 PM