I have a form that a user is filling out by using SharePoint Columns. There are a varied amount of dates that they get to choose from. The form will take a start date and an end date and count the total number of days between them.

For example Start Date: 4/2/18 End Date: 4/9/18 is a total of 8 days.

If I use the following code in SP 2016:=DATEDIF([Start Date],[End Date]+1,"D")

The formula will work. However, it adds 1 whenever there are no dates entered. I need to account for all the zeros/blanks and create a report out of that. So I need to see the blanks/zeros.

What formula can I use that just counts the days without having to add 1 to include the date?

I've tried: =DAYS(End Date,Start Date) and this returns #NUM. Can anyone help?

  • hi please what if you want the end date to be automatically populated based on the day input is filled in..for example.End date will be generated automatically – olops Dec 20 '19 at 23:46

Try using an IF Function to check if the Start Date has a value before doing your calculation. This way you can return no value if there are no dates selected.

It should look something like this:

=IF(ISBLANK([Start Date]),"",DATEDIF([Start Date],[End Date]+1,"D"))

Additionally, you can add another ISBLANK check for the End Date so that it will only calculate if both Start and End Dates have been selected. That would look something like this:

=IF(ISBLANK([Start Date]),"",IF(ISBLANK([End Date]),"",DATEDIF([Start Date],[End Date]+1,"D")))

| improve this answer | |
  • Thank you Vanblart! I've tried your first suggestion: =IF(ISBLANK([Start Date]),"",DATEDIF([Start Date],[End Date]+1,"D")) and it works well. – Sun Apr 3 '18 at 16:05
  • I confirm that I now tried both solutions and they work well. thanks again. – Sun Apr 3 '18 at 16:29

Please try use the following formula, if either any date is blank will return blank

=IF(OR(date1="",date2=""),"",DATEDIF(date1,date2 +1,"d"))
| improve this answer | |
  • A. Saeed, since you took the time out of your day to respond to my question. I tried your solution out as well and it worked. Thank you! – Sun Apr 3 '18 at 16:24
  • @Sun you're welcome , glad to help :) , please mark either solutions which works for you as an answer so your question be marked as answered. – A.Saéed Apr 3 '18 at 16:28
  • How do I do that? – Sun Apr 3 '18 at 16:32
  • @Sun select the check mark beside the answer , only one answer can be marked – A.Saéed Apr 3 '18 at 16:33
  • A. Saeed, can you help me calculate the average? I posted another question where I used your logic. But I think I'm doing something wrong. – Sun Apr 5 '18 at 20:40

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.