{"id":2895,"date":"2017-10-11T01:17:10","date_gmt":"2017-10-11T01:17:10","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=2895"},"modified":"2020-04-23T10:52:16","modified_gmt":"2020-04-23T10:52:16","slug":"excel-vba-lesson-11-date-time-functions","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-date-time-functions\/","title":{"rendered":"Excel VBA Lesson 11: Date and Time Functions"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-10-financial-functions\/\">&lt;&lt;Lesson 10&gt;&gt;<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\">[Contents]<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-12-if-then-else\/\">&lt;&lt;Lesson 12&gt;&gt;<\/a><\/strong><\/h4>\n\n\n\n<p>Excel VBA provides various built-in date and time functions that allow us to write VBA codes involving dates and times. We can use date and time functions to display system date and time, add and subtract data and time, converting a string to date and more.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">11.1 Date Functions<\/h3>\n\n\n\n<p>The date functions are explained in Table 11.1.<br>\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Table 11.1 Date and Time Functions<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th>Function<\/th><th>Description<\/th><\/tr><tr><td>Now<\/td><td>returns current system date and time<\/td><\/tr><tr><td>Date<\/td><td>returns current system date<\/td><\/tr><tr><td>Day(Date)<\/td><td>Returns the day of the month for the date specified in the argument<\/td><\/tr><tr><td>Weekday(Date)<\/td><td>Returns weekday as an integer for the date specified in the argument<\/td><\/tr><tr><td>WeekdayName(Weekday(Date))<\/td><td>Returns the name of weekday for the date specified in the argument<\/td><\/tr><tr><td>WeekdayName(Weekday(Date), True)<\/td><td>Returns the abbreviated name of weekday for the date specified in the argument<\/td><\/tr><tr><td>Month(Date)<\/td><td>Returns the month of the year in integer for the date specified in the argument<\/td><\/tr><tr><td>MonthName(Month(Date))<\/td><td>Returns the name of month of the year for the date specified in the argument<\/td><\/tr><tr><td>MonthName(Month(Date))<\/td><td>Returns the abbreviated name of month of the year for the date specified in the argument<\/td><\/tr><tr><td>Year(Date)<\/td><td>Returns the year in integer for the date specified in the argument<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Example 11.1<\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>Cells(1, 2) = Now<br>\nCells(2, 2) = Date<br>\nCells(3, 2) = Day(Date)<br>\nCells(4, 2) = Weekday(Date)<br>\nCells(5, 2) = WeekdayName(Weekday(Date))<br>\nCells(6, 2) = WeekdayName(Weekday(Date), &#8220;true&#8221;)<br>\nCells(7, 2) = Month(Date)<br>\nCells(8, 2) = MonthName(Month(Date))<br>\nCells(9, 2) = MonthName(Month(Date), &#8220;true&#8221;)<br>\nCells(10, 2) = Year(Date)<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><br>\nThe output is as shown in Figure 11.1\n<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Figure 11.1<\/h4>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">11.2 Time Functions<\/h3>\n\n\n\n<p>The time functions are explained in Table 11.2.<br>\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Table 11.2 Time Functions<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th>Function<\/th><th>Description<\/th><\/tr><tr><td>Time<\/td><td>Returns the current system time<\/td><\/tr><tr><td>Hour<\/td><td>Returns the hour from its argument<\/td><\/tr><tr><td>Minute<\/td><td>Returns the minute from its argument<\/td><\/tr><tr><td>Second<\/td><td>Returns the second from its argument<\/td><\/tr><tr><td>Timer<\/td><td>Returns the number of seconds since midnight<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script>\n<\/p>\n\n\n\n<div id=\"content\" style=\"border-bottom: 2px #3399ff solid; border-top: 2px #3399ff solid; padding-bottom: 10px;\">\n<div class=\"column1\" style=\"padding-bottom: 8px;\">\n<h4>Example 11.2<\/h4>\n<p>Private Sub CommandButton1_Click()<\/p>\n<p>Cells(1, 2) = Time<br>\nCells(2, 2) = Hour(Time)<br>\nCells(3, 2) = Minute(Time)<br>\nCells(4, 2) = Second(Time)<br>\nCells(5, 2) = Timer<\/p>\n<p>End Sub<\/p>\n<\/div>\n<figure><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.2.jpg\"><\/figure><p>The output is shown in Figure 11.2<\/p>\n<h5>Figure 11.2<\/h5>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\">11.3 DatePart Function<\/h3>\n\n\n\n<p>The DatePart function returns the part of the date specified in the arguments. The arguments are:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>YYYY- Year<br>\nq- Quarter<br>\nm- Month<br>\nY- Day of Year<br>\nd- Day<br>\nw- Weekday<br>\nww- Week<br>\nh- Hour<br>\nn- Minute<br>\ns- Second<\/p><\/blockquote>\n\n\n\n<h4 class=\"wp-block-heading\">Example 11.3<\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>Cells(1, 2) = DatePart(&#8220;YYYY&#8221;, Now)<br>\nCells(2, 2) = DatePart(&#8220;q&#8221;, Now)<br>\nCells(3, 2) = DatePart(&#8220;m&#8221;, Now)<br>\nCells(4, 2) = DatePart(&#8220;y&#8221;, Now)<br>\nCells(5, 2) = DatePart(&#8220;d&#8221;, Now)<br>\nCells(6, 2) = DatePart(&#8220;w&#8221;, Now)<br>\nCells(7, 2) = DatePart(&#8220;ww&#8221;, Now)<br>\nCells(8, 2) = DatePart(&#8220;h&#8221;, Now)<br>\nCells(9, 2) = DatePart(&#8220;n&#8221;, Now)<br>\nCells(10, 2) = DatePart(&#8220;s&#8221;, Now)<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.3.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<p>The argument Now is to return the current date and time.The output is shown in Figure 11.3<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Figure 11.3<\/h5>\n\n\n\n<h3 class=\"wp-block-heading\">11.4 DateAdd and DateDiff Functions<\/h3>\n\n\n\n<p>The function DateAdd is to add dates and the DateDiff is the function to subtract dates.<\/p>\n\n\n\n<p>The syntax of DateAdd is<\/p>\n\n\n\n<p>DateAdd(&#8220;t&#8221;,n,date)<\/p>\n\n\n\n<p>Where t indicates the interval of the part of the date to add, either d(day), m(month) or year and n is the value to add.<\/p>\n\n\n\n<p>The syntax of DateDiff is<\/p>\n\n\n\n<p>DateDiff(&#8220;t&#8221;,date1,date2)<\/p>\n\n\n\n<p>Where t indicates the interval of the part of the date to subtract. The interval can be YYYY, m, w, ww, d, h, n, s, same as parameters for DatePart. The function of calculating the difference between date1 and date2.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 11.4<\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>Cells(1, 2) = Now<br>\nCells(2, 2) = DateAdd(&#8220;yyyy&#8221;, 2, Now)<br>\nCells(3, 2) = DateAdd(&#8220;m&#8221;, 10, Now)<br>\nCells(4, 2) = DateAdd(&#8220;d&#8221;, 100, Now)<br>\nCells(5, 2) = DateAdd(&#8220;h&#8221;, 10, Now)<br>\nCells(6, 2) = DateAdd(&#8220;YYYY&#8221;, 3, &#8220;2015\/3\/28&#8221;)<br>\nCells(7, 2) = DateDiff(&#8220;YYYY&#8221;, Now, &#8220;2020\/4\/16&#8221;)<br>\nCells(8, 2) = DateDiff(&#8220;m&#8221;, Now, &#8220;2020\/4\/16&#8221;)<br>\nCells(9, 2) = DateDiff(&#8220;ww&#8221;, Now, &#8220;2020\/4\/16&#8221;)<br>\nCells(10, 2) = DateDiff(&#8220;d&#8221;, Now, &#8220;2020\/4\/16&#8221;)<br>\nCells(11, 2) = DateDiff(&#8220;YYYY&#8221;, &#8220;2016\/5\/20&#8221;, &#8220;2020\/4\/16&#8221;)<br>\nCells(12, 2) = DateDiff(&#8220;m&#8221;, &#8220;2016\/5\/20&#8221;, &#8220;2020\/4\/16&#8221;)<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>The output is shown in Figure 11.4<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.4.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Figure 11.4<\/h5>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-10-financial-functions\/\">&lt;&lt;Lesson 10&gt;&gt;<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\">[Contents]<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-12-if-then-else\/\">&lt;&lt;Lesson 12&gt;&gt;<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>&lt;&lt;Lesson 10&gt;&gt; [Contents] &lt;&lt;Lesson 12&gt;&gt; Excel VBA provides various built-in date and time functions that allow us to write VBA codes involving dates and times. We can use date and time functions to display system date and time, add and subtract data and time, converting a string to date and more. 11.1 Date Functions The &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-date-time-functions\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel VBA Lesson 11: Date and Time Functions&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-2895","page","type-page","status-publish","hentry","category-function"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel VBA Lesson 11: Date and Time Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/vba_lesson11.htm\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Lesson 11: Date and Time Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"&lt;&lt;Lesson 10&gt;&gt; [Contents] &lt;&lt;Lesson 12&gt;&gt; Excel VBA provides various built-in date and time functions that allow us to write VBA codes involving dates and times. We can use date and time functions to display system date and time, add and subtract data and time, converting a string to date and more. 11.1 Date Functions The &hellip; Continue reading &quot;Excel VBA Lesson 11: Date and Time Functions&quot;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/vba_lesson11.htm\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-23T10:52:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-date-time-functions\/\",\"url\":\"https:\/\/excelvbatutor.com\/vba_lesson11.htm\",\"name\":\"Excel VBA Lesson 11: Date and Time Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson11.htm#primaryimage\"},\"image\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson11.htm#primaryimage\"},\"thumbnailUrl\":\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg\",\"datePublished\":\"2017-10-11T01:17:10+00:00\",\"dateModified\":\"2020-04-23T10:52:16+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson11.htm#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/vba_lesson11.htm\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson11.htm#primaryimage\",\"url\":\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg\",\"contentUrl\":\"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson11.htm#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel VBA Lesson 11: Date and Time Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel VBA Lesson 11: Date and Time Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/excelvbatutor.com\/vba_lesson11.htm","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Lesson 11: Date and Time Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"&lt;&lt;Lesson 10&gt;&gt; [Contents] &lt;&lt;Lesson 12&gt;&gt; Excel VBA provides various built-in date and time functions that allow us to write VBA codes involving dates and times. We can use date and time functions to display system date and time, add and subtract data and time, converting a string to date and more. 11.1 Date Functions The &hellip; Continue reading \"Excel VBA Lesson 11: Date and Time Functions\"","og_url":"https:\/\/excelvbatutor.com\/vba_lesson11.htm","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-23T10:52:16+00:00","og_image":[{"url":"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg","type":"","width":"","height":""}],"twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-date-time-functions\/","url":"https:\/\/excelvbatutor.com\/vba_lesson11.htm","name":"Excel VBA Lesson 11: Date and Time Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson11.htm#primaryimage"},"image":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson11.htm#primaryimage"},"thumbnailUrl":"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg","datePublished":"2017-10-11T01:17:10+00:00","dateModified":"2020-04-23T10:52:16+00:00","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson11.htm#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/vba_lesson11.htm"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/excelvbatutor.com\/vba_lesson11.htm#primaryimage","url":"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg","contentUrl":"https:\/\/excelvbatutor.com\/vba_img\/figure_L11.1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/vba_lesson11.htm#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel VBA Lesson 11: Date and Time Functions"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/2895","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=2895"}],"version-history":[{"count":9,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/2895\/revisions"}],"predecessor-version":[{"id":3456,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/2895\/revisions\/3456"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=2895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=2895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=2895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}