Access Excel VBA day month year difference between two dates

Posted on July 18, 2015July 7, 2017 by Wyman W

This Access Excel tutorial explains how to find complete year difference, month difference and date difference between two dates. You may also want to read: Excel DATEDIF Function to calculate date difference Access Excel VBA difference between two dates (year difference, month difference, day difference) In Excel worksheet, there is a Function called DateDif that can calculate difference between two dates in complete year, month or day. In VBA, there is no DateDif Function. There is ...

Access Excel add leading zero

Posted on May 5, 2015December 26, 2016 by Wyman W

This Access / Excel tutorial explains how to add leading zero using Text Function or using custom Function to pad other text in suffix or prefix. Excel add leading zero (non-VBA) Number in Excel can come in two forms - Text or Number. For number in Number format, you may not add zero in prefix For number in Text format, you can add a zero in prefix In order to convert a Number to Text, add a ' in the prefix. For example, instead of typing 00123, type '00123 Sometimes you receive ...

Access generate random letter A to Z using Rnd

Posted on April 5, 2015 by Wyman W

This Access tutorial explains how to generate random letter A to Z / a to z or a mix using Rnd Function. You may also want to read: Excel generate random letter A to Z using RANDBETWEEN Access generate random letter A to Z using Rnd In order to generate random letter, we have to make use of two Functions. - Rnd - Chr In the below sections, I will briefly go going through these two Functions. For details, click on the link above. Access generate random number using Rnd Access R...

Access Excel Generate random HKID Hong Kong ID card number

Posted on April 5, 2015March 17, 2016 by Wyman W

This Access Excel VBA tutorial demonstrates how to generate random HKID / Hong Kong ID card number. You may also want to read: Access Excel Function HKID Check Digit (last digit) Excel VBA Rnd Function to generate random number Excel generate random letter A to Z using RANDBETWEEN Assign unique random number using Excel Access Excel Generate random HKID (Hong Kong ID card number) The purpose of this article is to generate a random HKID (Hong Kong ID card number) that has va...

Access count substring in string or text

Posted on March 29, 2015September 15, 2015 by Wyman W

This Access tutorial explains how to count substring in string (text). Access count substring in string (text) Access does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a VBA solution for your reference. Access count substring in string using Len and Replace Function Access Len Function is to measure the length of text. Access Replace Function is to replace a substring in a text. To cou...

Access Rnd Function generate random number and decimal

Posted on March 26, 2015March 27, 2015 by Wyman W

This Access tutorial explains how to use Access Rnd Function to generate random number and create custom Function for random decimal number. Access Rnd Function to generate random number Access Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and smaller than 1 (1 and 0 exclusive). Syntax of Access Rnd Function Rnd[(number)] Number Rnd generates Not supplied or any positive number Generate a new random number 0 R...

Access Excel assign number for custom sorting sequence

Posted on March 26, 2015April 20, 2015 by Wyman W

This Access Excel tutorial explains how to assign number to Text for custom sorting sequence and how to use Excel Custom Lists. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Access Excel Assign number to Text for custom sorting sequence Suppose your company has departments IT, HR, Finance. Normally if you sort the sequence in ascending order, it wou...

Access Excel VBA group number with larger than smaller than (age group)

Posted on March 26, 2015December 4, 2015 by Wyman W

This Access Excel tutorial explains how to create custom Function to categorize /group number with larger than smaller than symbol, such as age group. Access Excel categorize / group number with larger than smaller than symbol Sometimes when you have a group of numbers, you may need to categorize / group them into different categories for analysis. For example, you may need to make age group for employees, such as <20 >=20 to <30 >=30 to <40 >=40 In order ...

Excel Access MRound custom Function round to nearest multiple

Posted on March 8, 2015March 17, 2019 by Wyman W

This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple. You may also want to read: VBA Excel Access roundup rounddown Function Excel MRound Function Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we want the bonus amount looks better. ...

Access Excel custom split Function to delimit text

Posted on February 22, 2015December 26, 2016 by Wyman W

This tutorial shows how to create a custom Access Excel VBA custom Function to delimit text with the help of VBA "Split" Function instead of "Text to Columns". Delimit text in Excel (non-VBA) In Excel worksheet, there is a function called "Text to Columns" (Data > Text to Columns) to separate a text by delimiter. For example, Cell A1 contains "a,b,c" and after split by comma, A1 contains "a", B1 contains "b" and C1 contains "c". An alternative way to do that is using Left Function with...