Excel convert column with Date and Text to Date

Posted on June 3, 2015June 7, 2015 by Wyman W

This Excel tutorial explains how to convert a column with Date and Text to Date. Excel convert column with Date and Text to Date Excel has a built-in Function DateValue, which converts a Date in Text format to date serial. What if the column contains both Text and Date? If you use DateValue Function on Date, #VALUE! will return. So how can we convert Text and Date at the same time? Convert Text to Date in Notepad The below method is even faster than DateValue Function or Text to Column, it i...

Excel Display Time from Date Time

Posted on May 13, 2015May 13, 2015 by Wyman W

This Excel tutorial explains how to use Custom Format to display Time from Date Time. You may also want to read: Excel Extract Time from Date Time Excel Custom Format Excel Display Time from Date Time In my previous post, I have explained how to extract time from date time. What I mean "extract" is that the date is using formula to completely remove date from date time. In this tutorial, I will explain how to use custom format to display Date Time in Time format. Custom Format to disp...

Excel find all external links and broken links in workbook

Posted on April 13, 2015December 26, 2016 by Wyman W

This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro. You may also want to read Excel VBA Workbook LinkSources Method Excel VBA refresh closed workbook Excel find all external links and broken links in workbook There are several ways to find external links and broken links in workbook, some are easy to use but have limitations, I will talk about each method in this article. Note that this article ...

Excel VBA Function sum colored Cell count colored Cell

Posted on April 10, 2015 by Wyman W

This Excel tutorial explains how to sum colored Cell and count colored Cell in Excel worksheet. Excel VBA Function sum colored Cell and count colored cell In my previous posts, I have explained how to use ColorIndex Property to find the first colored Cell. In this Post I will create a custom Function to count colored Cell of a Range and sum colored Cell To recap the previous posts, you can click on the followings. Excel VBA custom Function Find the first colored Cell value VBA Excel...

Excel timer countdown for test using Application.OnTime

Posted on April 4, 2015September 10, 2015 by Wyman W

This Excel tutorial shows how to make Excel timer countdown for test using Application.OnTime You may also want to read: Excel VBA Application.OnTime Method to schedule event with timer Send Email in Excel VBA using CDO and OLMailItem Excel timer countdown for test using Application.OnTime If you are going to job interview, you may be asked to do an Excel test. I have been thinking it would be awesome to make a countdown in the worksheet to tell the interviewee how much time is left. ...

Excel VBA Application.OnTime Method to schedule event with timer

Posted on April 4, 2015July 19, 2016 by Wyman W

This tutorial explains how to use Excel Application.OnTime Method to trigger / schedule event at specified time. You may also want to read: Auto Open Excel with Windows Task Scheduler Excel automatically refresh pivot table Excel VBA Application.OnTime Method to trigger event at specified time You can do the followings with Excel VBA Application.OnTime Method - schedule a procedure to be run at a specified time in the future - schedule a procedure to be run after a specific amou...

Excel Fill Blank Rows or blank Cells in inactive Pivot Table

Posted on April 1, 2015March 24, 2016 by Wyman W

This Excel tutorial explains how to use the value above to fill blank rows (blank cells) in inactive Pivot Table in Tabular format. You may also want to read: Excel Delete Blank Rows Excel VBA reformat merged cells to row Fill Blank Rows or blank Cells Excel Pivot Table Fill Blank Rows In Pivot Table, when you group an item in column, the same grouping name does not repeat itself and leaving blank rows. In the below picture, Division contains blank rows. In Excel 2010 o...

Excel VBA Add Table AutoFilter cancel AutoFilter sorting

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

This Excel tutorial explains how to use Excel VBA to add Table AutoFilter, cancel Table AutoFilter, sorting Table. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Worksheet AutoFilter Excel VBA Add Table AutoFilter or Cancel AutoFilter Using VBA to add AutoFilter or cancel Auto Filter is very simple if you know the code, but you should note th...

Excel use If condition on aggregate Function using Array

Posted on March 30, 2015March 30, 2015 by Wyman W

This Excel tutorial explains how to use If condition on aggregate Function using Array such as Average, Median, Mean, Maximum, Minimum. Excel use If condition on aggregate Function using Array Some Excel formula require you to input a range cells as argument in order to calculate a value, such as Sum, Count, Average, Median, Mean, Maximum, Minimum. However, in those formula, you cannot use If Condition on the data Range before calculating Sum, Count, Average, Median, Mean, Maximum, Minimu...

Excel count substring in string or text

Posted on March 29, 2015 by Wyman W

This Excel tutorial explains how to count substring in string (text). Excel count substring in string (text) Excel 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 Macro solution for your reference. Excel count substring in string using Len and Substitute Function Excel Len Function is to measure the length of text. Excel Substitute Function is to replace a substring in a text. To c...