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. Commented Jul 31, 2018 at 16:18
  • Can you give the conditions as a statement and sample ouput? 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. 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. 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. 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. Commented Jul 31, 2018 at 18:14
  • Whether TodayDate is custom field or you meant TODAY()? 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. Commented Jul 31, 2018 at 18:20
  • Can you try with Created date instead of TodayDate? So you can locate the issue 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]} 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. 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. Commented Jul 31, 2018 at 17:51
  • I'll take a look again and see what I come up with. May take a bit. Commented Jul 31, 2018 at 18:02
  • any help is appreciated, thank you for all your input so far. Commented Jul 31, 2018 at 18:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.