Excel SumProduct lookup multiple criteria

Posted on February 19, 2015March 20, 2015 by Wyman W

Excel SumProduct lookup multiple criteria not in first column In this tutorial, I will explain how to use Excel SumProduct Function to lookup multiple criteria while the lookup value is not in first column of lookup range. The reason for writing this article is that Vlookup can only lookup one criteria and lookup value must be in the first column of lookup table. You can also use Lookup Function to achieve the same result. Read the below article for details. Excel Lookup Function lookup...

Excel Function add year month day hour minute second to date

Posted on February 15, 2015February 27, 2015 by Wyman W

Excel Function add year month day hour minute second to date In this tutorial, I will show how to use Excel Function to: - add year to date - add month to date - add day to date - add hour to date - add minute to date - add second to date I recommend you to read my previous tutorial to understand the concept of time serial. In the following examples, I will assume A1 contains a date 2015 Jul 1. Add year to date Change the Cell Format to dd/mm/yyyy Assume that we add...

Access Excel VBA Check Prime Number using custom Function

Posted on February 14, 2015March 8, 2015 by Wyman W

This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE. Access Excel VBA Check Prime Number using custom Function In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number. 1–20 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 ...

Excel Extract Time from Date Time or Extract Date

Posted on February 4, 2015December 7, 2015 by Wyman W

This Excel tutorial explains how to extract time from Date Time (timestamps) or extract date from Date Time in Excel and how to convert date time to time serial (decimal number). You may also want to read: Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Access Excel extract file name from file path Excel Display Time from Date Time Excel Custom Format Excel Extract Time from Date Time / extract date from Date Time Sometim...

Excel Use Date in IF Condition

Posted on January 27, 2015February 27, 2015 by Wyman W

Excel Use Date in IF Condition (worksheet solution) If you use a Text as a condition in IF, you can simply use double quote on the text such as "Text", but to use Date in IF condition, you cannot simply use double quote on a date. Lets recap how we apply condition on different data type. To apply condition on Number: =IF(A1>5,"large number","small number") To apply condition on Text: =IF(A1="John","My friend","Stranger") What about Date? Should we just use double quote as we ...

Excel time difference in hours minutes seconds

Posted on January 24, 2015February 9, 2015 by Wyman W

Excel time difference in hours minutes seconds Excel has a special way to treat Date Time value but most people are not aware of that. You need to understand the meaning of the numeric values that represent Excel Date Time in order that you can freely perform Excel date time conversion to hours, minutes, days, etc. After you understand the meaning of the numerical values, you can easily calculate Excel time difference. I have written a post that explains conversion between time and numeric ...

Excel lookup partial text using Vlookup with Wildcard

Posted on January 14, 2015February 18, 2016 by Wyman W

This Excel tutorial explains how to lookup partial text using Vlookup Function with Wildcard. Excel Vlookup second matched value or specific occurence Excel Lookup Function lookup multiple criteria not in the first column Excel lookup Function lookup multiple criteria Excel lookup partial text using Vlookup with Wildcard Many people know how to use Vlookup but not many people know Vlookup allows Wildcard in the lookup value. Lets recap the syntax of Vlookup first. Syntax of Vlookup...

Excel Dynamic Print Area

Posted on January 9, 2015January 3, 2016 by Wyman W

This Excel tutorial explains how to set dynamic print area. You may also want to read: Excel dynamic Data Validation list Excel dynamic data range Excel graph dynamic data range Excel Print Area Print Area is a function to define the worksheet Range you want to print. To set a Print Area, select a Range, say A1:B6, then click on Set Print Area Click on FORMULA tab > Name Manager You will find Range A1:B6 is named as Print_Area, which is the reserved name f...

Excel Find records of Minimum Date or Maximum Date

Posted on December 30, 2014March 1, 2015 by Wyman W

This tutorial explains how to find minimum date and maximum date of each record in Excel by sorting and remove duplicate. You can find more tutorials at http://Access-Excel.tips Find records of Minimum Date or Maximum Date, Why? Find records of minimum date or maximum date is an essential skills in employee records. Some have data use an unique key such as employee ID plus a date as the unique record. For example, when Peter (Employee ID 001) was hired on 1/1/2014 and then terminated on 1/5...