Analysis of business data often requires working with date values in Excel to answer questions such as “how much money did we make today” or “how does this compare to the same day last week?” And that can be hard when Excel doesn’t recognize the values as dates.
Unfortunately, this is not unusual, especially when multiple users are typing this information, copying and pasting from other systems and importing from databases.
In this article, we will describe four different scenarios and the solutions to convert the text to date values.
Dates that Contain a Full Stop/Period
Probably one of the most common mistakes beginners make when typing dates into Excel is doing so with the full stop character to separate the day, month, and year.
Excel will not recognize this as a date value and will go ahead and store it as text. However, you can solve this problem with the Find and Replace tool. By replacing the full stops with slashes (/), Excel will automatically identify the values as dates.
Select the columns on which you want to perform the find and replace.
Click Home > Find & Select > Replace—or press Ctrl+H.
Post a Comment Blogger Facebook
We welcome comments that add value to the discussion. We attempt to block comments that use offensive language or appear to be spam, and our editors frequently review the comments to ensure they are appropriate. As the comments are written and submitted by visitors of The Sheen Blog, they in no way represent the opinion of The Sheen Blog. Let's work together to keep the conversation civil.