Practice Video: Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)

Loading...

來自 Macquarie University 的課程

Excel Skills for Business: Advanced

248 個評分

Macquarie University

Excel Skills for Business: Advanced

248 個評分

課程 4（共 4 門，Specialization Excel Skills for Business）

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.

從本節課中

Data Cleaning and Preparation

Been handed a messy spreadsheet? This module focuses on data cleaning and preparation. You will learn how to use dynamic and automated fixes, clean messy data, automate these processes through functions, and ensure data remains clean dynamically. We will use functions to fix dates, replaces blanks and remove unwanted characters from data – all to help us build an efficient solution that lasts.

Practice Video: Replace blanks with repeating values5:41

Practice Video: Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)6:21

Practice Video: Remove Unwanted Spaces (TRIM, CLEAN)4:42

Practice Video: Diagnostic Tools (ISNUMBER, LEN, CODE)7:21

Practice Video: Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)6:12

與講師見面

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

Dates can be a major issue with important data.

That can be because your regional settings are not the

same as the regional setting from whence the data came,

or simply because they have used a format that Excel just doesn't recognize.

That's the situation we have here.

While these dates are easily recognizable to us,

Excel does not recognize this date format.

That can be a problem the moment you try and do

any type of calculations or processing with these dates.

One of the things the client wants to be able to do,

is to run some analytics on which month people are most likely to join.

So they would like to get the months name from that date.

The way this would normally work,

and I'll just quickly demonstrate,

I'm going to click in here,

in J 1, type equals today,

open brackets, close brackets,

and press enter to get today's date.

So that is a valid date format for my regional settings,

and I would like to be able to extract the month from that date,

but not just the number 10,

I actually want the month name.

One really useful function that you can use when you're trying to extract

information and present it in a particular format is the RTEXTfunction.

What the TEXT function does is takes the value

and presents it as text in a format of your choosing.

We're going to type equals TE and tab,

and I'm going to click on the value that I want to extract the text from,

and I'm going to type a comma.

The second argument required is what format you want that text in.

Now I want to get the month name,

and you may recall when working with dates we use a kind of code.

Two D's is for the day.

Three D's is the abbreviated name of the day,

and four D's is the full length name of the day.

We can use M for the month.

In this case, I want the full length name of the month so I'm going to type four M's.

Close my quotes, close my brackets, and enter.

There's the name of the month.

That worked really nicely.

Let's try to apply that same function to our join date.

I'm going to click on column G and insert to get a new column.

I'm going to type in Join Month in the heading and click enter.

I'm then going to use my TEXT function again, equals TE,

tab, click on join date,

comma, and our four M's.

Close my brackets.

However, when I press enter this time I do not get what I was expecting.

This is because Excel does not recognize this as a valid date.

The next thing we're going to look at are some functions that can

help us take what is really text and convert it to a date format.

Let's come and have a look at this.

Basically what we have is a string of texts.

The function that will help us take text and convert it to

a date that Excel recognizes is the date function.

We're going to double click on a cell and just after the equals,

we're going to type DA for date and press tab.

The Date function requires three arguments.

A year, a month, and a day.

How are we going to get the year out of this little bit of text?

Well, you can see that it is the first four characters and that is consistent throughout.

So what we're going to do,

we're going to use our functions that we learned about in the Intermediate One course,

LEFT, RIGHT, and MID,

to extract the piece of text that we need.

Just before I do that,

I'm just going to copy this because we're going to need that again.

What I need to do first of all is get the year,

which is the first four characters sitting in sheet 1 F two.

To get the first four characters,

I use my LEFT function.

I'm going to type LE and tab.

I'm then going to type a comma and the number of characters I

need off the left is full and then close my brackets.

I'm then going to type another comma.

The month is sitting in the middle of the text.

To get this, I'm going to need my MID function.

I'm going to type mid, open my brackets.

Now I'm going to paste that text I copied earlier,

which is sheet 1 F2 and the MID requires two arguments,

a starting point and a number of characters.

Our starter point is actually the sixth character in,

and then we need two characters returned.

So there's our mid and type a comma.

The last thing I need is my day,

which is my to our right most characters.

For this, I'm going to use

the RIGHT function and I'm going to paste my Sheet1 F2 again.

I just need the two characters of the right.

Close by brackets from my RIGHT function,

close my brackets from my date function.

What that is going to do is chop off the text into day, month,

and year, and reformat it as a recognized Excel date.

Let's click enter.

Not only has that corrected the date,

but our month function is now working.

I'm going to copy it down.

That has corrected all my dates and I can copy down my join month,

and that has been repaired as well.

So just to summarize then.

A very useful function for taking text and converting it

to a proper recognized date is the DATE function.

We often use that in conjunction with LEFT, RIGHT, and MID.

Another little function we looked at which does the

opposite actually takes the date and extracts some texts,

which we want from it and that is the TEXT function.

In the next video,

we'll look at another potential issue,

which is how we get rid of unwanted spaces.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2018 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取