Calculating the Number of Days Between Dates

Suppose you want to know how long it took to ship an item. That is, you need to calculate the number of days between the date an item was ordered and when it was shipped. Calculating the number of days between dates is easy in Stonefield Query: just create a formula that subtracts the two date fields, such as:

ShippedDate – OrderDate

However, there may be a few complications with this.

Handling null dates

What should the number of days be if the item hasn’t shipped yet? If ShippedDate is null (that is, an unknown value), ShippedDate – OrderDate is also null, which displays as blank in Stonefield Query. If that’s what you want, great. If not, adjust the formula to display a special value in that case, such as:

IIF(ISNULL(ShippedDate), DATE(), ShippedDate) – OrderDate

which displays the number of days between the order date and today if the item hasn’t shipped, or:

IIF(ISNULL(ShippedDate), -1000, ShippedDate – OrderDate)

which displays –1000.

Note that you can’t use something like:

IIF(ISNULL(ShippedDate), "Not shipped", ShippedDate – OrderDate)

because the formula is supposed to be numeric but “Not shipped” isn’t numeric.

Handling weekends

Do you ship on weekends? If not, the number of days it took to ship an item may be overstated because the formula counts days even when you’re not open. For example, if something was ordered on Friday and shipped on Monday, the difference is three days when it should really be one.

In that case, you need a more complicated formula, one that subtracts weekend days. To do that, use an expression of:

WeekendSpan(OrderDate, ShippedDate)

After you tab out of the Expression textbox in the Formula Editor, Stonefield Query tells you that a function named WeekendSpan can’t be found and asks if you’d like to create it. Choose Yes and paste the following code into the code editor window that appears:

function WeekendSpan(tdStart, tdEnd)
local lnWeekendDays, lnI, ldDate, lnDay, lnSpan
lnWeekendDays = 0
for lnI = 1 to tdEnd - tdStart
      ldDate = tdStart + lnI
      lnDay  = dow(ldDate)
      if lnDay = 1 or lnDay = 7
            lnWeekendDays = lnWeekendDays + 1
      endif
next
lnSpan = tdEnd - tdStart - lnWeekendDays
return lnSpan

This code assumes you’re closed on Saturday and Sunday; the DOW function gives the day of the week for the specified date, which is 7 for Saturday and 1 for Sunday. Adjust the code as necessary if, say, you’re closed on Friday and Saturday and open on Sunday.

Handling holidays

This code doesn’t account for holidays, such as December 25 or January 1. To check for dates like that, change the code to:

function WeekendSpan(tdStart, tdEnd)
local lnWeekendDays, lnI, ldDate, lnDay, lnSpan
lnWeekendDays = 0
for lnI = 1 to tdEnd - tdStart
    ldDate = tdStart + lnI
    lnDay  = dow(ldDate)
    if lnDay = 1 or lnDay = 7 or IsHoliday(ldDate)
        lnWeekendDays = lnWeekendDays + 1
    endif
next
lnSpan = tdEnd - tdStart - lnWeekendDays
return lnSpan

function IsHoliday(tdDate)
local laHolidays[2], llHoliday, lnI
laHolidays[1] = date(2015, 12, 25)
laHolidays[2] = date(2016, 1, 1)
llHoliday     = .F.
for lnI = 1 to alen(laHolidays)
    if month(laHolidays[lnI]) = month(tdDate) and ;
        day(laHolidays[lnI]) = day(tdDate)
        llHoliday = .T.
        exit
    endif
next
return llHoliday

(New code is shown in bold.)

Note that you have to dimension the laHolidays array to the number of holidays and set each element in the array to the appropriate date (don’t worry about the year part of the date; the code only checks month and day) as this example code does.

If you need to handle holidays that aren’t on fixed days, such as Thanksgiving in the U.S., you need to code for that specifically, such as:

if lnDay = 1 or lnDay = 7 or IsHoliday(ldDate) or ;
    ldDate = DATE(2015, 11, 26)

Conclusion

As you can see, date math can be very simple or it can be more complicated, depending on your needs. However, it’s good to know that Stonefield Query can handle even the most complex date calculations you need.