Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 37 ยท Language Basics

Math and Date Functions in VBA

Use VBA math functions (Abs, Int, Round, Sqr, Rnd) and date functions (Now, DateAdd, DateDiff, DatePart, Weekday, DateSerial) with practical examples.

VBA has a compact set of built-in math and date functions. Anything missing, such as sine tables or statistics, can be reached with WorksheetFunction in Excel (covered later). This lesson covers the built-ins you will use daily.

Math functions

Function What it does Example Result
Abs(x) absolute value Abs(-4.5) 4.5
Int(x) rounds down Int(-3.2) -4
Fix(x) drops the fraction Fix(-3.2) -3
Round(x, n) rounds to n places (banker's) Round(2.5) 2
Sqr(x) square root Sqr(81) 9
Sgn(x) sign (-1, 0, 1) Sgn(-8) -1
Exp, Log e^x and natural log Log(1) 0
Sin, Cos, Tan, Atn trigonometry in radians Atn(1) * 4 pi

VBA has no Max, Min, Power or Pi function. Use ^, write your own or call Application.WorksheetFunction.Max(...).

Round uses banker's rounding (halves go to the even number), so Round(0.5) is 0 and Round(1.5) is 2. For "school" rounding of money use Int(x * 100 + 0.5) / 100, or WorksheetFunction.Round, which rounds half up.

VB
Sub MathDemo()
    Debug.Print Int(7.8), Fix(-7.8), Round(3.14159, 2)    ' 7  -7  3.14
    Debug.Print Sqr(144), 2 ^ 8, Abs(-12)                  ' 12  256  12
End Sub

Random numbers

Rnd returns a Double from 0 (inclusive) to 1 (exclusive). Call Randomize once to seed it from the clock, otherwise every run gives the same sequence:

VB
Randomize
Dim dice As Long
dice = Int(Rnd * 6) + 1       ' 1 to 6

General formula for a whole number between lo and hi: Int((hi - lo + 1) * Rnd + lo).

Date and time basics

VBA stores a date as a number: the integer part counts days (Excel's day 1 is 1900-01-01 in worksheets, VBA's day 0 is 1899-12-30), the fraction is the time of day. That is why you can add 1 to a date to get tomorrow.

VB
Sub DateDemo()
    Debug.Print Date          ' today, e.g. 11/12/2026
    Debug.Print Time          ' current time
    Debug.Print Now           ' date and time
    Debug.Print Date + 7      ' a week from now
    Debug.Print Timer         ' seconds since midnight (good for timing code)
End Sub

Building and splitting dates

VB
Dim d As Date
d = DateSerial(2026, 3, 15)           ' year, month, day
Debug.Print Year(d), Month(d), Day(d) ' 2026  3  15
Debug.Print DateSerial(2026, 14, 1)   ' month overflow rolls into 2027-02-01
Debug.Print DateSerial(2026, 3, 0)    ' day 0 = last day of previous month: 2/28/2026
Debug.Print TimeSerial(14, 30, 0)     ' 2:30:00 PM
Debug.Print CDate("2026-03-15")       ' text to date (depends on locale)

DateSerial(y, m + 1, 0) is the classic trick to find the last day of a month.

DateAdd, DateDiff, DatePart

The first argument is the interval: "yyyy", "q", "m", "d", "ww" (week), "h", "n" (minutes), "s".

VB
Dim start As Date: start = #3/15/2026#
Debug.Print DateAdd("m", 1, start)                 ' 4/15/2026
Debug.Print DateAdd("d", -10, start)               ' 3/5/2026
Debug.Print DateDiff("d", start, #12/25/2026#)     ' 285
Debug.Print DatePart("q", start)                   ' 1

DateDiff counts boundaries crossed, not full intervals: DateDiff("yyyy", #12/31/2026#, #1/1/2027#) is 1 even though one day passed.

Weekdays and names

VB
Debug.Print Weekday(#3/15/2026#)                    ' 1 = Sunday (default)
Debug.Print Weekday(#3/15/2026#, vbMonday)          ' 7, Monday-based
Debug.Print WeekdayName(Weekday(#3/15/2026#), True) ' Sun
Debug.Print MonthName(3)                            ' March
Debug.Print Format(#3/15/2026#, "dddd, d mmmm yyyy") ' Sunday, 15 March 2026

March 15, 2026 is a Sunday.

Checking before converting

IsDate("31/02/2026") tells you whether text can become a date; IsNumeric does the same for numbers. Dates typed as text are interpreted using the user's regional settings, so prefer DateSerial or ISO strings you build yourself.

Stand-in: date arithmetic in JavaScript

This JavaScript stand-in (not VBA) mirrors DateSerial, DateAdd and DateDiff so you can verify the end-of-month trick.

JS Stand-in: DateSerial and DateDiff

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Int(-3.2) return?

    Int rounds down toward negative infinity. Fix would return -3.
  2. 2. What does Round(2.5) return in VBA?

    VBA's Round uses banker's rounding: halves go to the nearest even number.
  3. 3. Which call gives the last day of March 2026?

    Day 0 of April is the day before April 1, i.e. March 31.
  4. 4. Why call Randomize?

    Without Randomize, Rnd repeats the same sequence at every start.

Summary

  • Math basics: Abs, Int, Fix, Round (banker's), Sqr, Rnd with Randomize.
  • Dates are numbers: add days with +, build with DateSerial, change with DateAdd, compare with DateDiff.
  • Weekday, MonthName, Format produce readable output.

Next: Arrays