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
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
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
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
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
Is it achievable? Not by formatting but formula.
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
Well, there is no such custom format for date in Excel… However it can be achieved indirectly, with helper cell 🙂
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?
Posted in Formula
Tagged Date, MAX, MIN
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
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
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?
Posted in Excel Basic
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
Posted in Excel Basic
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