IntroductionWelcome| 00:04 | Hi! I am Dennis Taylor, and welcome to
Excel 2007 Working with Dates and Times.
| | 00:10 | Whether you are using Excel to track
order and shipping dates, calculate the age
| | 00:14 | of your inventory, or tabulate the
hours worked on a project, you need to
| | 00:18 | understand how Excel stores date and time data.
| | 00:21 | And although occasionally problematic, most
of these techniques are pretty easy to use.
| | 00:26 | I will show you the best ways to enter
dates and times, including some great shortcuts,
| | 00:31 | how to quickly build date time series,
apply date time formatting instantly, and
| | 00:36 | when necessary, create your own custom
date formats for optimum readability.
| | 00:40 | We'll see powerful date functions
that determine a day of the week,
| | 00:45 | calculate the number of working days
between two dates, and determine future
| | 00:50 | and past target dates.
| | 00:51 | I will also show you the powerful
but poorly documented datedif function.
| | 00:56 | We'll look at shortcuts and tips to
optimize tricky time calculations and the
| | 01:01 | best techniques for converting dates
downloaded from other sources, how to use
| | 01:05 | dates in data validation rules, as
well as in filtering. But enough talk.
| | 01:10 | Let's start saving time with Excel 2007.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the exercise
files for this course, you can put them on
| | 00:04 | your desktop, as I have,
or anywhere else you want.
| | 00:07 | As I click File > Open here, the top
folder contains subfolders for each chapter,
| | 00:14 | which contain the exercise files themselves.
| | 00:16 | I am double clicking here on Chapter01, and
you see the two files that are in place there.
| | 00:21 | If you do not have access to these files,
you can follow along with your own
| | 00:25 | files as we proceed through the course.
| | Collapse this transcript |
|
|
1. Excel's Date/Time SystemUnderstanding how Excel records and stores dates| 00:00 | In many Excel worksheets, date data
and time data are important ingredients.
| | 00:06 | For example, on the screen here, in
column F, we see Hire Dates, important
| | 00:10 | information for this list here.
| | 00:13 | In another worksheet here, we're
keeping track of sales by date and time; both
| | 00:18 | columns C and D are important.
| | 00:20 | Notice that in both cases the
information is right aligned.
| | 00:25 | The way Excels handles dates and times
is perhaps best understood by talking
| | 00:29 | about the idea that both of
them are considered values.
| | 00:33 | If you type a date into a cell, for
example, 4/5/12, April 5th 2012, the
| | 00:41 | information is right aligned.
| | 00:44 | If you type in a date that really
doesn't exist, probably a typo, for example,
| | 00:49 | November 31st, slash 12,
| | 00:53 | that's left aligned. As far as Excel is
concerned, that's just string of characters.
| | 00:58 | When we put in valid dates, Excel
automatically aligns them on the right side,
| | 01:03 | as it does with values in general
throughout Excel; put in a value,
| | 01:07 | it's right aligned.
| | 01:08 | Text entry is left aligned.
| | 01:09 | Of course, you can change that, but,
because it's right aligned it alerts us
| | 01:14 | with the idea that this is a value, but
what value and should we really care, or
| | 01:19 | do we really need to know?
| | 01:21 | If you really do want to know what
value is being stored here, we could do this
| | 01:25 | a number of different ways, possibly
on the Home tab, click the comma button
| | 01:28 | for a quick fix here.
| | 01:29 | There's some keystroke shortcuts,
41,004. What does that mean?
| | 01:33 | I'm pressing Ctrl+Z here, do an undo.
| | 01:37 | In Excel, the date time
system begins January 1st, 1900.
| | 01:42 | If you were to enter that date, as I'm
doing right now, press Enter, the value 1
| | 01:49 | is being stored in the cell.
| | 01:51 | A quick keystroke shortcut to do this,
by the way, would be Ctrl+Shift+Tilde.
| | 01:55 | That'll display this as the value 1.
| | 01:57 | That's the so-called general
format there. So, that's 1.
| | 02:00 | That's January 1st, 1900.
| | 02:02 | January second is the value 2, January
third the value 3, and so on, and so on, and so on.
| | 02:09 | You rarely need to see that actual
value, but it does get to the heart of how
| | 02:14 | Excel handles information.
| | 02:16 | That allows us to use
these dates in calculations.
| | 02:19 | We can subtract days to find out how
many days exist between two different dates.
| | 02:24 | So, this works smoothly and
efficiently, starting January 1st 1900, until,
| | 02:29 | believe it or not, the year 10,000.
| | 02:32 | Now, there is a problem here if you
download data that's come from the Mac.
| | 02:37 | In the world of Mac, dates begin
January 2nd, 1904, and here now you're going to
| | 02:44 | have some problems when you copy data.
| | 02:47 | On the Office button, under Excel
Options, the category Advanced, you'll
| | 02:56 | notice here, one of the workbooks options
that you can change is Use the 1904 date system.
| | 03:03 | If I were to apply that right now, keep
an eye on the dates here, the April 5th
| | 03:09 | and the January 1st dates, as I click
this and then click OK, watch those dates.
| | 03:14 | You'll see what's happened here.
| | 03:17 | When you are downloading data from the
Mac, you have to think about this issue,
| | 03:21 | and there are some tough conversion
issues here and there, but it's a good idea
| | 03:26 | when you're downloading data. Check it out.
| | 03:28 | Go to the Office button > Excel
Options and see if there is a setting.
| | 03:34 | Now, most of the time, this is
not a big issue, and for most people
| | 03:37 | this isn't a major concern, but if this
is set, you might have some conversion
| | 03:41 | issues to deal with.
| | 03:42 | I'm going to uncheck this here, and once
again, as I click OK, watch those dates
| | 03:46 | return to their previous setting.
| | 03:50 | In using dates in Excel, again, every
date is considered a value, even though
| | 03:54 | you don't see it, and it works
beautifully and all leap years are accounted for,
| | 03:58 | all the century years.
| | 03:59 | There's one tiny flaw for most of us.
| | 04:01 | This don't make any difference.
They actually calculated that February 29th, 1900
| | 04:05 | doesn't really exist, but it
doesn't affect any of our calculations.
| | 04:08 | So, it's ultimately a very logical
system that makes sense. All dates are
| | 04:12 | stored as values.
| | Collapse this transcript |
| Understanding how Excel records and stores times| 00:00 | In column D, an important piece of
information in this worksheet is the Time
| | 00:05 | recorded for the various sales that
have been made. Perhaps some analysis is
| | 00:08 | going to be done based on which times of day
| | 00:11 | the greatest sales were recorded at.
| | 00:13 | So, when we make an entry of a time, it's
similar to what we do with dates in Excel.
| | 00:19 | Excel actually stores a value.
| | 00:22 | The unit of measure in Excel for
dates and times is 1 equals a day.
| | 00:28 | If I put in, for example, a time, 4:30,
that actually represents a portion of the
| | 00:35 | day. Now it's about 4/24th.
| | 00:36 | It's about a sixth of the day.
| | 00:39 | We're not thinking of that when we make
data entries, but a real value is being
| | 00:43 | stored here, and it's a portion of the day.
| | 00:46 | We can quickly see what this value is -
although we don't really need to do this
| | 00:50 | very often - by clicking the comma
button. That's one fast way to do this.
| | 00:54 | That's 0.19 of the day, and just for the
moment here, as I press Ctrl+Z to undo,
| | 01:00 | you could experiment with this a little bit
| | 01:02 | to get a sense of it yourself.
| | 01:04 | You could imagine that 6 AM
is a quarter of the day.
| | 01:07 | So, if you were to type 6:00, if we
were to display the value of this, we'd see
| | 01:13 | that it's 0.25, and if you were typing
in a time after noon, for example, you
| | 01:19 | might type 1:30 as 1:30 PM.
| | 01:24 | It's one way to do this, that too is
being stored as a value, and you could
| | 01:28 | imagine here what this might be, or at
least a rough idea. Click the comma and
| | 01:32 | we see what's happening.
| | 01:34 | So, times are considered
portions of the day, and they're stored
| | 01:37 | fractionally. And as we'll see in this
course, this means that we can perform
| | 01:42 | any number of different calculations
based on time of day, and this also
| | 01:47 | allows us to keep track of times
across different days, so we can calculate
| | 01:51 | differences, as we'll see,
| | 01:53 | so that the basic idea is that when you
enter a time, you're really entering a
| | 01:57 | value, and it's a portion of the value of one.
| | 02:01 | It's a percentage of the day.
| | Collapse this transcript |
|
|
2. Creating Date/Time EntriesLooking at standard date/time entry options and acceptable alternatives| 00:00 | In Excel, the standard way to type a
date entry is with the slash or hyphen.
| | 00:05 | In either case, the default
display will include a slash.
| | 00:09 | So, for example, if we want to put in
May 13th of 2012, we'll simply type it this
| | 00:15 | way. There's no reason to type a
four-digit year, and as we press Enter, we
| | 00:20 | see the display this way. And a hyphen will
work the same way, of course, and here we
| | 00:24 | see the display same way.
| | 00:25 | Even though we typed a
hyphen, the slash appears.
| | 00:28 | The default setting throughout Excel,
unless you have made a change, is to
| | 00:32 | display a four-digit year.
| | 00:35 | You can either override this on a cell-
by-cell basis, range basis or within a
| | 00:40 | worksheet, or within a workbook, but if
you want your standard display from now
| | 00:45 | on to be a two digit year, as we see,
for example, in column D, for some people
| | 00:50 | that's just fine, what you'll want to do
is actually to go outside of Excel into
| | 00:56 | your operating system.
| | 00:57 | So, if you go to the Control Panel,
and this will vary somewhat by operating
| | 01:01 | systems, so I'm using Windows 7 here,
you want to check out the Regional
| | 01:06 | settings. And when you get there,
don't just leap right into Date and Time;
| | 01:10 | continue to go to Regional because there you
will see some default settings for dates and times.
| | 01:17 | And, for example, here you'll see
this Short date, and this is the default,
| | 01:21 | unless you've altered it, is
to display a four-digit year.
| | 01:24 | I think that's a good idea, and I'll
explain in bit why, but I think that's
| | 01:28 | probably a better choice, but if you
prefer from now on to have your default
| | 01:33 | settings to be display years with two
digits, fine; make the change here.
| | 01:39 | You can always override that within a
given worksheet or workbook, as well.
| | 01:44 | So, you may or may not have made that change.
| | 01:46 | I did mention it's best
to show four-digit years.
| | 01:50 | For many, many people this will not
make any difference, but here and there,
| | 01:53 | there is something that you should note.
| | 01:55 | Suppose you have the birth dates of retirees,
and so here's someone born on May 12th in 1931.
| | 02:02 | You probably would have typed this way.
No surprises there, as you see the entry here.
| | 02:07 | There's another former retired employee,
she was born August 12 of 1929, and
| | 02:13 | you probably type it this way.
And if you weren't paying attention, you'd say, 'Whoops!
| | 02:17 | Wait a minute here, what's happening?'
| | 02:20 | By default, in Excel, and this probably
will change in a few years, but anytime
| | 02:24 | you enter year that's from 30 through
99, it's automatically determined to be
| | 02:30 | previous century, 20th century, and any
year that you enter from 0 through 29
| | 02:36 | is automatically in this century.
| | 02:38 | In your day-to-day work of business,
it doesn't make any difference at all,
| | 02:41 | but here and there, you do want to be
sensitive to that. And if you are working in
| | 02:45 | an environment like this, you do want to
be typing four-digit years to avoid any
| | 02:49 | confusion about this.
| | 02:51 | When it comes to entering times, you
should be using the colon, and there are
| | 02:56 | couple of ways to enter times.
| | 02:57 | We see that the time here entered as 4:30.
| | 02:59 | You'll notice in the formula bar, that's 4:30 AM.
| | 03:03 | Now, in the different situation, this
could also mean four hours and 30 minutes,
| | 03:07 | if you're tabulating some time that way.
| | 03:10 | A couple of shortcuts here that might
help. If you're putting in a morning time,
| | 03:14 | simply by typing nothing after the entry,
for example, if it's 8:30 AM, if you
| | 03:19 | type 8:30, it's implied that's 8:30 AM.
| | 03:23 | You look in the formula bar. You'll see that.
| | 03:26 | If it's 8:30 PM, two ways to do this:
either type the 24 hour type style, which is
| | 03:32 | 20:30 and again, you can look in the
formula bar and see what's happening there,
| | 03:38 | or you could type 8:30 P. You could
type PM, but P is shorter, why not? And see
| | 03:45 | what's happening that way.
| | 03:46 | Of course, in a column like this you
would standardize the display by changing
| | 03:50 | the format, but do recognize the shortcut,
| | 03:53 | a small shortcut you wouldn't use too
often. If the time is within the first 10
| | 03:57 | minutes, and you want to make an entry
here, suppose it's, for example, 4:05 PM,
| | 04:03 | 4:5 space P, and you see that entry there.
| | 04:10 | It's important to note too that if
you are recording times that you're not
| | 04:15 | thinking of the clock, but you're
thinking of, for example, hours or hours and
| | 04:19 | minutes and seconds, suppose you want
to put in 30 minutes and 15 seconds.
| | 04:24 | You might type it this way,
but what's going to happen? How is this
| | 04:28 | going to be stored if you
mean 30 minutes and 15 seconds?
| | 04:32 | That's not what you want, and you look
in the formula bar, and it says 6:15 AM.
| | 04:38 | Type it differently;
| | 04:39 | type 30:15.0. Then the
information is actually stored that way.
| | 04:48 | There's a different use of
time in this situation here.
| | 04:51 | Whenever you're entering times,
definitely use the colon, and here and there, you
| | 04:55 | don't need to type in all the numbers.
| | 04:57 | You can certainly use P or A, as necessary.
| | 04:59 | Eventually, you want to explore some
formatting options to make these look
| | 05:02 | similar, and certainly if you're
entering not times of day, but times elapsed or
| | 05:08 | times spent on project and so on, you
want to use colons, and in some cases, if
| | 05:12 | you're dealing with minutes and seconds,
you actually want to put in a point .0
| | 05:16 | as well to tabulate that information.
| | Collapse this transcript |
| Using instant date/time entry| 00:00 | There are two quick and efficient ways
for entering dates and times in Excel,
| | 00:05 | and many times throughout the course of a
day you might be putting in the current date.
| | 00:10 | So at the date I'm recording this
particular movie - I am going to click right here -
| | 00:13 | I want to put in today's date, but
rather than typing it, I'm going to use a
| | 00:17 | great keystroke shortcut - not so
obvious but very fast: Ctrl+semicolon to put
| | 00:22 | in the current date.
| | 00:24 | This date will not change unless you
or I happen to actually change it.
| | 00:28 | So, it's going to stay there indefinitely.
| | 00:31 | I also use this too when I'm putting in
dates say nearby. Maybe I need to put in
| | 00:36 | July 29th, or July 18th, or whatever. Ctrl+semicolon
| | 00:41 | and then just a quick edit here to
change whatever entry I need to make a change
| | 00:45 | to, and we're all set. It makes it fast
and easy for putting in the current date.
| | 00:50 | For the current time,
it's going to be Ctrl+Shift+semicolon
| | 00:56 | and it'll put in the entry this way.
| | 00:58 | Here too, this will not change no
matter what you do, unless you type something
| | 01:02 | different here. And although it's less likely
to be needed as the current date, it's
| | 01:07 | handy, it's fast, does use
that same semicolon key, but it's
| | 01:10 | Ctrl+Shift+semicolon for the
time, Ctrl+colon for the date.
| | Collapse this transcript |
| Using TODAY and NOW functions for dynamic date/time entry| 00:00 | You may want to enter the current date in the
worksheet in such a way that it always adjusts,
| | 00:05 | in other words it's a Dynamic Date Entry,
even as you open and close and save the file.
| | 00:11 | In column B here, I want to put in the
current date, the date of this recording, and
| | 00:15 | this time I want to type
a function called today.
| | 00:19 | As with all functions, there is
no reason to capitalize these.
| | 00:21 | Although all functions are followed by
two parentheses, we need to only type the
| | 00:25 | left parenthesis here, then press Enter.
| | 00:29 | And that's the current date.
| | 00:31 | If I save this file and then open it
tomorrow, it's going to indicate July 20th,
| | 00:36 | and then open at a later date, it's
always going to be adjusting as needed.
| | 00:40 | It does answer that question that
sometimes people have when they say, "So I
| | 00:44 | open and close this file and sometimes
I don't even make changes, and yet I get
| | 00:48 | these prompts that say 'Do
you want to save your changes?'"
| | 00:51 | It's probably a function like this.
| | 00:54 | You might use this also in a way
to timestamp a certain worksheet.
| | 00:59 | So on this Profits worksheet,
maybe you have been working with this.
| | 01:04 | It's early in the year.
| | 01:05 | You're still adjusting some of these numbers.
| | 01:07 | And as you print it out various
copies, you might want to always have
| | 01:10 | the current date in it.
| | 01:11 | You want to put in the current date in
such a way that it's always reflective of
| | 01:16 | whatever the current date happens to be.
| | 01:18 | So =today(, Enter, and
there we have the entry there.
| | 01:24 | And that will adjust and change as
we print this worksheet, or whenever we
| | 01:28 | happened to be looking at this.
| | 01:30 | Similarly, we might want
to also put in a timestamp.
| | 01:35 | Back on this worksheet here,
Dynamic Time Entry is the function =now.
| | 01:40 | Now this actually, as you'll see here,
now( is all we need to type. It puts in
| | 01:46 | the date and the time.
| | 01:48 | And you possibly will leave this here if
you wish, or perhaps you'll format this.
| | 01:53 | And if you only want the time of course,
you could right-click and Format Cells.
| | 01:58 | You could also press Ctrl+1 to come
to the same dialog box, and while here
| | 02:03 | choose Time and then pick the Time
Style that's most conducive to your needs.
| | 02:08 | Perhaps this one here, 1:30 PM, that style.
| | 02:12 | So here we see a Dynamic Time Entry.
| | 02:15 | This will not change unless we do
something in the worksheet, and we're probably
| | 02:18 | not going to be watching it anyway, but
I do want to make the point that that is
| | 02:22 | a dynamic entry, and as we work with a
worksheet and make other changes, maybe
| | 02:27 | we put in some data over here somewhere.
| | 02:30 | Possibly, when I press Enter, this
time will have changed, as it did here.
| | 02:34 | I'm not going to be testing that
often, but it does reflect the idea that
| | 02:39 | this is a Dynamic Entry.
| | 02:41 | Now as we look at it now, we see what
the entry was, but it's a good way to
| | 02:45 | timestamp certain information, and
here, too, this has possibly use in
| | 02:50 | certain kinds of formulas.
| | 02:52 | Two quick ways to enter both Dynamic
Date Entry with the function =today, and
| | 02:58 | Dynamic Time Entry using the function =now.
| | Collapse this transcript |
| Using Auto Fill to enter date and time series| 00:00 | Excel's built-in Auto Fill capability
is going to save you a ton of time for
| | 00:04 | entering certain kinds of date series.
| | 00:08 | Recognize that on any given date,
for example, the date in column E2 here,
| | 00:14 | if we drag the Fill handle, that's the
lower right-hand corner of the active
| | 00:17 | cell, downward, we will
automatically get next day, next day, next day.
| | 00:21 | The pop-up tip on the right alerts us as
to the values that will be entered into
| | 00:26 | that series automatically.
| | 00:28 | And that just saves a lot of time by
itself, in many different situations.
| | 00:32 | If it turns out, in a slightly
different situation here, where a date has to be
| | 00:37 | entered twice, or maybe it's just a
single situation where you want a date in,
| | 00:41 | but you need to have it repeated,
| | 00:43 | if you hold down the Ctrl key when
dragging a date entry, you will, if you let go
| | 00:49 | with the mouse first, get the same date
repeated, and sometimes that's the case.
| | 00:53 | Maybe after this we want to drag it
down without the Ctrl key. We'll get the
| | 00:57 | next day, next day, and so on.
| | 00:58 | So you could have a situation, maybe
slightly different heading, where you do
| | 01:02 | have repeat dates that way.
| | 01:04 | In creating a date series, for example, in
column A we see two different date entries.
| | 01:10 | These happened to be Sundays.
| | 01:11 | They are seven days apart.
| | 01:13 | Let's highlight both of them
and then drag from the corner.
| | 01:17 | Whatever that difference is, in this case
seven, we're going to get that repeated.
| | 01:22 | So this is our series of Sundays, and
of course, it could be any day of the
| | 01:26 | week, and the interval
could be anything you want.
| | 01:28 | If it's a different kind of working
environment, and we have a different
| | 01:31 | heading, and it's a different work-
cycle, maybe the first two dates if they
| | 01:35 | happened to be five apart or ten apart.
| | 01:38 | Let's just use an example here, and of
course, we would have a different heading
| | 01:40 | under this situation, but
here is a 10-day interval.
| | 01:44 | Drag here, and what do we
get? A 10-day interval.
| | 01:46 | But as far as we drag this, well into
the future, and there could be even
| | 01:50 | situations where you're highlighting
these two cells and you drag upward.
| | 01:54 | And the general rule on Auto Fill is as
you drag downward you proceed with the
| | 01:59 | series, if we drag rightward it
operates in the same sequence.
| | 02:03 | Rarely, but occasionally in these
situations where you drag upward or leftward,
| | 02:08 | but then it works in a reverse direction.
| | 02:11 | Sometimes you need to put in
dates in a consistent kind of way.
| | 02:14 | For example, suppose you have a mid-
month reporting date, and you've got one on
| | 02:19 | the 15th of January, and you'd simply
like to fill this in with the series of
| | 02:25 | the 15th of every month.
| | 02:27 | Two ways to do this: We can either
put in February 15th right here, /11,
| | 02:32 | highlight both and then drag from the corner.
| | 02:34 | So let's do it that way, 2/15/11.
| | 02:38 | Highlight both of these, drag from the
corner, we automatically get next day,
| | 02:43 | next day, next day, and that of course,
can carry on into different years if we wish.
| | 02:48 | Another way to do this, not a very
likely way, but perhaps a better way, in one
| | 02:52 | respect, is to drag the Fill
handle with the right mouse button.
| | 02:58 | Now as you do this, the
pop-up tips are misleading.
| | 03:01 | That's not the date that we're
looking for, but when we let go of the mouse
| | 03:05 | here, we do get a choice along this to
fill this with Months, and we'll get the
| | 03:10 | information that way.
| | 03:12 | Suppose you have a situation
for end-of-month reporting.
| | 03:15 | If any entry that you start with
happens to be the last day of a given month, as
| | 03:20 | it is here, right drag here, hold
down the right mouse button, drag here to
| | 03:25 | where we're getting a misleading pop-up.
| | 03:27 | As soon as we let go with the mouse,
the menu Fill Months and what's this going
| | 03:32 | to do? Give us the last day of every month.
| | 03:36 | If we had started with January 30th,
we would have gotten some different
| | 03:39 | answers here and there.
| | 03:40 | So this automatically picks up the
last day of the month, and you see how it
| | 03:44 | does of course, pick up
| | 03:45 | on the fact that there are 29 days
in February, in 2012, so we see how that
| | 03:50 | proceeds nicely with the right mouse button.
| | 03:53 | And earlier, we've brought out the fact
that when you drag a date simply with
| | 03:56 | nothing, no keys held down
and the standard left-drag,
| | 04:00 | we automatically get next
day, next day, next day.
| | 04:03 | But maybe you only want to track weekday sales.
| | 04:06 | So that's a weekday, Monday through Friday.
| | 04:09 | Let's drag with the right mouse button
here, not necessarily sure how far this
| | 04:13 | needs to be drag, but when we let go
with the mouse, we do get a choice that
| | 04:17 | says Fill Weekdays.
| | 04:20 | So that first one is a Sunday.
| | 04:22 | So Monday, Tuesday, Wednesday, Thursday,
Friday, no Saturday, Sunday, and then
| | 04:27 | we get Monday, Tuesday, Wednesday,
Thursday, Friday, and so on, for as far as
| | 04:30 | we might drag this.
| | 04:32 | So there are quite a few quick ways to
enter date series in Excel using Auto Fill.
| | 04:37 | And we can also use this for times of day.
| | 04:41 | When you're tracking information, now this
could be maybe making observations every
| | 04:45 | 15 minutes or something
like that, every 11 minutes,
| | 04:48 | in other words, put in two entries of a
certain interval, highlight both of them
| | 04:53 | and drag from the corner, and with time
entries, as you might expect, we are going
| | 04:58 | to get coherent answers here, for
example, in this case, every 11 minutes.
| | 05:02 | So you can create numerical series this
way as well, and even we'll cross hourly
| | 05:08 | boundaries as we see here.
| | 05:09 | So the same general idea here, but
putting in information, if we simply drag
| | 05:14 | one of these by itself, for example, drag this
without any keys held down or anything special,
| | 05:21 | see in this case, we
automatically get a one-hour interval.
| | 05:25 | And what happens if we
put in, for example, 8:30?
| | 05:29 | Drag from the corner, and
we get these options there.
| | 05:33 | So as with times, although not nearly
as expansive as with dates, we have some
| | 05:37 | quick ways for entering series of data.
| | Collapse this transcript |
|
|
3. Date/Time FormattingExploring keystroke shortcuts| 00:00 | When you're working with dates and
times in Excel, there certainly are times
| | 00:03 | when you need to change the
display of either dates or times.
| | 00:08 | In column B here, nothing wrong with
this certainly, but a different way of
| | 00:12 | displaying these dates can be gotten
into quickly - either by pressing Ctrl+1 or
| | 00:18 | right-clicking on the selected data and
choosing Format Cells, and then in the
| | 00:23 | Format Cells dialog box, in the
Number category, under Date, quite a few
| | 00:29 | variations, quite a few
different ways to display these dates.
| | 00:33 | Sometimes you're in a hurry, and if you
like this particular format you're about
| | 00:37 | to see, it's a great keystroke shortcut.
| | 00:39 | It's the only one associated
with dates. It's Ctrl+Shift+#.
| | 00:44 | It's on the number 3 key,
displays the information this way.
| | 00:50 | A lot of people like this because it
eliminates that momentary doubt that you
| | 00:54 | have sometimes about which
month we're talking about here.
| | 00:57 | This clarifies that instantly.
| | 01:00 | It also helps if you're dealing with
data from other countries where many times
| | 01:04 | they use the slashes in a different
order, the month-day order, than we do.
| | 01:08 | So this quickly cleans up the data this way.
| | 01:11 | With regard to times themselves,
these times here have been formatted to
| | 01:16 | be 24-hour type style.
| | 01:19 | And here, too, by selecting the data,
perhaps pressing Ctrl+1 or right-click,
| | 01:24 | we'll go right into Format Cells, and
this time of course, we would be checking
| | 01:28 | out various time formats that are built-in here.
| | 01:31 | Quite a few variations there as well.
| | 01:34 | There is one keystroke shortcut
associated with times that's going to be
| | 01:39 | Ctrl+Shift+@, maybe you'll remember that
by saying, well, I'll meet you at 3 o'clock.
| | 01:45 | Ctrl+Shift+@, and this
display uses the AM/PM style.
| | 01:51 | And for many people, that's ideal.
| | 01:53 | Others prefer the 24-hour style, but
this is the only keystroke shortcut
| | 01:56 | associated with time.
| | 01:58 | So two quick keystroke shortcuts.
| | 02:01 | For time, Ctrl+Shift+@, and as
we've mentioned previously for
| | 02:04 | dates, Ctrl+Shift+#.
| | 02:07 | Both formats widely used and
easy to read for most people.
| | Collapse this transcript |
| Formatting time for hours over 24| 00:00 | If you're working with time values and
you're trying to tabulate some totals,
| | 00:04 | you're likely to run into a sticky
little problem that should be better
| | 00:08 | documented, but it's not.
| | 00:10 | One the worksheet that we are looking
at here, we see that some entries are
| | 00:13 | being made in column B, and we
are about to show the total here.
| | 00:17 | And of course, we would like to be
able to do to make this as fast as
| | 00:20 | possible is to use the AutoSum button,
which is available either on the Home
| | 00:24 | tab or on the Formulas tab.
| | 00:26 | So on the Home tab, we could just
click AutoSum and many of you know you can
| | 00:29 | click it once, and it serve it.
| | 00:31 | Click it again, and we get an answer.
| | 00:34 | And right away, of course, we are
seeing that answer is not correct.
| | 00:38 | If we highlight the values in
question, the Status bar in the lower
| | 00:42 | right-hand corner is giving us the correct total:
27 hours and 38 minutes in this example here.
| | 00:49 | As soon as you see the Total here, you
might do a quick bit of math in your head
| | 00:54 | and recognize that this is only, in effect,
showing us hours beyond 24, which is not
| | 01:00 | really what we want here.
| | 01:02 | So we need to change the format.
| | 01:04 | Now, I am not sure that that's an
obvious think to say, but let's do change the
| | 01:07 | format by right-clicking
and going into Format Cells.
| | 01:11 | And here is where Microsoft, I
believe, has dropped the ball.
| | 01:15 | Which of these formats are we likely to use?
| | 01:18 | Any help out here, as we
click on the various choices? No.
| | 01:22 | What choice will we make?
| | 01:24 | It might be process of elimination.
| | 01:26 | If you choose the one that has a 37 in
it, this is the one we will be using.
| | 01:31 | Now the sample here does give us a preview.
| | 01:35 | So maybe indirectly, we do have some help here.
| | 01:38 | Looking at this too, we might say, well
yeah that's going to give us the correct
| | 01:41 | answer, but isn't it
giving us seconds as well? Yes.
| | 01:46 | If that doesn't bother us, fine.
| | 01:47 | We will just click OK, and end of
discussion, and we will just explain it to
| | 01:51 | others, but that's the answer.
| | 01:53 | Probably what we should have done
and certainly can do is go back into
| | 01:58 | Format Cells by right-clicking and
take this given format, jump into the
| | 02:03 | Custom category here.
| | 02:06 | And in the Display panel up here under
Type, get rid of the trailing ampersand and the
| | 02:11 | seconds, simply by doing this.
| | 02:13 | You'll notice that in that format, as we
saw earlier, the h within brackets means
| | 02:19 | it will allow us to tabulate hours
over 24. None of that is displayed here on
| | 02:24 | the screen, but that is the
explanation of how this particular format works.
| | 02:29 | If you need to deal with hours above
24, you'll need to use this kind of a
| | 02:35 | format, and you'll find this particular
format, at least initially as we saw it,
| | 02:39 | in the Time category.
| | 02:40 | So all we are doing now is taking that
format and customizing it and then clicking OK.
| | 02:46 | That's probably the way we would
want to have this information displayed.
| | 02:50 | And if these entries change and go above 48,
this will continue to work as well, too.
| | 02:55 | So it works indefinitely for all hour
entries above 48, a special format for
| | 03:01 | those kinds of situations.
| | Collapse this transcript |
| Creating custom date formatting| 00:00 | Excel has quite a few different
ways to display date information.
| | 00:05 | In the worksheet that we are looking
at here, the dates in column B, for
| | 00:10 | example, are certainly displayed in more
or less typical kind of way, and simply
| | 00:13 | by right-clicking or pressing Ctrl+1, we
can go right into Format Cells and find
| | 00:18 | a wealth of choices there.
| | 00:20 | But don't overlook the Custom category.
| | 00:23 | This can be a little bit intimidating
if you're scrolling up and down here and
| | 00:26 | you're looking at all those crazy symbols.
| | 00:28 | But you will recognize, here and there,
some others. Perhaps you've created them,
| | 00:33 | but you'll see Ms and Ys here.
| | 00:36 | Using the panel right here, we can, for
example, display all kinds of different
| | 00:41 | variations using four Ms for a full
spelling of a month if we wish, or three
| | 00:47 | Ms to abbreviate it.
| | 00:48 | We can put in day of the week, using
four Ds for a full spelling, three Ds
| | 00:53 | for an abbreviation.
| | 00:54 | So maybe on these sales dates, just for
clarity, we might want to put in day of the week.
| | 00:59 | Let's just put in, for example,
| | 01:01 | three Ds to abbreviate the day of the week.
| | 01:04 | We might want to add a comma here.
If you want to spell out the month,
| | 01:08 | three Ms to abbreviate it, four to
spell it out, and put in a single day. D will
| | 01:14 | put in the day of the month, using one
or two digits as necessary, comma, maybe
| | 01:19 | four Ys, if you want to put in the full year.
| | 01:22 | So all kinds of variations
using Ms and Ds, and Ys.
| | 01:26 | So what we're about to see -
| | 01:27 | and we see the sample up above here too,
as to what we are about to see for these dates.
| | 01:31 | So you can easily make your own changes.
| | 01:34 | That certainly takes up a lot more space,
but it does reveal day of the week, and
| | 01:38 | maybe that's important to
know in certain situations.
| | 01:41 | So all kinds of variations, as we see here.
| | 01:45 | And within a given year, don't
overlook the idea, too, that it's redundant
| | 01:49 | perhaps in the example here, to
show the actual year, so why not
| | 01:54 | first take a look at, under Format
Cells, some of the built-in date variations
| | 01:58 | here, for example, this one right here.
| | 02:01 | And this will just simply display
the information without the year.
| | 02:04 | That could be just fine.
| | 02:06 | And as always, if you wanted to
include with that the day of the week, then
| | 02:11 | simply jump into the Custom category.
| | 02:14 | It will carry this format over there,
and we can see how that's being displayed,
| | 02:19 | and in front of that we will just put in,
for example, maybe four Ds this time
| | 02:23 | and a comma to display that information.
| | 02:27 | So it certainly worth experimenting with.
| | 02:29 | It's easy to get to, and you can
imagine using this in a variety of
| | 02:32 | different situations.
| | 02:33 | Sometimes day of the week
is really important,
| | 02:35 | you want to see that, either a full
spelling or abbreviation; sometimes seeing
| | 02:39 | the months spelled out has value, as well.
| | 02:42 | So custom formatting for
date entries makes good sense.
| | Collapse this transcript |
|
|
4. Excel's Date/Time FunctionsEOMONTH and EDATE: Setting ends of months and future/past dates| 00:00 | Excel has a number of functions that allow
you take advantage of Date & Time entries.
| | 00:06 | On the Formulas tab in the Ribbon is
a category referred to as Date & Time.
| | 00:10 | If you click this button, here
are quite a few different functions.
| | 00:14 | Let's talk about two functions that
are going to help you calculate end of
| | 00:18 | month, or monthly anniversary-type entries.
| | 00:22 | In column A are various dates, and
depending upon the situation that's set up
| | 00:27 | here, we want to have a report that's
going to be due at the end of the month, a
| | 00:32 | few months after this. Maybe it's
two months. Maybe it's five months.
| | 00:35 | Let's say it's a three-month anniversary report.
| | 00:38 | These reports are always
due with the end of the month.
| | 00:41 | So what we're looking for here is two
months later, in this case November, but
| | 00:46 | we wanted the last day of November
here, and certainly there are ways to
| | 00:50 | calculate formulas here. But let's take
advantage of a function in Excel called
| | 00:56 | eomonth, end of month.
| | 00:59 | As always, when you're typing functions,
no need to capitalize them, eomonth we
| | 01:03 | are looking at this type as our starting
date, and we want the end of the month,
| | 01:08 | two months out, let's say. Put in the number 2.
| | 01:12 | So two months later, of course, is
November, and the end of the month is here.
| | 01:16 | And as we copy some of these down the
column, and I will just double-click to
| | 01:19 | do this a bit faster,
| | 01:20 | we will see what's happening in each case.
| | 01:23 | In each example here, on the result here,
we are talking about two months later,
| | 01:28 | but the last day of the
month, at the end of the month.
| | 01:32 | And you can even go to the other direction, too.
| | 01:34 | That's less likely to be needed, but if
you're looking back two months, and I am
| | 01:37 | just going to copy this date over here,
| | 01:40 | similarly we could indicate a function
here, =eomonth referring to this month
| | 01:46 | here, three months previous to this,
but not just three months previous.
| | 01:50 | The last day of the month is three
months previous, so -3 in this case.
| | 01:56 | So we see the entry there, and if
it's not formatted properly, a simple
| | 01:59 | right drag of the bottom of any edge of the
cell onto the format here. Copy the formats.
| | 02:05 | There we are. So again, this function here is
showing us three months previous to this, but
| | 02:12 | the last day of the month,
eomonth, meaning end of month.
| | 02:16 | At other times, we want to look ahead,
usually ahead although, also backward, for
| | 02:21 | certain anniversary-type dates, and
maybe this refers to a set of employees, and
| | 02:26 | there is a 3rd Anniversary here.
| | 02:28 | This could mean three months;
| | 02:30 | it could mean three years.
| | 02:31 | We can go either way here.
| | 02:33 | But the function we are talking
about in this example here will be edate.
| | 02:39 | This is our starting date, three months
out, three months from this, comma 3, the
| | 02:47 | 15th of October, and in the
other examples, as we will see here,
| | 02:52 | in every single case, to the day
we see the three month average.
| | 02:56 | Do be aware of certain situations here,
for example, if we are going to get an
| | 03:00 | answer in February, or in a short month
like that, or even September, for example,
| | 03:05 | what if we start here with a date -
| | 03:07 | let me see if we can come up the
right difference here - on one of these?
| | 03:10 | Let's actually put in, for example
here, the 31st of January, and of course
| | 03:18 | there is no 31st of April,
so it comes up with 30th here.
| | 03:20 | So you see how that's playing out.
| | 03:22 | And you could imagine here also, if the
heading were different and we needed to
| | 03:26 | know three months
previous or three years previous,
| | 03:30 | in this case put in a minus, if it's 3 months.
| | 03:34 | So we can calculate backward in time,
| | 03:36 | here we go from July back into April.
| | 03:39 | And of course for years, you'll have
to either do the math or actually put in
| | 03:43 | the number of months.
| | 03:44 | So if we are talking about three years
here, we are either going to type in 36,
| | 03:48 | or 3*12, whatever fits your needs and
of course we will get the date here, out
| | 03:53 | of 2013 in this example.
| | 03:57 | Exactly three years.
| | 03:59 | So good uses for this function, edate
and previously mentioned eomonth, built-in
| | 04:04 | date functions in Excel.
| | Collapse this transcript |
| DATEDIF: Calculating date differences by year, month, day, and more| 00:00 | A powerful date function, but one that
is strangely not present under Date &
| | 00:05 | Time as we click on the Formulas tab
categories here for Date & Time functions,
| | 00:11 | is a function called DATEDIF.
| | 00:13 | It's not here, and yet, I am using
it here in column G, and here how it's
| | 00:18 | being used in cell G3.
| | 00:20 | We are trying to calculate how long a
person has been in this organization.
| | 00:25 | I am using a function called TODAY,
and at the time of this recording, it's
| | 00:30 | mid-July of 2010, and you can see
clearly here, this person has been here seven
| | 00:36 | years, and notice that there is a Y out here.
| | 00:38 | The DATEDIF function requires that we
begin with a starting date, and then in
| | 00:44 | this case an ending date, which is
actually the dynamic entry, TODAY's date.
| | 00:49 | This is the function TODAY
that's using today's date.
| | 00:52 | You notice below that there is no
prompting for arguments here, unlike
| | 00:56 | typical Excel functions.
| | 00:58 | So this is a strange thing indeed.
| | 01:00 | Now, as I press Enter here, and you
saw the result earlier, it is a 7, and as
| | 01:05 | you look at some of the other examples
here, again, imagine we are talking about
| | 01:08 | mid-July of 2010. The anniversaries
are working, and it does work to the day,
| | 01:13 | exactly the way we use
anniversaries and birth dates.
| | 01:18 | In addition to Y, we could also use M to
calculate months or D to calculate days.
| | 01:24 | There are some other variations on this, as well.
| | 01:26 | So in a different worksheet here called
DATEDIF Examples, let's calculate, for
| | 01:31 | example, the number of months a
particular project or a person that we are
| | 01:36 | referring to in columns A and B.
We have got a time lapse between
| | 01:39 | the two entries here.
| | 01:40 | So DATEDIF, left parenthesis, starting
date in this case is in cell A2, comma,
| | 01:47 | the ending date is in B2.
| | 01:49 | This time we want to calculate the
number of months, "m", and a
| | 01:54 | quick double-click here
will copy this down the column.
| | 01:57 | So we see in the other examples here,
and here is one we can calculate most
| | 02:00 | easily with our eyes, just looking at
dates within the year, now 2011, that's
| | 02:05 | a six month interval.
| | 02:07 | Notice that it's almost seven months,
but well, not quite, and that's 27 days,
| | 02:12 | so it's six months and 27 days.
| | 02:14 | Even if it were six months and one day short,
it's still going to indicate six months.
| | 02:19 | We are calculating the difference here using m.
| | 02:21 | Now, we could do this with days, but
that's just more easily achieved simply by
| | 02:26 | subtracting the two.
| | 02:28 | But there are two other
ways we could use this as well.
| | 02:30 | We might want to know how many days
after a certain monthly anniversary, perhaps.
| | 02:35 | Now, that might sound a little contrived,
but what if we were trying to tabulate
| | 02:39 | a date difference here between these two days,
as before, the one in A2 and the one in B2.
| | 02:48 | This time we want to know how
many days it's been since the last
| | 02:52 | monthly anniversary.
| | 02:54 | So within double quotes now, we will
put in "md", the number of days after the
| | 03:01 | monthly anniversary: eight days from
the twentieth to the twenty-eighth. And we
| | 03:06 | can see what's happening in
other examples as well here,
| | 03:09 | the number of days after
the monthly anniversary.
| | 03:12 | We can also do this, meaning the
number of days after the last yearly
| | 03:16 | anniversary. Put in a Y here.
| | 03:19 | So this could be as high as 364,
and we see the entries in here.
| | 03:24 | So I can see the example here.
| | 03:26 | We're three shot of a year here, as we use 'yd'.
| | 03:30 | So, various uses for this.
| | 03:31 | Again, I think it's most effectively
used in the first example that we saw.
| | 03:36 | In a dynamic database, you always want
to know how many years someone has been
| | 03:40 | here, but you don't want to have
to constantly recalculate this.
| | 03:44 | So using the TODAY function as the later date,
this allows us to tabulate a higher date.
| | 03:50 | And certainly in a different kind of
database, where you have a birthday in,
| | 03:54 | you would have a column called age,
and this would calculate age to the day.
| | 03:59 | And once again, it's a little bit
strange in that you do not find documentation
| | 04:03 | on this function in Excel.
| | 04:05 | You will see it in various Excel
books, but not within Excel itself.
| | 04:09 | But a great tool for calculating
differences in a variety of different
| | 04:13 | ways between dates.
| | Collapse this transcript |
| WEEKDAY: Determining the day of the week| 00:00 | Knowing day of the week can be
valuable information in a variety of
| | 00:03 | different situations.
| | 00:05 | For example, date of a sale, or maybe
you are just analyzing product shipments
| | 00:10 | or orders - knowing day of the week is
something you need to know sometimes.
| | 00:15 | In column B, we can put in a function
called weekday to tabulate this information.
| | 00:22 | By the way, if you're not interested
necessarily in tabulating the information
| | 00:25 | but simply seeing the information, we
could format the entries in column A to
| | 00:31 | include the day of the week being displayed.
| | 00:34 | But if we need to analyze this, we will need
to use this function referred to as weekday.
| | 00:39 | We are looking at the data here.
| | 00:43 | Now, the result that we get here
is not going to make us too happy:
| | 00:47 | 6. That doesn't tell us a whole lot at first.
| | 00:50 | Now, I could quickly explain that 1
means Sunday, 2 means Monday, 6 therefore is
| | 00:55 | Friday, 7 is Saturday, and so on.
| | 00:57 | We could leave it this way if it
sticks on our head, in the same way that
| | 01:01 | numbers might remind us of certain months.
| | 01:03 | But a better follow up to this will be
to actually display this by simply right
| | 01:09 | clicking and choosing Format Cells.
| | 01:12 | We can quickly create a special Custom format.
| | 01:15 | 3 Ds will abbreviate the day of the week,
as we see up above, Fri. Four Ds will
| | 01:23 | spell out the entire day of the week.
| | 01:26 | And we could simply double-click
here to copy it down the columns.
| | 01:29 | So if we need to see this being
displayed, we can see it this way with
| | 01:33 | the weekday function.
| | 01:34 | You see how it's working here.
| | 01:35 | It's the day of the week.
| | 01:37 | There is an obscure setting here too,
that if you want the numbering system to
| | 01:40 | start at 0 and go through 6,
you can do that as well.
| | 01:43 | There are some options there, but I think
most people use it directly in this way.
| | 01:47 | Now, from this point, of course, it's
up to you to decide how you might be
| | 01:51 | using it, but you can also take
advantage of this function if you are trying
| | 01:55 | to indicate due dates and you don't
want them to be, for example, Saturdays
| | 01:59 | and Sundays, you might use the If
function. If your knowledge of that
| | 02:03 | function is good enough,
| | 02:04 | you can also use the weekday function
there to determine whether a date is on
| | 02:09 | this date or that day of the week;
| | 02:11 | the weekday function.
| | Collapse this transcript |
| NETWORKDAYS: Calculating working days| 00:00 | As you work with dates in Excel, it
becomes useful and very helpful at times to
| | 00:05 | understand how much time has elapsed
between a starting and an ending date, and
| | 00:10 | whether it has to do with a
project or an anniversary.
| | 00:13 | It's easy and straightforward in
Excel to subtract cells that have dates to
| | 00:17 | figure out the amount of days elapsed.
| | 00:20 | Equal, the later date, in this case D3,
December 2, minus the starting date, in
| | 00:26 | this particular example, June 15. 170 days.
| | 00:32 | That's certainly useful information.
| | 00:34 | But in a working environment, many
times you would like to be calculating the
| | 00:37 | number of working days.
| | 00:40 | We don't want to count the weekends.
| | 00:42 | So in cell E4 here, working
with the same dates, let's use the
| | 00:47 | function networkdays.
| | 00:54 | The starting date, June 15, comes
first, comma, the ending date, and for the
| | 01:01 | moment we will ignore the holidays,
but we do want know how much time has
| | 01:04 | elapsed between the two dates, not
counting the weekends.
| | 01:09 | And instead of 170, it's
considerably shorter: 123.
| | 01:13 | It's important to note here that this is
counting both the starting date and the ending date.
| | 01:18 | So if you are tabulating differences
between dates using this function - if, for
| | 01:23 | example, this starts on
noon, and this ends on noon,
| | 01:27 | that would be one day less than this,
and so you have to decide here and there,
| | 01:31 | whether you want to
subtract one from an answer here.
| | 01:33 | So that's an inclusive count of
working days, just Mondays through Fridays.
| | 01:38 | But surely, many times, as in this case,
there are holidays involved as well.
| | 01:44 | So here is a list of holidays, and
it's not for the entire year, although it
| | 01:46 | certainly could be, but let's say it
encompasses the relevant dates that we
| | 01:50 | are talking about here.
| | 01:51 | Let's use the network function again.
| | 01:54 | I am simply going to copy this over
here into column F and then edit this
| | 01:58 | by double-clicking.
| | 01:59 | Let's do now include following the F3
here, the reference to the End Date, comma,
| | 02:06 | and now highlight the relevant holidays cells.
| | 02:10 | We can highlight some or all of them;
it doesn't make any difference.
| | 02:12 | That's good enough right there.
| | 02:14 | Now, instead of a 123 working days, we have 119.
| | 02:20 | So it's a lesser count because we
are not including the holidays here.
| | 02:25 | Now, some of these holidays, for example,
July 5 is a Monday, July 4 is a Sunday.
| | 02:30 | This was never counted to begin with,
so we do run into that situation.
| | 02:33 | So we are not counting these holidays
right here for this particular example.
| | 02:39 | So anytime you are trying to
tabulate the actual working days with a
| | 02:43 | starting and ending date and
holidays are involved, you want to use the
| | 02:47 | function networkdays.
| | Collapse this transcript |
| WORKDAY: Calculating an ending date| 00:00 | If you are trying to calculate future
dates, as you might, for example, with a
| | 00:04 | project length, it will be helpful to
know how many days into the future the
| | 00:09 | actual date will be.
| | 00:10 | For example, suppose you started a
project on May 27 in 2010, and it's estimated
| | 00:16 | that it's a 120-day project.
| | 00:19 | Certainly, 120 days difference is easy
to calculate in Excel, but I think, almost
| | 00:24 | immediately, you would recognize a shortcoming.
| | 00:26 | Simple formula here will be equal the
starting date, which is in D2, plus 120
| | 00:34 | to give us this date.
| | 00:36 | How many days is 120 days
from this date? There it is.
| | 00:42 | But this is an actual day count.
| | 00:44 | It includes weekends.
| | 00:46 | What we want to do is
count the actual working days.
| | 00:49 | So we use a function called workday.
| | 00:55 | The Starting Date is that May 27 date, comma,
| | 01:01 | here is the Project Length, 120 days.
| | 01:03 | We are ignoring Holidays for the moment,
but let's not count the weekend days,
| | 01:07 | and that's what happens
with the workday function.
| | 01:09 | Instead of this project ending on
September 24, the real ending date is going to
| | 01:16 | be on November 11, in 2010.
| | 01:18 | But there are some holidays involved as well,
and in column A, you see holidays listed.
| | 01:23 | It includes just the
relevant holidays and a few more.
| | 01:27 | So let's perform this
same calculation again here.
| | 01:30 | I will just copy this into column F.
You want to do this calculation again, but
| | 01:34 | this time take into account the holidays.
| | 01:37 | So that 120-day project begins on May 27.
| | 01:42 | And you don't work weekends, nor do you
work holidays, and here is your list of
| | 01:46 | holidays over here in column A.
We can just highlight these.
| | 01:49 | What will be the target or the end date now?
| | 01:53 | It's going to be on November 16, in 2010.
| | 01:56 | So we see how the workday function
allows us to calculate actual working days,
| | 02:01 | not including weekends, and where
appropriate, not including holidays, which are
| | 02:06 | listed on your worksheet.
| | Collapse this transcript |
| DATEVALUE and TIMEVALUE: Converting text entries into dates and times| 00:00 | When you download data from other
sources, sometimes you end up with date and
| | 00:04 | time information that's in text format.
| | 00:08 | And although it appears sometimes that
you can perform occasional math on these
| | 00:12 | functions by adding numbers to them,
you can't always format the information
| | 00:16 | properly, and you need to convert it into dates
and time so you can properly use them in Excel.
| | 00:22 | We are talking about two different
functions related, very similar name,
| | 00:26 | DateValue and TimeValue.
| | 00:28 | Let's take a look at how this might work.
| | 00:31 | The data in column A is in text form.
| | 00:34 | There are four variations on it.
| | 00:35 | These are text entries.
| | 00:37 | And we can't do as much with them as
we might want to in Excel, in terms of
| | 00:41 | manipulating them and using
them in a mathematical way.
| | 00:44 | So using column B here, let's put in
=datevalue, simply referring to
| | 00:50 | this cell over here.
| | 00:53 | And we are likely to end up with a date number.
| | 00:56 | I might do this for the rest of these as well.
| | 00:58 | And you may recall there are a couple
of quick ways to quickly change this into
| | 01:02 | a visible date; Ctrl+Shift+# is one of them.
| | 01:06 | If you don't care for that format, the
main point here is you can see how the
| | 01:10 | data has been converted into dates.
| | 01:12 | Another variation, of course, is simply
right-click, go into Format Cells, pick
| | 01:15 | one of the available entries there
under Date, whatever suits your needs,
| | 01:19 | for example, this one, if
you like the two digit year.
| | 01:24 | In situations like this, when you are
using a different column or different
| | 01:28 | set of cells to translate the data, at
some point you do reach a point where
| | 01:32 | you say, in effect, I'd like to keep these
results and effectively throw away the formulas.
| | 01:37 | There are a couple of quick ways to do this.
| | 01:38 | The best way, and probably the fastest
way, is simply to select the data that has
| | 01:43 | the formulas in them, and with the
right mouse button, if you don't need the
| | 01:46 | original data, drag this on top of
the old data, point to any edge, use the
| | 01:53 | right mouse button, drag on top of the old data.
| | 01:56 | And as you let go of the mouse,
| | 01:57 | you'll get a prompt, Copy Here as Values Only.
| | 02:02 | And here too, unfortunately,
you will have to reformat them.
| | 02:05 | And again, if you like the format with
the Ctrl+Shift+#, fine, use that, or if
| | 02:10 | not, right click, go to
Format Cells and change it.
| | 02:13 | We can get rid of this data here by
pressing Delete, and there we are.
| | 02:18 | These are now Excel dates.
| | 02:19 | We can perform math as we need to on these.
| | 02:23 | Also, sometimes you download data with
times that have been treated as text.
| | 02:27 | Here's some entries here, and the
function in these two cases will be =timevalue.
| | 02:34 | Now, there are other approaches to this.
Possibly if you are adept with text
| | 02:39 | functions, you could use these.
| | 02:40 | But these two functions, DateValue and
TimeValue, give us quick ways to take
| | 02:44 | data here, like this, and
convert it into data that we can use.
| | 02:49 | Here too, as with dates, we
need to make a conversion.
| | 02:52 | Keystroke shortcut here, if you like
the particular style, is Ctrl+Shift+@, and
| | 02:59 | once again, if that's not appropriate
for your needs, right click, Format Cells,
| | 03:03 | pick one of the other available Time
formats, for example, this one here, the 24
| | 03:07 | hour style, if that's
what you are interested in.
| | 03:09 | So different ways to convert data by
using DateValue for dates, TimeValue
| | 03:15 | for times.
| | Collapse this transcript |
|
|
5. Calculating with Dates and TimesCalculating date differences across days, months, and years| 00:00 | Calculating date differences in Excel
is generally straightforward, whether you
| | 00:04 | are talking about existing
dates or looking into the future.
| | 00:08 | In columns A and B, we are
tracking some equipment, when it was installed,
| | 00:12 | when the expiration date actually occurred.
| | 00:14 | We want to tabulate how
many days elapsed in each case.
| | 00:18 | Simple formula, equal, the cell that
contains the later date, minus the cell
| | 00:24 | containing the earlier date.
| | 00:25 | And the answer will be in days, 1,524.
| | 00:31 | Double-click will copy this down the column.
| | 00:33 | We have our answers for every single case here,
| | 00:35 | and whether it's within the same
year, or crosses yearly boundaries, or
| | 00:39 | century boundaries.
| | 00:40 | And of course, this can work well
into the future, easily capturing the
| | 00:44 | difference between days.
| | 00:46 | If you need to tabulate years, you probably
want to take a look at the datediv function.
| | 00:51 | This is simply for
calculating days by subtracting.
| | 00:55 | At other times, you have situations
where you want to set a future date, or
| | 00:59 | possibly a date in the past as well,
but in the example in column E here, we
| | 01:04 | have starting dates.
| | 01:05 | We want to know when the review date is due.
| | 01:07 | If this represents the starting date
for an individual, who has a 90-day review
| | 01:12 | that's due on a certain date.
| | 01:14 | Simply equal the date that we
are starting with, plus a value, 90.
| | 01:22 | There's our answer, 90 days into the future.
| | 01:25 | We will simply double-click
to copy this down the column.
| | 01:28 | So we see the result in each case, 90 days.
| | 01:31 | And of course that can be any number we want.
| | 01:33 | And if we did have to have an entry
here that looked back 90 days, we'd
| | 01:37 | simply subtract 90.
| | 01:39 | So using date math is simple
and straightforward in Excel.
| | Collapse this transcript |
| Calculating time differences within and across days| 00:00 | Calculating time differences in Excel is
very similar to calculating date differences.
| | 00:06 | And it's generally straightforward, as
long as the starting and ending times are
| | 00:09 | within the same day.
| | 00:11 | But we want to talk about some more
difficult situations, as well as the
| | 00:14 | straightforward situation, for
example, that we are seeing in column C.
| | 00:18 | The starting and ending times in this
example are all within the same day.
| | 00:22 | We simply want to know how much
time has elapsed between the two times.
| | 00:27 | Equal the ending time minus the earlier time.
| | 00:32 | And we see our answers here.
| | 00:34 | Just double-click to copy this down the column.
| | 00:37 | How much time has elapsed
between those two times of day?
| | 00:41 | If this happens to be, and we can see
by the examples this wouldn't represent
| | 00:45 | working time, but let's suppose we
had a situation where you arrived in the
| | 00:49 | office at 8:41 in the morning.
| | 00:52 | You might type it this way, just as 8:41.
| | 00:55 | And if you hadn't formatted that, you
might even put some strange things there.
| | 01:02 | And you left the office on this given
day at 5:03 PM. You might type it this way.
| | 01:10 | And there too, copy the format from
above, make it a little bit more readable,
| | 01:14 | with the right drag formats. 8:22.
| | 01:18 | What if you say, well, yes,
but I took 45 minutes for lunch.
| | 01:21 | We'd like to subtract that.
| | 01:23 | How are we going to do that?
| | 01:24 | Well, you might put it
out here on a separate cell.
| | 01:26 | And how do we type 45 minutes? 0:45,
| | 01:33 | let's simply now take
this information, minus this.
| | 01:39 | This is certainly one way to do this.
| | 01:41 | So this actually was 7 hours and 37
minutes of we might call, billable time.
| | 01:46 | If you want to embed this within the
formula itself, you want to subtract "0:45"
| | 01:58 | to get the same answer.
Then you wouldn't need this.
| | 02:00 | So depending upon circumstances, you might
want to proceed with that kind of a formula.
| | 02:05 | It doesn't have the flexibility as the
other one, but it can be done this way.
| | 02:09 | Another way to do this more awkward is
if you have a full understanding of how
| | 02:14 | Excel calculates times, you could say,
well, if it were an hour, we could be
| | 02:18 | subtracting 1/24 of a day.
| | 02:22 | So if this were an hour, we
could put in, in parentheses, 1/24.
| | 02:27 | 45 minutes is 3/4 of that, so you
might either do some math in your head or
| | 02:31 | figure out that that's 1/32 of a day.
| | 02:34 | So we are getting a little bit weird
here perhaps, but that's certainly doable.
| | 02:38 | 1/32 of a day is 45 minutes.
| | 02:41 | So we could do it that way, as well.
| | 02:43 | So different approaches to
calculating time differences.
| | 02:47 | There are also situations where you
want to find an ending time by adding.
| | 02:53 | So maybe we want to know 45
minutes later here, or an hour later.
| | 02:57 | Equal this, plus, once
again, what is the time frame?
| | 03:03 | If it's an hour, remember, we don't want to
add 1, because that's a day. We could add 1/24.
| | 03:11 | And here too, we probably
would want to copy the format.
| | 03:14 | Easy way to do this is to right drag any
edge, pop it right over here, Copy Here
| | 03:19 | as Formats, and so 1 hour later in this case.
| | 03:22 | If we wanted to do this the way we
formally had done this in column C, by adding
| | 03:27 | 45, or an hour in this case, we could
put in "0:60", and we get the same answer.
| | 03:38 | So different approaches to
coming up with situations here.
| | 03:42 | What if we have something
involving time over different days?
| | 03:47 | Imagine a situation where the computer
system or telephone system went down on a
| | 03:52 | certain day, so it went down on
November 17, 2010 at 9:30 p.m.
| | 03:59 | So you could type it that way if you wish.
| | 04:02 | That's one of many
different ways we could type this.
| | 04:04 | And the system was back in business maybe
two days later, on the 19th, and at 6:30 am.
| | 04:16 | How much time has elapsed?
| | 04:18 | So we want to tabulate the
difference between the two times here across a
| | 04:23 | roughly two-day period, day-and-a-half
period, equal the later time, minus the
| | 04:29 | earlier time, and we get this answer.
| | 04:33 | We need to format this as an actual time.
| | 04:36 | Right-click > Format Cells > Time, and
we want to use the 13:30 format here.
| | 04:45 | So how much time has elapsed? 9 hours.
| | 04:49 | You may remember, or you may have seen
situations where if you are tabulating
| | 04:53 | times that exceed 24 hours, we don't
always get a correct answer in Excel.
| | 04:57 | So what we need to do here, also, is
to format this with a time format
| | 05:03 | that includes the 37.
| | 05:06 | If you don't want to see the seconds,
immediately jump into Custom here and
| | 05:10 | strip off the seconds at the end here.
| | 05:13 | So how much time has elapsed here?
| | 05:15 | 33 hours between the two times, across three
different dates, but involving only 33 hours.
| | 05:23 | So you can actually tabulate
date and time differences and time
| | 05:27 | differences across multiple days by
calculating the difference and then
| | 05:32 | adjusting the formats.
| | Collapse this transcript |
| Calculating fiscal years and quarters| 00:00 | If you're tracking data across multiple
months, and possibly even multiple years,
| | 00:05 | you may want to track data by
quarter and/or by fiscal year.
| | 00:10 | In column E here, we see an FY entry,
Fiscal Year, and it's simply the year
| | 00:15 | that we see in the actual date that's
in column C. The function in place in E2
| | 00:20 | is simply the Year function. It picks up
the year from the actual data entry, and
| | 00:26 | of course we can do this with any year, and
we see simple, straightforward answers here.
| | 00:31 | Quarter is a little trickier, and you
may not need this if, for example, if
| | 00:35 | you're using a PivotTable, you could
take advantage of the innate grouping
| | 00:39 | capabilities of the PivotTable to
automatically calculate quarterly
| | 00:43 | information for you.
| | 00:45 | But if you need to see this here, you're
going to have to write some kind of a formula.
| | 00:48 | Now, this isn't the only solution to
this, and it might be a little tricky if
| | 00:52 | you've never seen Roundup, but this
is one way to calculate the quarter.
| | 00:57 | It too uses a function that you
might not have seen, like Year, but it's
| | 01:02 | called a Month function.
| | 01:03 | In the particular example here, this
actually comes up with the number 3.
| | 01:07 | So you can imagine, of course, the first
three months are in the first quarter,
| | 01:12 | the second three months are in
the second quarter, and so on.
| | 01:16 | So this calculation simply allows us to
calculate the quarter of any entry that
| | 01:22 | we see in column C. And a quick review
of some of the results that we see here
| | 01:26 | is showing that it does work properly.
| | 01:29 | January and February, first
quarter, so is March, of course, that's
| | 01:31 | what's turning up here.
| | 01:33 | December is in the fourth
quarter. We see this here.
| | 01:36 | September is in the third quarter, and so on.
| | 01:38 | We see how this is being done.
| | 01:40 | In some situations though, your
fiscal year doesn't match up with the
| | 01:45 | chronological year, and then it
gets a little bit more involved.
| | 01:49 | So suppose, for example, as within
the federal government, suppose that the
| | 01:53 | fiscal year begins in October, and
the first quarter there is October,
| | 01:57 | November, December.
| | 01:59 | Now, a different function, and I would
admit this is a little bit involved here
| | 02:04 | and a little bit tricky.
| | 02:05 | Instead of this, we could have used
something here, but let's shift the focus
| | 02:09 | into the idea that we're using a
fiscal year that's completely different.
| | 02:13 | We're going to use a function called Choose.
| | 02:18 | The Choose function takes a value, for
example, the month that we have here.
| | 02:24 | So we'll use this function here called Month.
| | 02:27 | Pull the Month out of here, and
the first example is going to be a 3.
| | 02:32 | If the month is 1, meaning January, or
February, or March, then it will be in
| | 02:38 | the second quarter.
| | 02:40 | So the Choose function, after we
provide it with an index number, then we're
| | 02:45 | going to provide 12 separate answers here.
| | 02:49 | If the answer is 1, 2, or 3, meaning
January, February, or March, then this is
| | 02:55 | in the second quarter.
| | 02:56 | So we put in three 2s.
| | 02:59 | Then for April, May, June, that's
going to be third quarter, and then July,
| | 03:04 | August, September will be fourth
quarter, and then October, November, and
| | 03:07 | December are going to be first quarter.
| | 03:10 | Again, this is how the federal
government uses this, and I think a lot of other
| | 03:13 | governments are as well.
| | 03:15 | So as we press Enter here,
then make some changes.
| | 03:21 | So March, for example, as well as
February and January, as we see here,
| | 03:26 | these are all in the second quarter
if the fiscal year begins in October.
| | 03:31 | And those October, November, December dates,
as we're seeing here, are in the first quarter.
| | 03:37 | So here's the function.
| | 03:38 | It's the Choose function.
| | 03:39 | Now, going back to our previous example,
a straight chronological layout, we
| | 03:44 | could be using this function and simply
have three 1s, then three 2s, three 3s,
| | 03:49 | three 4s, et cetera, left or right,
straightforward, but as long as this is, even
| | 03:53 | though it is long, it does give us
the answer pretty quickly and easily.
| | 03:57 | With fiscal year, it's going
to be a little bit trickier.
| | 04:00 | Let's say that we would want to use
the year in all cases here, except when
| | 04:04 | we're in the first quarter.
| | 04:06 | In other words, this is
actually fiscal year of 2012.
| | 04:10 | So we just start off with the
function year, as we're saying here.
| | 04:13 | This is actually 2012 for the fiscal year.
| | 04:17 | So what we need to do in this example
here is to put in an If to check to see if
| | 04:25 | this quarter equals 1, and if it is, we
want to use the year of that date, plus
| | 04:34 | 1, comma; otherwise, we simply
want to use the year of this entry.
| | 04:49 | The reason this result has a minus in
it is that I inadvertently typed a minus
| | 04:53 | right after the equal sign.
| | 04:54 | I didn't mean to do that.
| | 04:55 | So I'm going to take it out, and
it will give the correct answer.
| | 05:00 | As we copy this, we copy it up to the
previous cell and then down from here.
| | 05:04 | So you see what's happening in the entries here.
| | 05:09 | For those that are in all quarters,
except for the first quarter, the year that
| | 05:13 | we're seeing for the fiscal
year matches up with the date here.
| | 05:17 | I've seen lots of different uses of
fiscal years, and they start at different
| | 05:21 | monthly boundaries than October, so
you'll have to make your own adjustments
| | 05:25 | depending upon the environment you're
working in, but we see how it's playing
| | 05:29 | out in the examples here, where we
actually come up with a new fiscal year if it
| | 05:34 | doesn't match up with the chronological year.
| | 05:37 | So there are quite a few ways to work
with fiscal year data if it doesn't follow
| | 05:41 | those chronological boundaries.
| | Collapse this transcript |
| Rounding time calculations to convenient intervals| 00:00 | If you're working with data that has
starting and ending dates and times,
| | 00:04 | sometimes you want to not only
calculate the difference, but you want to round
| | 00:09 | that difference to a convenient number.
| | 00:12 | The date information in columns A and B
also include times, and the variances, in
| | 00:16 | many cases, extend over multiple days.
| | 00:18 | So we can simply subtract the 2, equal
the later time, minus the earlier time,
| | 00:25 | to get an answer here, and double-
click this to copy it down the column.
| | 00:29 | So 66 hours, 110 hours. This one's
within the same day. It's 8 hours, and so on.
| | 00:34 | We see what's working here.
| | 00:36 | That might be exactly what you need.
Fine. End of discussion, possibly.
| | 00:40 | But you might want to round these
results in different kinds of ways.
| | 00:44 | So the headings up here
suggest what we're about to do.
| | 00:46 | Perhaps less likely in this
environment, unless the numbers are huge, round
| | 00:50 | this to the nearest day.
| | 00:52 | In all three cases here, although
there are multiple possibilities for using
| | 00:57 | different functions, the one function
that's going to help us in all three of
| | 01:00 | these situations is the one called mround.
| | 01:03 | mround, different than round, allows
you to round numbers to multiples that are
| | 01:10 | not necessarily powers of 10.
| | 01:13 | So what we want to do in this example
here is to take that same difference, the
| | 01:19 | ending date and time minus the earlier
date and time, comma, and round it to the
| | 01:25 | nearest day. Simply putting in
the unit 1 will do this for us.
| | 01:30 | So 66 hours is closest to 3
days, which would be 72 hours.
| | 01:36 | 110 hours coming up.
| | 01:39 | You can probably figure that out.
It's going to be 4, actually 5.
| | 01:42 | It's 10 hours away from being 120, and so on.
| | 01:45 | So I'll copy this down.
| | 01:47 | And in this case, of course,
that's 0 to the nearest day.
| | 01:51 | Again, in certain situations I think
you can see how this could be valuable,
| | 01:53 | particularly if the numbers are
quite large in terms of hours.
| | 01:57 | Now, what if we wanted to
round this to the nearest hour?
| | 02:00 | Again, equal mround. In this case, we
want to take that same difference and
| | 02:09 | round these to the nearest 24th of a
day, 1/24, and you can see the difference
| | 02:16 | there, 66 instead of 66 hours and 10 minutes.
| | 02:19 | So once again, double-clicking to
copy this down the column, we see what's
| | 02:23 | happening in the other case that's here.
The 29 is almost 30, but not quite.
| | 02:27 | So it goes back to 110, and we see
what's happening in the other cases as well,
| | 02:31 | rounding to the nearest hour, and
again, this is how the function works.
| | 02:34 | We're rounding to the nearest 1/24 of a day.
| | 02:38 | Looking ahead to round to the nearest
15 minutes. Two ways to approach this.
| | 02:43 | You could say, well, that's 1/4 of 1/24,
and you could do some math in your head
| | 02:48 | and figure that one out.
| | 02:50 | It's going to be 1/96th.
| | 02:53 | So we want to round this way.
| | 02:54 | This is probably not the best way for
most people, because you always have to
| | 02:58 | explain what it means if someone else
is looking at it, but double-clicking
| | 03:03 | we'll see that it does work.
| | 03:04 | The results are rounded
to the nearest 15 minutes.
| | 03:07 | A better way to do this for
documentation purposes would be, instead of the
| | 03:12 | 1/96th, double quote, and then 0:15,
certainly more readable, and I think anybody
| | 03:21 | looking at it would say, well, oh yeah, now
I get it, and we'll change all these at once.
| | 03:26 | So again, displaying it this way, to
the nearest 15 minutes, and that's what's
| | 03:31 | done in all these cases here.
| | 03:33 | And you can certainly adapt this and
adjust it to other kinds of amounts
| | 03:37 | that you might want to round time
calculations to: 15 minutes, 10 minutes,
| | 03:42 | 30 minutes, whatever.
| | 03:43 | Good, quick, easy ways to
deal with time and math.
| | Collapse this transcript |
| Using times with currency calculations| 00:00 | If you're working with time information
and you need to combine it with currency
| | 00:04 | calculations, sometimes these appear
to be a little bit problematical, but
| | 00:08 | actually they are pretty
easy and straightforward.
| | 00:10 | Look at the data in columns B and C. In
cell D2, we want to know how much pay is
| | 00:17 | deserved here if someone has
worked 5 hours at $10 an hour.
| | 00:20 | So we know the answer ahead of time. It's $50.
| | 00:23 | So we do the math of =B2*C2.
| | 00:29 | And our answer is, well,
it's not $2.08. We know that.
| | 00:34 | What needs to be done here?
| | 00:36 | A little bit of reverse engineering.
Might think this out a little bit.
| | 00:39 | You know it's got to be $50.
| | 00:41 | How does that $2.08 relate to it?
| | 00:44 | And what really is the difference here?
| | 00:46 | When we are dealing with times, keep
in mind the idea that in Excel a time is
| | 00:50 | a portion of a day.
| | 00:52 | And the more you think about that, the more
you would say, yes, that's right, 24 hours.
| | 00:56 | We need to take this calculation
and multiply it by 24. There you go.
| | 01:03 | There's our $50.
| | 01:05 | And we'll double-click and
copy this down the column.
| | 01:07 | And check out another one here and there.
| | 01:10 | Some of it might be easier to do than others.
| | 01:11 | There's one, $20 an hour, 8 hours, $160.
| | 01:15 | So you can see how the math is handled there.
| | 01:17 | At first, it throws you, but once you see
a solution here, it makes perfect sense.
| | 01:22 | What if you've got some kind of a
computer system or some kind of a mechanical
| | 01:26 | system and downtime is really critical.
| | 01:28 | And when there is downtime, and
here we are talking about minutes.
| | 01:32 | It costs you so much per minute.
| | 01:34 | You want to know what that costs.
| | 01:36 | So in this situation too, first thought
might be, well, we'll just multiply these.
| | 01:40 | Equal this downtime, times this rate.
| | 01:44 | It should be around $25 or so, or
$30, but obviously not $0.02 here.
| | 01:50 | So something is off.
| | 01:51 | What do we need to do here?
| | 01:52 | We are talking about minutes here.
| | 01:55 | So how many minutes are in
a day? 24*60. It's 1,440.
| | 02:03 | So times.
| | 02:04 | Now, for better documentation, you might put
in 24*60, or simply put in 1,440, as you choose.
| | 02:13 | So 24*60 or 1,440.
| | 02:15 | It doesn't make any difference.
| | 02:17 | That's an answer that makes sense.
| | 02:19 | And in the second one, for example, you
can see what's about to happen there, and
| | 02:22 | some of the others as well too.
| | 02:24 | Double-click to copy this, readjust
the column width. So there we are.
| | 02:28 | When we are multiplying data like this,
we need to factor in the idea that
| | 02:33 | there are so many minutes in a day, and we
have to put in that additional factor there.
| | 02:37 | So two different ways to deal with
currency information when used with
| | 02:42 | time data.
| | Collapse this transcript |
| Calculating holidays (Labor Day, Thanksgiving, Memorial Day, etc.)| 00:00 | When working with Excel data, sometimes
you need to know ahead of time which day
| | 00:05 | of the year certain holidays will appear.
| | 00:06 | For example, Memorial Day, Labor Day,
and Thanksgiving all occur on certain
| | 00:11 | days of the week, but not always the same date.
| | 00:15 | On the screen here we see the
various Memorial Days, Labor Days, and
| | 00:19 | Thanksgivings for the next five years or so.
| | 00:22 | And in each case, behind the scenes
here in column C are your formulas.
| | 00:25 | Now, I am going to press Ctrl+Tilde, or
from the Formulas tab, there is a button
| | 00:31 | called Show Formulas.
| | 00:34 | And these formulas are a bit long,
and I certainly don't intend to explain
| | 00:37 | them in detail, but just a quick look at one
or two of these will give you some insight.
| | 00:41 | And for your benefit, simply feel
free to copy these, but take a case, for
| | 00:45 | example, of Labor Day.
| | 00:46 | You probably know that Labor Day is
always the first Monday in September.
| | 00:51 | Without going into all the details of
what are the formulas, but let's say the
| | 00:55 | formula right here in cell C7,
| | 00:57 | you'll notice in here that the way
this works - in general, without the
| | 01:01 | details - is that it focuses on the
number 2 here, the weekday value being
| | 01:07 | 2, which is Monday.
| | 01:10 | And so we see how this is being compared,
whether it's greater than two, we have
| | 01:15 | one kind of formula;
| | 01:16 | otherwise, we have another kind of a formula.
| | 01:17 | I'll leave it up to you to go over the
details yourself, but these do work and
| | 01:22 | for Labor Day, not only Labor Day here,
but also Memorial Day, which is the last
| | 01:27 | Monday in May, and Thanksgiving,
which is the fourth Thursday in November.
| | 01:32 | See, these formulas are all similar,
as you can see, but a little bit tricky,
| | 01:36 | I wouldn't say you have to
understand them in detail, but do feel free to
| | 01:38 | use them whenever you need to calculate these.
| | 01:41 | For other holidays, of course,
they are fixed dates, like July 4th and
| | 01:45 | Christmas on December 25th, and
New Year's on January 1st ,and so on.
| | 01:49 | But if you are also looking around,
trying to calculate your own formulas, for
| | 01:52 | your own given holidays, if they
happen to fall on certain Mondays or
| | 01:56 | Wednesdays, whatever,
| | 01:57 | you can use formulas like
this as a starting point.
| | Collapse this transcript |
|
|
6. Using Dates and Times with Excel CommandsUsing special date filters with date data| 00:00 | When you work with database data,
applying a filter makes great sense because
| | 00:04 | sometimes you don't want to see all of the
data, and it's easy to filter data by date.
| | 00:10 | In this particular worksheet, which has
about 700 rows, let's apply the filter.
| | 00:14 | On the Data tab, click the Filter button,
under the Sort & Filter group, and the
| | 00:20 | arrows emerge for each column.
| | 00:22 | If we wanted to filter this data based
on department entry, a lot of you know by
| | 00:26 | clicking the drop arrow here, we
recognize some text filters are in place.
| | 00:30 | If it's a numeric field, for example Salaries,
| | 00:33 | one of the arrows there, we
have number filters in place.
| | 00:38 | If it's a date field, as we see in
column F for Hire Date, we have date filters,
| | 00:44 | and the number of date filters,
as of version 2007, is substantial.
| | 00:50 | If this is current sales data, it's
real important to isolate just the data
| | 00:55 | from last month, for example, or last week,
yesterday even. That certainly makes sense.
| | 01:02 | Lots of nice groupings.
| | 01:03 | Last Quarter, and something that we
couldn't have done so easily in prior
| | 01:08 | versions also brand-new here, Year to
Date, and All Dates in the Periods, we
| | 01:14 | can get to data by month or by quarter.
| | 01:16 | If we are looking at Hire Dates, maybe
we wouldn't be thinking necessarily about
| | 01:20 | month, but we might just jump into
here and say, let's look at all the people
| | 01:23 | hired in the first quarter.
| | 01:25 | I think maybe with sales data that might
be a more crying need, but
| | 01:28 | nevertheless, we see the data here.
| | 01:31 | And of course these are just
January, February, March entries.
| | 01:34 | Let's also not overlook, in the
Filtering capability here, when dealing with
| | 01:38 | dates, we certainly could select the
existing filters as we see them here, by Year.
| | 01:45 | Maybe you just want to view those
entries from 2002, and maybe another year or
| | 01:51 | maybe not. Click OK.
| | 01:52 | We are only seeing the entries for that year.
| | 01:56 | So a lot of this, if not self-
explanatory is getting fairly close.
| | 02:00 | Now, you can also choose
dates between certain dates.
| | 02:06 | So we might want to view just those
employees hired, and we could put in the
| | 02:10 | starting date here of January
1 of 2002, and before 1/1/2005.
| | 02:19 | So in effect, we want to see all the
people hired in 2002, 2003, and 2004.
| | 02:25 | Again, in most cases, fairly self-explanatory.
| | 02:28 | And also with the Filter here, of course,
you can click the drop arrow and sort
| | 02:31 | them in ascending or descending
order, oldest to newest, as they are
| | 02:35 | appropriately located here, too.
| | 02:38 | So that's easy to get to, and
again, almost self-explanatory here.
| | 02:41 | And let's do bring out the fact that
in these date filters we can, as we
| | 02:46 | suggested here, for example, we
want to know anniversary dates.
| | 02:50 | Let's find all the people
hired in July. There they are.
| | 02:55 | Doing this in prior versions was not so easy.
| | 02:57 | It's relatively straightforward in 2007,
| | 03:00 | as we use some of the date aspects of
our data to isolate this month or that
| | 03:05 | month, any of the data on the
basis that we have seen here.
| | 03:08 | Again, quite a lot to explore here
under date filters using the filter capability
| | 03:14 | of Excel.
| | Collapse this transcript |
| Using date controls in data validation rules| 00:00 | A big problem with database data is
bad data from the start, and the Data
| | 00:06 | Validation capability in Excel is
quite adept at giving you tools for
| | 00:11 | controlling information
before it goes into a worksheet.
| | 00:14 | Let's imagine how we can use this
effectively with date type data.
| | 00:20 | And we are talking about
two possible situations here.
| | 00:22 | Let's imagine column A is all
by itself here on this worksheet.
| | 00:25 | We'll ignore the other data.
| | 00:27 | We want to make sure that the dates that
go in here fall within a certain range.
| | 00:31 | Now, possibly you might have a
restriction that says they must follow or must be
| | 00:35 | after a certain date, or before
a certain date, maybe even both.
| | 00:40 | With Data Validation, it's often best
if you use a column, although that's
| | 00:43 | not always appropriate.
| | 00:45 | It depends upon the nature of the
data, and how much you are going to have.
| | 00:48 | Let's just say that in column A, you
are going to make sure that any date that
| | 00:52 | goes in here meets our requirements.
| | 00:54 | So on the Data tab, we
want to click Data Validation.
| | 00:59 | And under Allow, let's choose Date.
| | 01:04 | And immediately, we get
the choice, choosing between.
| | 01:06 | Now, that may be what we want, or
possibly depending upon the circumstances,
| | 01:10 | we just want to make sure that they occur
after a certain date or before a certain date.
| | 01:15 | So we might pick between here and maybe
if we're recording some old data here,
| | 01:19 | we want to make sure that the only
entries that occur in this list are between
| | 01:24 | 1/1/2010 and 12/31/11, inclusive;
all dates must fall within this range.
| | 01:39 | So I'll put in a date or two, 3/4/10.
| | 01:42 | That's good enough.
| | 01:43 | I'll also do something here, obviously a typo.
| | 01:46 | Of course, we get this message, and
we'll retry, and obviously there's no 41st
| | 01:53 | date, but there is a 31st date.
| | 01:56 | But sure enough, we do want to just
test it out for a couple of dates here and
| | 02:00 | there, beyond the range for sure -
| | 02:02 | for example, this. And we
get that same kind of message.
| | 02:05 | You can customize the message
too, if you want to pursue that.
| | 02:08 | But this does the work.
| | 02:09 | It keeps out the bad entries, those
typos, or those impossible dates, or dates
| | 02:14 | outside of the range.
| | 02:16 | And similarly, in column F, but a
slightly different use over here.
| | 02:20 | In this example, we want to make sure
that the shipping dates for these orders,
| | 02:25 | first of all, appear after the order data.
| | 02:28 | That would make sense.
| | 02:30 | And at the same time, we might even add
a further restriction that says it must
| | 02:34 | be more than 3 days or 2 days,
whatever, after the Order Date.
| | 02:39 | So let's say that in column F here
we want to apply Data Validation, and
| | 02:45 | the choice here under Settings and Allow is
not Date, but Custom, meaning custom formula.
| | 02:54 | And the formula we use here wouldn't
be obvious, but once you see it, it
| | 02:57 | will make perfect sense.
| | 02:59 | The formula will be =F1, even though,
literally, there is no data in F1.
| | 03:07 | By inference what we mean here is
any entry in column F, we want it to be
| | 03:11 | greater than whatever the entry is in
column E+2, because literally, F1 is not
| | 03:22 | greater than cell E1/2.
| | 03:25 | When we press OK, we get this message that says
that it evaluates to an error. We'll ignore it.
| | 03:32 | We do want to continue, Yes.
| | 03:34 | So let's check this out.
| | 03:36 | Could we put in September 3 here?
| | 03:38 | No, that doesn't work.
| | 03:43 | It's not more than two days.
| | 03:45 | And how about the 4th?
| | 03:46 | That's two days, but it's
not greater than two days.
| | 03:51 | So let's try this again.
| | 03:53 | Let's put in the 5th.
| | 03:54 | So now it's going to work.
| | 03:56 | So in every case, no matter where it is,
throughout column F, whenever we make
| | 04:00 | an entry here, it must be
more than two days after this.
| | 04:04 | Now, the formula, again, jumping
back here, just a quick look at it again
| | 04:09 | to remind ourselves,
| | 04:10 | the Data Validation Formula =F1>E1+2.
| | 04:15 | In other words, we use the active
cell in the formula to come up with the
| | 04:21 | appropriate formula in this situation.
| | 04:23 | Now, Shipping Time, we didn't
talk about time, but this works -
| | 04:27 | let's say in this example we want to
make it work in the same way that we did
| | 04:30 | over in column A, but this time we're
talking about time, and the idea here is
| | 04:35 | the Shipping Time stands by itself.
| | 04:37 | Let's say that within this organization,
all shipping is done between 8 a.m. and
| | 04:42 | 6 p.m., that's it, and that's what
those recorded times must be recorded as,
| | 04:47 | within that time frame.
| | 04:49 | So the Data Validation rule here will
be to Allow > Time, and between, if we do
| | 04:58 | all our shipping from 8 a.m., 8:00 a,
that's good enough, and then the ending
| | 05:05 | time, 6:00 p. That will work.
| | 05:10 | It has got to be between 8 a.m. And 6 p.m.
| | 05:13 | So here is an 8:30 entry,
because we don't type anything else,
| | 05:17 | it's automatically a.m.
| | 05:19 | But if we try and put in, for
example, 7, that's beyond the range.
| | 05:25 | So good use of using built-
in Data Validation rules.
| | Collapse this transcript |
| Using date functions in data validation rules| 00:00 | If you are familiar with Excel Date
functions, you can use them in a creative
| | 00:04 | way, along with Excel's
Data Validation capability.
| | 00:08 | In this particular worksheet, there
is a Data Validation rule in place in
| | 00:12 | column B, and that rule is that
no entry in column B can be within two
| | 00:19 | days of the Order Date.
| | 00:20 | And the way that formula
is stated here, =B1>A1+2
| | 00:27 | refers to all entries in column B.
They must be more than two days later than
| | 00:31 | the Order Date in column A.
| | 00:34 | It so happens that in setting this up
maybe you weren't thinking about when
| | 00:38 | that shipping date is.
| | 00:39 | What day of the week is this? =weekday.
| | 00:43 | This function will tell us that,
and if it's a 1, it means it's Sunday.
| | 00:47 | Well, you don't do shipping on Sunday.
| | 00:49 | So you want to make sure
that this date is not a Sunday.
| | 00:53 | Now, its already been entered, so we'll
either have to manually take it out or
| | 00:55 | correct it, but let's say that you
want to change the rule to also include
| | 01:00 | this limitation here, that the
weekday is not equal to 1, and furthermore,
| | 01:06 | maybe you're thinking about Saturdays as well.
Maybe you don't do shipping on Saturdays.
| | 01:10 | So let's change the rule here.
| | 01:12 | So in column B, we are going to
use Data Validation and alter this rule.
| | 01:17 | Now, we want the existing rule still to be true.
| | 01:19 | We want to make sure that the
shipping date is more than two days after the
| | 01:24 | order date, but we want to couple
this with an additional requirement.
| | 01:29 | So we put in the word "and," meaning we
want a series of conditions to be true.
| | 01:35 | The first one is the one we have
already mentioned, about the two-day
| | 01:37 | difference, comma, then
let's use the weekday function.
| | 01:43 | As we type this, we don't need to capitalize it.
| | 01:45 | The weekday of - and even though we're
referring to B1, by inference we mean all
| | 01:51 | entries in column B - the weekday of
this entry cannot be equal to 1, the less
| | 01:57 | than arrow followed by the greater than arrow.
| | 01:59 | This will make sure that
the entry is not a Sunday.
| | 02:03 | And if it can't to be a Saturday
also, then we put in another weekday.
| | 02:08 | Also, B1, right parenthesis, less than, greater
than, meaning not equal to 7. 7 means Saturday.
| | 02:16 | So now it's three criteria.
| | 02:18 | I am going to copy this and redisplay
it, so we can see it better. Click OK.
| | 02:25 | Once again, this evaluates to an error,
simply because literally B1 and A1
| | 02:30 | don't fit our formula. That's fine.
| | 02:31 | We'll just continue anyway.
| | 02:32 | The function that I type right here,
and I'll display it here, is simply this,
| | 02:37 | and you typically don't see it
that way, but that's the way it looks.
| | 02:41 | So this is already here.
| | 02:42 | We can't make a change, but if I tried
to make this be the 12th, which should be
| | 02:46 | the following Sunday, that's not going to work.
| | 02:49 | Obviously, that's more than two days beyond.
| | 02:51 | So maybe I'll just hit Esc. Now, it does go back
to the 5th, so it doesn't disrupt that data, but if I change
| | 02:57 | it to the 7th, which is okay, and then
later come back and try and change it to
| | 03:02 | the 5th, this will not work.
| | 03:07 | Again, sometimes this is just perhaps
using your imagination, or knowing some of
| | 03:11 | the limitations of Excel, but the
more Date functions you know, the more
| | 03:15 | possibilities you have to use this in
combination with other Excel features.
| | 03:20 | So in this example, the shipping dates
here must be more than two days after
| | 03:25 | the order date, and they cannot be equal to
Sunday, and they cannot be equal to Saturday.
| | 03:31 | So a lot of creative screening going
on here using the weekday function in
| | 03:36 | combination with Data Validation rules.
| | Collapse this transcript |
| Converting unusually formatted dates into usable data (text to columns)| 00:00 | When you download information from
other sources, sometimes you end up with
| | 00:04 | date formats, or date entries, that are not in
a good Excel format that you can do much with.
| | 00:10 | Now, some of these are a little unusual,
but yet there are techniques in Excel
| | 00:14 | to handle and change date information.
| | 00:17 | Think of situations where you might
have thousands and thousands of entries.
| | 00:21 | In columns A, D, and G, we see three
different kinds of date layouts here.
| | 00:26 | All of them are a little bit unusual perhaps,
but they all make sense in a certain way.
| | 00:31 | If we look at the data in column A,
after looking at this for a bit, I think you
| | 00:34 | recognize that the year is embedded in
here, but the first two characters are
| | 00:39 | the day of the month, then a
four-digit year, then the actual month number.
| | 00:44 | So let's convert this data into
something we can work with effectively in Excel.
| | 00:49 | The best way to do this is to select the
entire column, when appropriate, or the
| | 00:53 | data in question, and go to the
Data tab, and choose Text to Columns.
| | 01:00 | This sounds as if we might be
splitting it into separate columns, but
| | 01:05 | within this feature, and when you
come to it, you'll see it's a so-called
| | 01:09 | wizard with multiple steps.
| | 01:11 | In Step 1, simply click Next, and then
Next again, and in Step 3, you'll see a
| | 01:19 | choice here called Date with a drop arrow.
| | 01:23 | As we see the display here, the
choices that we see, the day appears first,
| | 01:29 | that's represented by D,
then the year, then the month.
| | 01:32 | So DYM is the choice we want to make here.
| | 01:35 | Let's click Finish, and that first date
should be 4/28/2006, and that's exactly
| | 01:42 | what we see, and of course the
others have played out nicely as well.
| | 01:46 | In column D, a different layout,
same approach, Text to Columns.
| | 01:51 | Here too, right into Step 3 of the
wizard, and the choice here will begin with
| | 01:57 | the year, that's going to be Y,
then the month, then the day.
| | 02:02 | So YMD right there, and Finish,
and the same dates, same layout.
| | 02:10 | Now, in column G, this looks a little different.
| | 02:12 | We've got hyphens here.
| | 02:13 | Now, you might be tempted
to replace the hyphens first.
| | 02:17 | That might not be a bad idea, but let's
just try Text to Columns, because they
| | 02:21 | are delineated here.
| | 02:23 | We can read this a lot more
easily than the other two.
| | 02:25 | Let's try Text to Columns here as well.
| | 02:28 | Go right into Step 3, and then the example
here, it's day, year, month, DYM, click Finish.
| | 02:41 | And now the date is in shape, as
it is in the other columns as well.
| | 02:45 | We saw three examples
here of unusual date layouts.
| | 02:48 | And a quick effective way is to
change them into readable dates in Excel by
| | 02:53 | using the Text to Columns capability
in the Data Tools group on the Data tab.
| | 02:58 | Great capability for quickly
converting a lot of data into readable Excel
| | 03:04 | date information.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | In this course, we've shown you a
variety of techniques for handling date
| | 00:04 | and time information.
| | 00:05 | We've given you the background on
how Excel stores date and times, how to
| | 00:09 | quickly create date and time entries,
how to format that information quickly,
| | 00:14 | how to use vital important functions
that deal with date and time, and also some
| | 00:19 | calculations involving
currency and rounding tips.
| | 00:23 | This is information that is going to serve
you well as you work with Excel in the future.
| | 00:27 | Thank you for watching.
| | Collapse this transcript |
|
|
