Month: May 2016

DATE function

This function will convert a day, month and year to a serial number that Excel uses for date values. Syntax DATE(Year, Month, Day) Year (required) – This is the year number from 1900 to 9999. Month (required) – This is the month number from 1 to 12. Day (required) – This is the day number from 1 to 31. Example In this example we create a few dates by reference to a year, month and day. Notice that if we use a month greater than 12 or a day greater than the actual number of days in the month, then the result rolls over into the next month or year....

Read More

DATEVALUE function

This function will convert a date stored as text to a serial number that Excel uses for date values. Syntax DATEVALUE(Text) Text (required) – This is the date stored as text you would like to convert to a serial number. Example In this example we convert a few different text dates to the serial number. =DATEVALUE("December 31,...

Read More

SUBSTITUTE function

What Does It Do?   This function will allow you to find and replace a piece of text within a text string. You can either replace all occurrences of the piece of text or just a particular instance of it.   Syntax   SUBSTITUTE(Text, Find, Replace, Instance) Text (required) – This is the text you are going to search. Find (required) – This is the piece of text you want to find. Replace (required) – This is the piece of text you want to replace it with. Instance (optional) – This is the instance number of the text you want to find and...

Read More

Getting Started

So you’re new to Excel and have heard how great it is for calculations, data analysis, creating charts and graphs and the millions of other things you can do with it, but where to start?     When you open Excel, you will see this screen that will allow you to either open a new workbook or a previously saved workbook. Open Blank Workbook – Clicking here will allow you to open a new blank workbook in Excel. Recent Workbooks – This is a quick access area that will show you the last few Excel workbooks you’ve had open...

Read More

How To Conditionally Concatenate A Range

Excel has some great built in functions for summing and counting conditionally based on given criteria, but to concatenate a range conditionally we will need to create our own user defined function. This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice. Function ConcatenateIf(CriteriaRange As Range, Criteria As Variant, _ ConcatenateRange As Range, Optional Delimiter As String = ",") As Variant Dim j As Long Dim TempString As String TempString = "" On Error GoTo ErrorGoTo 'Check if criteria range and concatenate range are the...

Read More

Follow Us

Advertisement

Free Book

Recent Tweets

Categories

Pin It on Pinterest