# Working Week

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
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
• 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
• 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
• 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