# Tag Archives: TEXT

## Basic Date Calculations – Power Query vs Excel Formula

It is quite common to perform date-related calculations when we work with Excel. Before Power Query, we must do it with built-in functions. However, we may not get the results we need with a single function most of the time. … Continue reading

### Rate this:

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

## Convert numbers of different digits into text of fixed digits using #PowerQuery

This is an extension of the previous post. Here’s the task: If the data is coming from a CSV file, the solution is super easy. You may download the sample CSV file to follow along.

### Rate this:

Posted in Power Query | Tagged , | 3 Comments

## Convert numbers of different digits into text of fixed digits in #Excel

The situation We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work … Continue reading

## Insert Current Time or Today using #Excel functions or shortcuts

It’s not uncommon to insert a date stamp or time stamp on a spreadsheet.  We may use the following functions to get the results easily: =TODAY() ‘to get the date of today =NOW() ‘to get both the date of today … Continue reading

## 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

## 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 , , , | 4 Comments

## Display single letter Day of week in Excel

Is it achievable?  Not by formatting but formula.

## 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