It is easy to get tripped up with dates in SharePoint. Dates are troublesome with any software. There are considerations and exceptions when handling leap years, varying number of days in each month and time zone differences. Date headaches are a given, but there are some approaches that will make life easier.

Add your own auto-updating "Current Date" field to a list

Invariably, you need a way to reference the current date in your SharePoint tools.

SharePoint doesn't provide the reference of "Today's Date" in calculated fields.

Using Infowise, you can do it yourself:

1. Create a "Date and Time" field named "Current Date".
   1. Set the format as "Date Only".
   2. Set the default value as "Today's date".
2. Create an Infowise Action with these settings:
   1. General Settings:
      1. Name: "Update Current Date"
      2. Run on events: "Timer-Based", Daily, Hour 12 AM.
   2. Advanced Settings: (Default)
   3. Action Settings:
      1. Values to set: "Current Date" = [Today]
      2. Items: ID = [ID]
   4. Conditions:
      1. ID always not equals 0. (Timer actions require at least one condition.)

Now your list can always reference the current date which will automatically change to the current date each day at midnight. You dashboards will be dynamic and your users will see that KPI Indicator Field automatically change to a Red Flag when the project is late.

Use calculated fields first

Whenever trying to do anything with dates, use calculated fields first. Think of calculated fields as variables that you can add to your list. You won't show these in your forms or list views. These are workers calculating date references for other user facing fields such as progress bars or KPI Flags on your dashboards.

If you haven't practiced with date calculations before, start practicing now! Try creating lots of practice calculations to get a better understanding. Think of this as SharePoint Dates 101. This is must have fundamental learning to have success working with dates. Try to work through these Date and time formulas examples:

https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx#Anchor_2

Break it into pieces

Calculated fields can reference other calculated fields. Rather than try to build a nasty piece of nested code that handles a long and complicated date calculation, break up the work into multiple calculated fields. Try writing your calculated field formulas in a text editor so that you can check your code carefully. Simply copy and paste your formulas to SharePoint.

Using Infowise Date Functions

Here's a handy list of all the functions available for date calculations in Infowise:

• Year number from a date: $Year()
• Month number from a date: $Month()
• Day number from a date: $Day()
• Day number of the week from a date: $Weekday()
• Week number in the year from a date: $WeekNumber()
• Hour number from a date: $Hour()
• Minute number from a date: $Minute()
• Date Time value of today's date at midnight: $Today()
• Add value to date (Choose Years, Months, Days, Hour, Minutes or Seconds): $AddDate()
• Convert a date related string to a date value (to assign to a date field): $ToDate()
• Get the difference in days between dates: $Days()
• Get the difference in hours between dates: $Hours()
• Get the difference in minutes between dates: $Minutes()
• Get the difference in seconds between dates: $Seconds()

Create a Reference List

Here's a novel approach. Add a list to your site to create date references for handy reference in your other SharePoint lists. You can do all the hard work in this list and create your own "date functions" that SharePoint does not provide! Here is a way to get First date of the current month, Last date of the current month, first date of the previous month and last date of the previous month.

1. Create a simple SharePoint custom list and add one field "RefDate" to store date values. (Format set to Date Only.)
2. Create four records titled "FirstDateThisMonth", "LastDateThisMonth", "FirstDateLastMonth" and "LastDateLastMonth".

1. Now we can add some Infowise Actions to automatically update these values: 
   1. For each action, create a Timer-based action that executes once a month on the first day of each month.
   2. Set the RefDate value for each action:
      1. First Date of this month: $ToDate($Month([Today])-1-$Year([Today]))
      2. Last Date of this month: $ToDate(($Month([Today])+1)-1-$Year([Today]))-1

• First Date of last month: $ToDate(($Month([Today])-1)-1-$Year([Today]))

1. Last Date of last month: $ToDate($Month([Today])-1-$Year([Today]))-1

Here is the pay off! Now that we can treat these references as functions in other lists.

For example, if you want to run a monthly report, you can make a reference from an action like this:

$GetValue(|Date-Reference|Title=FirstDateLastMonth|RefDate)

This function assumes that the Date-Reference list is in the current site. It selects the record by title and pulls back the date. You can use these references from all over SharePoint to consistently pull back these date values any time it is needed!

With the combined power of Calculated Fields and Infowise Functions there is no limit to what you can calculated for your Date Time fields. Setting up a Date Reference List allows you to create your own Date Time Functions allowing you to do the hard work only once and reference these values from throughout your SharePoint environment.

Do you have a cool approach that you have figured out? Do you have a nasty problem that you can't solve? Post a message and let us know!