0

I am trying to calculate the number of weeks between two dates. The first is manually entered by a user and called "First Available Date" the second is today's date that I bring in through Microsoft Flow on a daily basis and it is called "TodayDate". The First available date can be either a past date or future date and SharePoint isn't liking it too much. If I was doing this in Excel I would just do =((TodayDate-First Available Date)/7) and use the negative to show future availability.

This is the formula that I'm working with right now, but I keep getting a syntax error:

=IF(ISERROR(DATEDIF(TodayDate,[First Available Date],"d")<0,(DATEDIF([First Available Date],TodayDate,"d"),(DATEDIF(TodayDate,[First Available Date],"d"),-99,(DATEDIF(TodayDate,[First Available Date],"d")<0,(DATEDIF([First Available Date],TodayDate,"d"),(DATEDIF(TodayDate,[First Available Date],"d"))

Any help would be appreciated. thanks

  • check your expression, you forgot to close the expressions. – Arulraj Jul 31 '18 at 16:18
  • Can you give the conditions as a statement and sample ouput? – Arulraj Jul 31 '18 at 16:25
  • That's just a sample expression, I've been messing with it for a few days and not getting it to work. – RogB Jul 31 '18 at 16:27
  • We're trying to accomplish how many weeks an individual is available. so today's date - the first available date. The problem I was originally running into is the future dates providing a negative number, I would get totally wrong answers, but the past dates would be fine. for example =((7/31/18-4/1/18)/7) would result in 17 weeks available. if it was a future date =((7/31/18-12/1/18)/7) it should be around -18, but SP would return something totally wrong. – RogB Jul 31 '18 at 16:31
0

Try this solution. It will work.

=IF(ISERROR(DATEDIF([First Available Date],TodayDate,"d")),"-"&DATEDIF(TodayDate,[First Available Date],"d"),DATEDIF([First Available Date],TodayDate,"d"))/7

  • still getting a syntax error. I'm currently having it return as a number, just making sure that's not an issue. – RogB Jul 31 '18 at 18:10
  • This one I tested in my environment and working fine. Make sure you are not doing any human errors. – Arulraj Jul 31 '18 at 18:14
  • Whether TodayDate is custom field or you meant TODAY()? – Arulraj Jul 31 '18 at 18:16
  • TodayDate is brought in via Microsoft Flow and populates the current date daily. I'll check the syntax again. – RogB Jul 31 '18 at 18:20
  • Can you try with Created date instead of TodayDate? So you can locate the issue – Arulraj Jul 31 '18 at 18:24
0

I think I fixed your syntax errors but I can't be certain. I believe the =ISERROR snippet at the bottom of the page should execute the following logic:

if(Error) {
    return -99
}
else if( TodayDate - [First Available Date] < 0){ 
    return [First Available Date] - TodayDate 
} 
else{
    return TodayDate - [First Available Date]
}

If that's not what you're looking for, let me know. But I just fixed syntax in your provided logic (or attempted to without testing). I am unsure if TodayDate is supposed to be [TodayDate]

With TodayDate as-is: =ISERROR(IF(DATEDIF(TodayDate,[First Available Date],"d")<0,DATEDIF([First Available Date],TodayDate,"d"),DATEDIF(TodayDate,[First Available Date],"d")),-99,(IF(DATEDIF(TodayDate,[First Available Date],"d")<0,DATEDIF([First Available Date],TodayDate,"d"),DATEDIF(TodayDate,[First Available Date],"d"))))

With TodayDate as [TodayDate]:

=ISERROR(IF(DATEDIF([TodayDate],[First Available Date],"d")<0,DATEDIF([First Available Date],[TodayDate],"d"),DATEDIF([TodayDate],[First Available Date],"d")),-99,(IF(DATEDIF([TodayDate],[First Available Date],"d")<0,DATEDIF([First Available Date],[TodayDate],"d"),DATEDIF([TodayDate],[First Available Date],"d"))))

  • thanks, I'm not sure why TodayDate is coming without the brackets, maybe because its Microsoft Flow. I'm trying it out now but still getting a syntax error. This is what I put in the calculated column so far. =if(Error){return -99} else if(TodayDate - [First Available Date] < 0){ return [First Available Date] - TodayDate} else{return TodayDate - [First Available Date]} – RogB Jul 31 '18 at 17:28
  • @RogB Sorry the if-else statement was to describe what what supposed to happen... Check out the =ISERROR section below that. – KGlasier Jul 31 '18 at 17:34
  • thanks for the clarification. I'm still getting a syntax error. I'm running on SharePoint 2010 if that makes a difference. – RogB Jul 31 '18 at 17:51
  • I'll take a look again and see what I come up with. May take a bit. – KGlasier Jul 31 '18 at 18:02
  • any help is appreciated, thank you for all your input so far. – RogB Jul 31 '18 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.