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

Improve this question
4
  • check your expression, you forgot to close the expressions.
    – Arulraj
    Commented Jul 31, 2018 at 16:18
  • Can you give the conditions as a statement and sample ouput?
    – Arulraj
    Commented Jul 31, 2018 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
    Commented Jul 31, 2018 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
    Commented Jul 31, 2018 at 16:31

2 Answers 2

Reset to default
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

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

Improve this answer
5
  • 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
    Commented Jul 31, 2018 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
    Commented Jul 31, 2018 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
    Commented Jul 31, 2018 at 17:51
  • I'll take a look again and see what I come up with. May take a bit.
    – KGlasier
    Commented Jul 31, 2018 at 18:02
  • any help is appreciated, thank you for all your input so far.
    – RogB
    Commented Jul 31, 2018 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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