Tag Archives: TEXT

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

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:

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

Highlight weekends and holidays using Conditional Formatting in #Excel

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

Display single letter Day of week in Excel

Is it achievable?  Not by formatting but formula.

Rate this:

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

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