You might know that Excel stores its date information as numbers starting at 1 on January 1st 1900. This makes manipulating date differences by subtraction very easy, shown below, where the difference between two dates is calculated.

And the unformatted values and formula are shown here.

Excel displays the results of date calculations as days by default and this works well when the numbers are relatively small. It’s easy to see that 7 days is a week and 10 days is about a week and a half. You might, however, think differently when the values get bigger or there are a lot of numbers to scan. Now you might need to start thinking in larger time units to make sense of them. Can you quickly calculate in your head how many weeks 111 days is? Or 473 days? You can? Good for you, but please keep reading.

I run into this problem quite frequently when creating reports of baseline vs. actual dates from project schedules and suchlike. I also like my audience to quickly understand what I’m telling them, so I wrote a function to display date differences more clearly. Paste the code below into a module in the VB IDE.

Function FormatYearWeekDay(theNumber As Integer) as String

   'Formats a date difference value as years, weeks and days
   'Assumes 365 days in a year

   Dim Yr As String
   Dim Wk As String
   Dim Dy As String
   Dim Neg as Boolean

   Yr = ""
   Wk = ""
   Dy = ""

  'Test if the number is zero
  If theNumber = 0 Then
     FormatYearWeekDay = "0d"
     Exit Function
  End If

  'Test if the number is positive or negative
  If theNumber < 0 Then
     Neg = True
  Else
     Neg = False
  End If

  'Setting the absolute value means we don't have to worry
  'about negative values until later
  theNumber = Abs(theNumber)

  'Deal with years
  If theNumber >= 365 Then
     Yr = Int(theNumber / 365) & "y "
     theNumber = theNumber Mod 365
  End If

  'Deal with weeks
  If theNumber >= 7 Then
     Wk = Int(theNumber / 7) & "w "
     theNumber = theNumber Mod 7
  End If

  'Deal with days
  Dy = theNumber & "d"

  'Set the function's return value
  If Neg Then
     FormatYearWeekDay = "-" & Yr & Wk & Dy
  Else
     FormatYearWeekDay = Yr & Wk & Dy
  End If

End Function

How it works

• Dimension some variables and set them to empty values
• Test if the number is zero, and if so set the return value to “0d” and exit the function – nothing more to do
• Set the Neg variable to TRUE or FALSE for later so we can use the absolute value of the number to avoid having to write additional code for negative numbers
• Get the absolute value of the number
• Test if the number is >= 365, and if so divide by 365 to give the number of years and store the result in a variable
• Add a ‘y’ indicator
• Use modular arithmetic to get the remaining days without the years
• Test if the number is >= 7, and if so divide by 7 to give the number of weeks and store the result in a variable
• Add a ‘w’ indicator
• Use modular arithmetic to get the remaining days without the weeks
• The remaining value must be the number of days left so store it in another variable
• Add a ‘d’ indicator
• Test if the original number was negative and if so replace the negative sign

This is what you get

I wondered if Excel had anything in-built to create the same functionality and I came across the DateDif function. I learned that this function is no longer supported; certainly not in Excel 2010 and I can’t vouch for earlier or later versions. You can see how it works here.

I also found the VBA function DateDiff (note the double ‘f’) which looks like it might be more useful. You can see how this one works here.

Things you can do

• How would you amend the function to cater for a 5 day working week?
• Explain the apparent anomaly in the second and third pairs of columns on rows 16 and 17 in the above screen shot
• Consider whether leap years would make any difference to the calculations
• Consider whether adding a category for months would be useful
• Examine how the ‘theNumber’ parameter in the function is altered under program control to achieve clear and concise coding
• Experiment with the DateDif and DateDiff functions in the example download and try to replicate and/or improve on my function’s results

Example download

• Working Week

Lessons learned

• Presentation of data to your audience is fundamentally important
• Dates can be manipulated using simple subtraction
• There is often in-built functionality in Excel, but does it always do what we want?

Over to you

Please leave a comment if you can think of any other uses for this functionality or improvements to the code. I am not a professional programmer and only do it to save me time in the long run. Or, occasionally, because I have no other choice. One day, if I can find the worksheets, I might tell you about the time I successfully used Monte Carlo simulation to create project costing estimates.