Monthly Archives: April 2014

3D VLOOKUP – Perform VLOOKUP from more than one table

Pls have your 3D glasses ready… only if you are going to watch a 3D movie.  You don’t need it for a 3D VLOOKUP. We talked about how to do a 2D VLOOKUP by using MATCH together with VLOOKUP.  With that … Continue reading

Rate this:

Posted in Formula | Tagged , , , | 3 Comments

Time Conversion (2)

How to convert 24hr input as 4-digit number into TIME in Excel? i.e. turn 500 into 05:00 correctly in Excel. Answer: =TEXT(Your 4-digit number,”00\:00″)+0 ‘Format the result as TIME

Rate this:

Posted in Excel Tips, Formula | Tagged , | 6 Comments

Convert an 8-digit number into Excel-recognizable Date

Have you ever encountered a situation that date is input as 8-digit number instead of the correct date value in Excel? You cannot perform any proper date related calculation / functions to the 8-digit number in Excel because it is … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | 25 Comments

Calculate number of a specific day between two dates

Answer: =SUMPRODUCT(–(TEXT(ROW(INDIRECT(B1&”:”&B2)),”DDDD”)=”Sunday”)) ‘where B1 is start date; B2 is end date. Excuse me? What it says?

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | 15 Comments

SUM across different worksheets (aka 3D SUM)

Did you ever make a formula like the one below?  After reading this post, I hope you will never make a formula in this way again.

Rate this:

Posted in Excel Tips, Formula | Tagged | 9 Comments

How to get rid of the error message?

  Did you know… there are 7 (+1 if you consider #### an error) types of error message in Excel?  In my experience, most users do not care about the meaning of the errors.  They just want them “disappear” on their … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | 2 Comments

When unhide row doesn’t work…

Hidden rows cannot be unhidden? Why? Although today is April Fools’ Day and the question sounds like an April Fool’s question, this post is not about to fool anyone.  Just another real case to share. For an unprotected sheet, hidden … Continue reading

Rate this:

Posted in Excel Basic, Excel Tips | Tagged , | 78 Comments