TOP

VBA function: Format

Description

The VBA Format function returns a string with a date or number in the specified format.


Format syntax

Format(value, format)

Example of VBA Format

Using the Format function to display numeric values in different formats:

Sub FormatExample1()

     '98.1%
     MsgBox Format(0.9814, "0.0%")

     '54'321.90
     MsgBox Format(54321.9, "##'##0.00")

     '54 321.90 €
     MsgBox Format(54321.9, "# ##0.00 €")

End Sub

Using the Format function to display a date in different formats:

Sub FormatExample2()
    
     myDate = #10/30/2020 3:35:45 PM#
    
     '30.10.20
     MsgBox Format(myDate, "dd/mm/yy")
    
     '30 October 2020
     MsgBox Format(myDate, "d mmmm yyyy")
    
     'Friday
     MsgBox Format(myDate, "dddd")
    
     '30/10/2020 15:35
     MsgBox Format(myDate, "dd/mm/yyyy hh:nn")
    
     'Friday 30 at 15h35
     MsgBox Format(myDate, "dddd d at h\hnn")

End Sub

Date Formats

Table of different date formats that can be used with the Format function (eg for the date 2021-01-08 09:05:07):

SymbolsExampleDescription
d 8 Day (from 1 to 31)
dd 08 Day (from 01 to 31)
ddd Fri Day of the week (abbreviated)
ddddFridayDay of the week
m 1 Month (from 1 to 12)
mm 01 Month (from 01 to 12)
mmm January Month (abbreviated)
mmmmJanuaryMonth
yy 21 Year (last 2 digits)
yyyy2021 Year
h 9 Hours (from 0 to 23)
hh 09 Hours (from 00 to 23)
n 5 Minutes (from 0 to 59)
nn 05 Minutes (from 00 to 59)
s 7 Seconds (0 to 59)
ss 07 Seconds (00 to 59)

The YLC Utilities menu in LO Calc

The YLC Utilities menu in LO Calc

The YLC Utilities menu in Excel

The YLC Utilities menu in Excel