# Tag Archives: Date

## 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 text date into date with Power Query for #Excel

I have a couple of blogposts about how to convert dates stored as text in various formats into real date that Excel recognizes, and you can further work on it. There are many approaches such as text to column and … Continue reading

## Determine leap year with #Excel

Today is a special day.  Today is February 29th, which happens only once every four years.  And today is Saturday, when I usually post a new blog.  So let’s talk about how to determine if a year is leap year using … Continue reading

### Rate this:

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

## Same day last year…

Avoid Overthinking Getting same day of last year using Excel formula In retail, it’s very common to compare sales of same day, not same date, of last year.  If you are not in retail sector, you may wonder what is … Continue reading

### Rate this:

Posted in Excel Tips | Tagged | 2 Comments

## Data transformation with Power Query

Transform date input as “Excel-unrecognized” text string into date (number) usable by #Excel This is a continuation of the previous post – How to turn “1st January, 2017” into #Excel recognizable date?   …using Get & Transform (better known as #PowerQuery). Note: … Continue reading

## How to turn “1st January, 2017” into #Excel recognizable date?

Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc… In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, … Continue reading

### Rate this:

Posted in Formula | Tagged , , , , , , | 8 Comments

## Copy and Paste can do so much…

Answer to the 5 little Tips (tests) – Part 5/5 The task: To sort the following dates in descending order.  Sound silly but… The first thing you would probably do is to go to Data tab and then click the Sort Z to A icon? … Continue reading

### Rate this:

Posted in Excel Tips | Tagged , , | 22 Comments

## 5 Little Excel Tips (tests)

First of all, Happy New Year of 2017!  Wish you all an Excellent year ahead! Let’s start the year with a simple “Game” which I developed for in-house Excel training in my previous company.  All participants were expected to have basic Excel knowledge. … Continue reading

## Extract 6 digits from a string and convert it into date

Got a question about how to extract the date of birthday (DOB) portion from an ID number like 63102400965, where the first 6 digits represent the DOB, so that we could use the date for other calculation.  In this example the DOB … Continue reading

## Display single letter Day of week in Excel

Is it achievable?  Not by formatting but formula.

## Date Filter is good, but still with limitation

This is about how to filter a particular date, say 1st of January regardless year, from a list of dates. As a matter of fact, not many people are aware of the Date Filter feature in Excel.  This is true at … Continue reading

## Date Formats – A trick to format date with “st”, “nd”, “rd”, “th”

Well, there is no such custom format for date in Excel… However it can be achieved indirectly, with helper cell 🙂

### Rate this:

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

## How to calculate number of overlapping days for two periods?

Have you ever encountered a situation that you need to find out how many days in Period 2 fall into Period 1? Is using IF(IF…(IF…(IF…(IF….. the first thing in your mind?

### Rate this:

Posted in Formula | Tagged , , | 10 Comments

## Advantages of having date input correctly in Excel

In previous posts, what do we need to know about inputting date in Excel was discussed.  Suggested solutions for fixing trouble dates were also given.  But WHY having date input correctly in Excel is so important? Why should we care? Answer: It’s … Continue reading

### Rate this:

Posted in Excel Basic, Excel Tips | Tagged | 1 Comment

## Fixing trouble dates

Different ways to convert dates stored as text or other unrecognized date formats to real dates in Excel. From time to time, we work with dates in Excel files.  Well, if dates were input correctly as serial number, everyone is happy. … Continue reading

### Rate this:

Posted in Excel Tips | Tagged , , | 4 Comments

## What we need to know about inputting Date in Excel?

When you type “1/5/2015” into Excel, do you know whether it is “1st of May, 2015” or “5th of January, 2015”? The answer: It depends! Depends on what?

### Rate this:

Posted in Excel Basic | Tagged | 1 Comment

## Are you sure you have input a date correctly in Excel?

Although they sound like basic questions, I am quite surprised that many colleagues are not aware of the different ways to input date in Excel “correctly”.  I would like to understand the situation of other Excel users by putting a poll … Continue reading