# Calculate Date Differences

Calculating the difference between two dates is rather easy, just subtract one date from the other to return the count of days between the two dates. If you add a number to a date, you will get a new date that is the number of days into the future from the initial date. You can also subtract a number form a date to get a previous date.

Another option is to use the worksheet function DATEDIF that can calculate the difference between two dates in a variety of different intervals, such as the count of years, months, or days between two dates.

The syntax for the DATEDIF-function is: =DATEDIF(Date1;Date2;Interval)
The Norwegian syntax for the function is =DATODIFF(Date1;Date2;Interval)

Where Date1 is the first date, Date2 is the second date and Interval is the interval type to return.
If Date1 is later than Date2, the function will return a #NUM! error.
If Date1 or Date2 is not a valid date, the function will return a #VALUE error.

The Interval argument can be one of these string values:

 Interval: Function Return Value: d Count of days between the two dates md Count of days between the two dates if they were in the same month and year yd Count of days between the two dates if they were in the same year m Count of months between the two dates ym Count of months between the two dates if they were in the same year y Count of years between the two dates

If the Interval argument is not one of the items listed in above, the function will return a #NUM error.

To calculate the number of years, months, and days between two dates, you can use a formula like this, where cell A1 contains the start date and cell B1 contains the end date:
=DATEDIF(A1;B1;”y”)&” years “&DATEDIF(A1;B1;”ym”)&” months “&DATEDIF(A1;B1;”md”) &” days”

This formula will return a string result like: 10 years 6 months 21 days

The DATEDIF function has not been documented much in the Excel help files by Microsoft, probably because the function was only implemented to make Excel compatible with their initial main spreadsheet rival Lotus 1-2-3.
The DATEDIF function was supposedly documented in the help file for Excel 2000. In more recent days the function was documented in the Excel for Mac online function reference.

The curious thing is that the DATEDIF function is also used and documented in Windows SharePoint 2003, 2007 and 2010.

### Calculating Date Differences in VBA:

In VBA you can calculate date differences use a similar function, the DateDiff-function:

```Sub ExamplesUsingDateDiffFunction()
Dim lngResult As Long, lngStartDate As Long, lngEndDate As Long
lngStartDate = DateSerial(2000, 1, 1)
lngEndDate = Date ' today
lngResult = DateDiff("yyyy", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays)
Debug.Print lngResult & " years"
lngResult = DateDiff("q", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays)
Debug.Print lngResult & " quarters"
lngResult = DateDiff("m", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays)
Debug.Print lngResult & " months"
lngResult = DateDiff("w", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays)
Debug.Print lngResult & " weeks (days)"
lngResult = DateDiff("ww", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays)
Debug.Print lngResult & " weeks (calendar)"
lngResult = DateDiff("d", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays)
Debug.Print lngResult & " days" ' y returns the same
End Sub
```

# Calculate workdays

With the custom functions below you can calculate the count of workdays between two dates. Workdays includes normal weekdays except Saturdays and Sundays or holidays e.g. like Easter).

The functions can be used like this in a worksheet cell:

```=CountWorkDays(A1,B1)
=DateIsHoliday(A1)
```

The date input cells must contain valid Excel dates, or formulas/functions that return dates, e.g. =TODAY().

```Function CountWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the workday count between two dates
Dim d As Long, dCount As Long
dCount = 0
If StartDate < 1 Or EndDate < 1 Then Exit Function

If StartDate <= EndDate Then
For d = StartDate To EndDate
If Not DateIsHoliday(d) Then
dCount = dCount + 1
End If
Next d
Else
For d = StartDate To EndDate Step -1
If Not DateIsHoliday(d) Then
dCount = dCount + 1
End If
Next d
End If
CountWorkDays = dCount
End Function

Function AddWorkDays(StartDate As Long, Offset As Long) As Long
' returns a date Offset days from StartDate
Dim d As Long, dCount As Long
If StartDate < 1 Then Exit Function
d = StartDate
If Abs(Offset) > 0 Then
dCount = 0
If Offset > 0 Then
Do
If Not DateIsHoliday(d) Then
dCount = dCount + 1
End If
d = d + 1
Loop Until dCount = Offset
d = d - 1
Else
Do
If Not DateIsHoliday(d) Then
dCount = dCount - 1
End If
d = d - 1
Loop Until dCount = Offset
d = d + 1
End If
End If
End Function

Function DateIsHoliday(InputDate As Long) As Boolean
' returns True if InputDate is a Saturday/Sunday or a holiday (Norwegian)
Dim d As Integer, intYear As Integer, lngEasterSunday As Long, OK As Boolean
OK = False
If InputDate > 0 Then
If Weekday(InputDate, vbMonday) >= 6 Then ' Saturday or Sunday
OK = True
End If
If Not OK Then ' check if InputDate is a holiday
intYear = Year(InputDate)
d = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21
lngEasterSunday = DateSerial(intYear, 3, 1) + d + (d > 48) + 6 - ((intYear + intYear \ 4 + d + (d > 48) + 1) Mod 7)
OK = True
Select Case InputDate
Case CDate("1.1." & intYear) ' 1. January
'Case lngEasterSunday - 4 ' Wednesday before Easter
Case lngEasterSunday - 3 ' Thursday before Easter
Case lngEasterSunday - 2 ' Friday before Easter
Case lngEasterSunday ' Easter Sunday
Case lngEasterSunday + 1 ' Monday after Easter
Case CDate("1.5." & intYear) ' 1. May
Case CDate("17.5." & intYear) ' 17. May
Case lngEasterSunday + 39 ' Ascension Day
'Case lngEasterSunday + 48 ' Pentecost
Case lngEasterSunday + 49 ' Pentecost
Case lngEasterSunday + 50 ' Pentecost
'Case CDate("24.12." & intYear) ' Christmas
Case CDate("25.12." & intYear) ' Christmas
Case CDate("26.12." & intYear) ' Christmas
'Case CDate("31.12." & intYear) ' New Years Eve
Case Else
OK = False
End Select
End If
End If
DateIsHoliday = OK
End Function
```

# Calculate a weeks start date

With the custom function below you can calculate the start date for any given week:

```Function WeekStartDate(intWeek As Integer, intYear As Integer) As Date
Dim FromDate As Date, lngAdd As Long
If intYear < 1 Then intYear = Year(Date) ' the current year
FromDate = DateSerial(intYear, 1, 1)
If Weekday(FromDate, vbMonday) > 4 Then lngAdd = 7
WeekStartDate = FromDate + ((7 * intWeek) - 6) - _
End Function
```

# Calculate Holidays

If you want to calculate the correct date for Easter Sunday between the years 1900 and 2078 without depending on any Excel built-in worksheet functions you can use the function below:

```Function EasterSunday(InputYear As Integer) As Long
' Returns the date for Easter Sunday, does not depend on Excel
Dim d As Integer
d = (((255 - 11 * (InputYear Mod 19)) - 21) Mod 30) + 21
EasterSunday = DateSerial(InputYear, 3, 1) + d + (d > 48) + 6 - ((InputYear + InputYear \ 4 + d + (d > 48) + 1) Mod 7)
End Function
```

When you have this date you are also able to calculate other variable dates regarding Easter etc.

### Calculate the date for Easter Sunday with a worksheet formula

Hans W. Herber held a competition on his website http://www.herber.de to find the shortest worksheetfunction that could calculate the date for Easter Sunday between the years 1900 and 2078. The competition ended March 31st. 1999.
The example formulas below assumes that cell A1 contains the year the formula uses to return the date for Easter Sunday.

The winning formula (in English):

```=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34
(submitted by Norbert Hetterich from Germany).
```

If you translate the winning formula to Norwegian, this is the result:
=AVRUND.GJELDENDE.MULTIPLUM.NED(DAG(MINUTT(A1/38)/2+56)&”/5/”&A1;7)-34

The first runner up is this formula (in English):
=DOLLAR((DAY(MINUTE(A1/38)/2+55)&”.4.”&A1)/7,)*7-6
(submitted by Thomas Jansen also from Germany).

The first runner up formula translates better to Norwegian:
=VALUTA((DAG(MINUTT(A1/38)/2+55)&”.4.”&A1)/7;)*7-6

### Calculate holidays

The function below will return True if the input date is a defined holiday.
You’ll have to customize the function to include the specific holidays for your country.

```Function DateIsHoliday(InputDate As Long) As Boolean
' returns True if InputDate is a Saturday/Sunday or a holiday (Norwegian)
Dim d As Integer, intYear As Integer, lngEasterSunday As Long, OK As Boolean
OK = False
If InputDate > 0 Then
If Weekday(InputDate, vbMonday) >= 6 Then ' Saturday or Sunday
OK = True
End If
If Not OK Then ' check if InputDate is a holiday
intYear = Year(InputDate)
d = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21
lngEasterSunday = DateSerial(intYear, 3, 1) + d + (d > 48) + 6 - ((intYear + intYear \ 4 + d + (d > 48) + 1) Mod 7)
OK = True
Select Case InputDate
Case CDate("1.1." & intYear) ' 1. January
'Case lngEasterSunday - 4 ' Wednesday before Easter
Case lngEasterSunday - 3 ' Thursday before Easter
Case lngEasterSunday - 2 ' Friday before Easter
Case lngEasterSunday ' Easter Sunday
Case lngEasterSunday + 1 ' Monday after Easter
Case CDate("1.5." & intYear) ' 1. May
Case CDate("17.5." & intYear) ' 17. May
Case lngEasterSunday + 39 ' Ascension Day
'Case lngEasterSunday + 48 ' Pentecost
Case lngEasterSunday + 49 ' Pentecost
Case lngEasterSunday + 50 ' Pentecost
'Case CDate("24.12." & intYear) ' Christmas
Case CDate("25.12." & intYear) ' Christmas
Case CDate("26.12." & intYear) ' Christmas
'Case CDate("31.12." & intYear) ' New Years Eve
Case Else
OK = False
End Select
End If
End If
DateIsHoliday = OK
End Function
```

# Weeknumbers

The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function below will calculate the correct week number depending on the national language settings on your computer.

```Function UDFWeekNum(InputDate As Date)
UDFWeekNum = DatePart("ww", InputDate, vbUseSystemDayOfWeek, vbUseSystem)
End Function```

The function above can also be modified to calculate the weeknumber the European way:

```Function UDFWeekNumISO(InputDate As Date)
UDFWeekNumISO = DatePart("ww", InputDate, vbMonday, vbFirstFourDays)
End Function```

The two functions above can, due to a bug, return a wrong week number. This occurs for dates around New Year for some years, e.g. the years 1907, 1919, 1991, 2003, 2007, 2019 and 2091.
You can use this worksheet formula to calculate the correct week number:

```=INT((A1-(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))-3+
MOD(WEEKDAY(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))+1,7))/7)+1
```

The formula above assumes that cell A1 contains a valid date for which you want to return the week number. To calculate the correct week number with a user-defined VBA function, you can use the function below:

```Function WEEKNR(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
WEEKNR = 0
If InputDate < 1 Then Exit Function
A = Weekday(InputDate, vbSunday)
B = Year(InputDate + ((8 - A) Mod 7) - 3)
C = DateSerial(B, 1, 1)
D = (Weekday(C, vbSunday) + 1) Mod 7
WEEKNR = Int((InputDate - C - 3 + D) / 7) + 1
End Function
```

# Calculate with dates

The TODAY()-function returns a serial number for the current date, as long as the computer date and time is set correctly. The serial number is a long integer counting the number of days since 1. January 1900 (serialnumber=1). The cell containing the function must be formatted to display numbers as dates.

#### Examples :

 Formula : Returns : =TODAY() The date today =TODAY()+15 A date 15 days later than today =TODAY()-7 A date 7 days earlier than today

Excel calculates dates including leap years.

If you enter two dates in two different cells, you can perform a calculation with the two dates.
If cell A1 contains the date 24.12.95 and cell B1 contains the function =TODAY(), you can enter the formula =A1-B1 in cell C1 to calculate the number of days until Christmas. Cell C1 must be formatted to display numbers, not dates.

You can enter a date in a cell by using the following separator signs between days, months and years:
a slash (/), a dot (.) or a hyphen (-). The date will be displayed with the preferred date format set in your computers international settings, or with the date format you select in Excel.
If you omit the year when you enter a date, Excel assumes that you want a date in the current year.

# Date calculation examples

The table below shows some useful example formulas for date calculations. The examples assumes that cell A1 contains a valid date, and uses a semicolon (;) as list separator, replace with comma (,) if necessary.

Description Function/Formula
Today’s date =TODAY()
A date 14 days from today =TODAY()+14
Return the current day
1=Monday, 7=Sunday
=WEEKDAY(TODAY();2)
Return the current day =DAY(TODAY())
Return the current month =MONTH(TODAY())
Return the current year  =YEAR(TODAY())
The date for Monday in the previous week =TODAY()-WEEKDAY(TODAY();2)-6
The date for Monday in the current week =TODAY()-WEEKDAY(TODAY();2)+1
The date for Monday in the next week =TODAY()-WEEKDAY(TODAY();2)+8
Create a valid date =DATE(2002;12;24)
Create a valid date =DATEVALUE("1.1.1980")
Count of days in a month =DAY(DATE(YEAR(A1);MONTH(A1)+1;0))
Last date in a month =DATE(YEAR(A1);MONTH(A1)+1;0)
Determine which quarter a date belongs to =CHOOSE(MONTH(A1);1;1;1;2;2;2;3;3;3;4;4;4)