Erlandsen Data ConsultingCalculating 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 [...]
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) =AddWorkDays(A1,15) =DateIsHoliday(A1) The date input cells must contain valid Excel dates, or formulas/functions that return [...]
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 [...]
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 = [...]
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 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 : [...]
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 [...]