VBA Syntax & Functions in Excel

VBA Date Manipulation: Syntax & Functions Explained

14 min read|Last Updated: April 2nd, 2024|Categories: excel|

The Date data type is one of the most commonly used data types in VBA that enables us to manipulate dates and times. In this blog post, we will explore the ‘Date’ data type in VBA and its syntax. We also introduce VBA functions that developers use to manipulate date and time in VBA.


How Date and Time are Stored and Displayed in Excel

 In Excel, dates and times are stored as integers formatted to look like dates. Dates in Excel are just the number of days since January 0, 1900, formatted to look like a date. Times are stored internally as numbers between 0 and 1. 

how date time is stored in excel

As you can see in the gif above, 0.25 is represented as  6:00 (quarter through the day). If you enter times as 6:00, Excel displays this time as 6:00:00 AM in the formula bar. AM is used for times in the night and morning and PM is used for times in the afternoon and evening.

When we enter a number in a cell, Excel assumes we want to use a general number format, which means no number format will be applied. If we type in a value, so it looks like a date or time, Excel automatically applies the default date and time format based on the regional date and time settings that are specified in Control Panel (It is important to choose a date format that Excel recognizes). It changes when we adjust the date and time settings in Control Panel.