Tag Archives: Date

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

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

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

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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

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

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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 , , , | 5 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 , , | 2 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

Rate this:

Posted in Excel Basic | Tagged | Leave a comment

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