# Help Calculating Dates

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. Jul 31, 2018 at 16:18
• Can you give the conditions as a statement and sample ouput? 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
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
Jul 31, 2018 at 16:31

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, 2018 at 18:10
• This one I tested in my environment and working fine. Make sure you are not doing any human errors. Jul 31, 2018 at 18:14
• Whether `TodayDate` is custom field or you meant `TODAY()`? 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
Jul 31, 2018 at 18:20
• Can you try with `Created` date instead of `TodayDate`? So you can locate the issue Jul 31, 2018 at 18:24

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{
}
``````

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