Tag Archives: TEXT

Highlight weekends and holidays using Conditional Formatting in #Excel Part 2

Posted on January 13, 2019 by

Conditional Formatting with formula could be tricky (or difficult), especially when the data layout is bad because you have to very clear and careful on the “applied to” range and the absolute/relative references set in the formula. Otherwise, it won’t work and can be quite confusing, if not frustrating. 😛

In this post, I will talk about a case that you will see how empty rows in a data set would complicate the process in setting up conditional formatting. And then I will show three different approaches to tackle the issues. Hope you find it helpful. Continue reading →

Rate this:

Advertisements
Posted in Formula | Tagged , , , | Leave a comment

Highlight weekends and holidays using Conditional Formatting in #Excel

Posted on January 6, 2019 by

In this post, I will show you step by step how to set up conditional formatting to highlight weekends and public holidays with Excel. The key is to set the formula right. Having a proper layout will make the whole thing more easier, which will be discussed in the next post. Continue reading →

Rate this:

Posted in Format | Tagged , , , | 2 Comments

Display single letter Day of week in Excel

Posted on May 21, 2016 by

Is it achievable?  Not by formatting but formula.

Rate this:

Posted in Format, Formula | Tagged , , , | Leave a comment

Time Conversion (2)

Posted on April 22, 2014 by

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

Posted on April 18, 2014 by

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

Posted on April 12, 2014 by

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 , , , | 16 Comments