Excel & Business Math 38: Date Math, Invoices, Cash Discounts on Invoices, Nested IF Functions

Excel & Business Math 38: Date Math, Invoices, Cash Discounts on Invoices, Nested IF Functions


Welcome to Excel in Business
Math, video number 38. Hey, in this video, we got to
talk about date math, invoices, and cash discounts on invoices. And this is going to be an
epic video, because we’ll get to see for the first
time one of the most amazing features in Excel– date math and the ability to
make formulas based on dates. We’ll also talk
all about invoices. And the bulk of our calculating
and the formulas that we’ll see in this video will
be all about calculating cash discounts. That means getting a discount
if you pay your invoice early. Now we got to start off by
going to the sheet, Date Number Formatting and Calcs. The very first
thing we have to do is talk about how to
enter dates in Excel. Type a proper date. Now I’m going to type
today’s date, 3 slash– and there it is, a slash. As soon as I do that, I’m
telling Excel this is a date. I could type a dash, also. But I’m going to do a
slash, 10 slash 2018. Now I hit Enter. Notice as we talked about in
one of the very first videos in our class, I think it was
video 4, the default alignment for numbers are to the right. And so we know that
when we enter a date, it’s actually a number. Now since typing today’s
date is so common in Excel, especially when entering
historical transactions, there’s the keyboard. I’m going to use the
keyboard, Control-Semicolon. I love that. Enter. Now we got to be careful
just as we talked about earlier in the class. If we mistakenly
type 28/2/2018, I’m intending to type
February 28, 2018. When I hit Enter,
that shows up a line to the left, which means you
know Excel thinks it’s text. Now if you have your regional
settings set to show day and then month, like in some
countries other than the United States, then that
would be a proper date. Now you can check
your regional settings by clicking on Start button,
or simply hitting the Window key and then type region. And there it is,
Region and Language. If you click on
that, there it is. There’s what’s defining
how Excel interprets dates. If you change it, for
example, to German right here, you can clearly see
the day is first. Now I’m going to click Escape. Now I want to notice something. And I’m going to uncollapse
my ribbons up here. Right-click. Uncheck the check box
here, or use Control-F1. That’s a toggle
for your ribbons. Notice the cell B7 has
general number formatting. Watch what happens
when I type a date. I’m going to use
Control-Semicolon. I can still see it
grayed out up here. But as soon as I Control-Enter,
yes, when you type a date, date number
formatting is applied. Now that’s how to enter dates. Now let’s talk about
date number formatting and what’s really going on. I’m going to enter today’s date. Control-Semicolon and Enter. I’m going to do it again. Control-Semicolon. Control-Enter. Now notice there’s date. What happens if we use our
eraser, the general number formatting, either
from the dropdown or by using our keyboard Control
Shift Grave Accent Tilde. What? 43,169? Yes, date number formatting
means under the dates, there are serial numbers. That’s the fancy word
they use to describe that, which are the number of
days since December 31, 1899. That means the number
1 is actually 1/1/1900. That means the very first
day in Excel’s history is January 1, 1900. Now historians don’t like
this, because historians use lots of dates
before January 1, 1900. But when they designed
the spreadsheet, they intended people to do date
math on invoices, and loans, and things like that. And so they didn’t
think that there was many invoices or loans that
were outstanding before January 1, 1900. Now let’s prove this to
ourselves in a different way. I’m going to type the serial
number for today, 43169. Control-Enter. That’s a number. I see general, but I can
certainly click the dropdown. And if I say Short
Date, you got it. We applied the
number formatting, and so now it’s
displayed as a date. So you got to know that
underneath date number formatting, there
are actual numbers. This is the perfect example
of formatting as facade. Even though we see on the
surface of the spreadsheet 3/10/2018, that
number is underneath. Another thing about
date number formatting, if you look up to
the formula bar, and this is similar
to time, you can’t see what’s underneath
the number formatting. You see the date in the cell
and up in the formula bar. It’s not until you apply
general that you can see that it’s an actual number. Now why is this so important? And really, why
is it so amazing? It’s because we
can do date math. If there’s actual numbers
under each one of these dates, we can simply take
the later date, subtract the earlier date. And it will tell us how many
days between the two dates. Now in this example, we
have our invoice date and the invoice payment date. And we’d like to
know how many days it took us to pay the invoice. Well, our formula for
calculating the number of days between two dates, equal sign. I’m going to up arrow to
get the later date, minus, up arrow to get
the earlier date. It’s always later date
minus earlier date. When I hit Enter, you got it. 38 days. Now why did that work? I’m going to highlight these. Control Shift
Grave Accent Tilde, because that
formula doesn’t care if we see the integers or
the number formatted dates. Formulas do not see
that number formatting. They always act on
the underlying number. Control-Z. Here’s another example,
invoice due date. And here’s today. Now I’m going to show
you a new function. I could hard code, like
Control-Semicolon and Enter. Later date, earlier date, equals
later date minus earlier date. Now that will work. That tells me that this
person is 16 days late paying their invoice. But if you want this
solution to update, meaning when I
open this tomorrow, I want this to say 17, the day
after, I want it to say 18, we can use the TODAY function. Equals today. Tab. This is an argument
list function. You simply enter TODAY
function with two parentheses, open and close. And when I hit Enter,
it’ll automatically give you today’s date. Now I can’t open this
tomorrow, because I won’t be filming this tomorrow. But tomorrow, this will say
3/11, and this will say 17. Here’s another great
date math example. Here is the loan issue date. That means the day
they took out the loan. Right on the loan
contract, it says what the term is, or
days until loan due, 120. Well, if there are numbers
underneath that date, I can simply go, hey,
there’s the date. And I can add a number to it. When I hit Enter,
it’s even smart enough to know since the cell reference
touched that one first, it formats it as a date. So this loan is
due on 4/29/2018. That’s called the maturity date. Here’s another example. We don’t have to know about
this one for this video, and we don’t have any examples. But it’s very common when
you have a project start date and a project end date. Well, later date
minus earlier date never includes the earlier date. So both of these
calculations here, we subtract a later date
minus earlier date. But it didn’t include
the first date. And down here for project,
we definitely worked on 2/28. So the trick is equals later
date minus earlier date. And since it’s not going
to include that one, you just add one back in. That number can be hardcoded
in, because it’s not going to change. If you want the first day of
the project to be included, you add one. So 8 days it took us to
complete this project. Now here are some
other great examples. Now down here, we want to
learn about the EDATE and EO– that means end of month– functions. The EDATE function allows you to
take any date, a serial number date, and get the same day
in future or past months. EMONTH allows you to take a date
and get the end of the month date for the current month,
future month, or past months. Now here’s our situation. We have the invoice
date, and we need to figure out the due date. And the contract
says that the amount is due on the same
day two months ahead. Now we might try to do, hey,
I know that’s a date plus 60. But guess what? Two months ahead is
not always 60 days. So we can’t do it that way,
because each month does not have exactly 30 days. That’s where the EDATE
function comes in. There’s two arguments–
the start date, comma. And then you simply say how many
months forwards or backwards. If I type 2, it’ll give
me two months ahead. And if I type minus 2, it’ll
give me two months in the past. We want 2 months. So I’m going to put 2, close
parentheses, and Control-Enter. Now we know that that
is a serial number. We’re not confused. We simply come up and apply
a date number formatting. And if we change this,
let’s change it to 15. Instantly, it gives
us two months ahead. Control-Z. If you
change it to minus 2, it’ll go back two months. I should have done minus
3, because it, of course, will go into the previous year. Now F2, this function
also can take 0. But for EDATE, it
doesn’t make any sense, because if I put a 0 here,
it would give me 3/6. We want 2, so I
leave it and Enter. Now down here, we
have an invoice date. And we need to figure
out the due date. EOM means end of month. The contract says
that the amount is due at the end of the month. It doesn’t matter
what day I put here. I simply want the end
of the current month. Equals EO. I see end of month. I hit Tab. Same two arguments. But now when I click on Date,
comma, and if I were to put 2, it doesn’t do what EDATE does. It’s going to jump
2 months ahead and give me the
end of the month. Now this is not what we want
here, but we’ll take a look. Come up to the dropdown. Short date. Oh, so it went 2, 3, 4. It gave me the end of the month. If I were to say minus
2, it doesn’t matter. It goes back 2 months, 1/12. It gives me the
end of the month. The beautiful thing
about this EOMONTH is that many times, we just want
the end of the current month. That means do not jump any
months forwards or backwards. Stay in the current month. When I hit Enter, whoa. And it knows leap year, because
of course, that is a leap year. If I change this to
1, it totally gets it. Control-Z. Another
invoicing date problem we’re going to run into
is we have the invoice date. That’s the day that
the invoice was issued. And we need to jump to
the end of the month. But that’s not when it’s due. We need to find the end
of the current month and then go 10 days
into the next month. So we can use
equals end of month. There’s the date. Comma, 0, close parentheses. That will give me
part of what I want. Dropdown for date, F2. Now I simply need to add 10. 1, 2, 3, 4, 5, 6, 7,
all the way to 10. That’s the correct date. And we’ll use the end
of the month function exactly like that when we do
a few of our cash discounts. Now we have one last
example on this sheet. Our goal was to create a
formula that will automatically tell you how many days
an invoice is overdue. Here is the customer. Here’s the amount they owe. And here are the
invoice due dates. That’s when they were
supposed to be paid. And we need to figure
out how many days late each one of these is. Well, I’m going to use
the TODAY function, because I want this to update
when I open it tomorrow. It’ll automatically put
the day I’m filming video. Double-click and send it
down, so we get the same day all the way down. Now I do my formula
to calculate number of days between two dates. Equals later date
minus earlier date. Control-Enter. Wow, nine days late. Double-click and send it down. I’m going to go to the
last cell and hit F2. So we have our
number of days late. It looks like the number one
slacker right there, 597.07, 74 days late. Tomorrow when we open
this, this will update. And if they haven’t
paid, that will say 75. So here we used a date function. And we used the base formula
for calculating number of days, later date minus earlier date. Now we saw a number
of date calculations, including EDATE
and end of month, and our basic formulas
for doing date math. Now we’re going to use what
we learned here, date number formatting and date math,
to solve common invoicing problems when we
calculate the amount that is due on an invoice. Now before we can look
at the calculations, we want to go over to
the sheet, Invoice. Now this is an example of an
invoice, selling boomerangs. In fact, this Belen
wholesale cost is what we calculated
last video. Now we want to look
at this invoice and talk about the
different parts. This is the seller
up at the top. Right here, this is the buyer. So Gel Boomerangs is selling
some wholesale boomerangs to Kite Flight. Here’s the invoice number. Here’s the invoice date. A lot of our calculations will
be based off the invoice date. Now we have our products,
quantity, price each, line item total. There’s the subtotal. There’s the shipping. And there’s the total. Now when we go to
calculate a discount, we’re going to base it off
of the subtotal amount. We have shipping. But if it had shipping,
insurance, tax, none of those things get a discount. Only the subtotal for
all the products sold. Now shipping terms,
shipping point, we got to figure
out what that means. And terms, this tells
us when the invoice is due and whether or not
we can earn a cash discount. Now this is read 1/15 net 45. That means you get a 1%
discount if you pay within 15 days of the invoice date. Otherwise, you have
to pay the full amount with no discount within 45
days of the invoice date. Now before we talk about
calculating cash discounts, I want to go talk
about shipping and tax. So let’s go over to the
sheet, Shipping Terms. Now FOB, what does that mean? Free on board. But really, we
want to distinguish between FOB destination
and FOB shipping point. What we saw on the
invoice just a second ago, and all of our examples in this
class will be shipping point. Now destination means the seller
retains ownership of the goods until they reach
the destination. At the destination,
ownership of the goods transfer to the buyer. This means the seller
pays the shipping charges. And if there’s damage
during shipping, the seller has to record a loss. That means for shipping
point, it’s the opposite. Ownership of goods transfers
to the buyer prior to shipment. And usually this means
when the goods are handed to the shipping company. The company I used to
run, we sold boomerangs to other stores. So we did FOB shipping point. So as soon as I handed the
box of boomerangs over to UPS, it became the buyer’s products. That meant the buyer
paid the shipping costs. And if there’s damage
during shipping, that means UPS wrecked it. The buyer records a loss. Now of course, the buyer
would go after UPS, or have insurance, or
something like that. COD, that means
cash on delivery. FAS, that means free alongside. Goods are delivered to the
dock with all the freight charges paid. Any remaining charges
are paid by the buyer. So it’s split. Now a few times while
running Gel Boomerangs, we actually bought wood
directly from Finland. And so it was shipped
over on a boat. And it took a long time. But all of the boat
charges were paid by the company from Finland. But we had to hire a truck
to go pick it up and bring it to the manufacturing facility. So that’s free alongside. Also note some businesses will
record shipping on the sales invoice, and some will not. And in fact, at Gel Boomerangs
when we got the wood, we always got a separate
shipping invoice. But many other times, shipping
is included on the invoice. In this class, all
the examples we have, shipping is on the invoice. Now let’s go talk
about sales tax, because sometimes
it’s hard to tell exactly who pays the sales tax. Now the rule is simple. Sales tax is only paid
by the final customer. When the item bought has made
it to the final destination, sales tax must be paid. Tax is paid on retail price,
well, most of the time. Tax is not paid
on wholesale price unless the business
pays at wholesale and uses it in its business. Thus it becomes
the final customer. Here’s the example. The company I used to
run, Gel Boomerang, we would buy paint and paint
equipment from the paint store. Well, guess what? The paint was going to be
used to paint the boomerang. So of course, we bought it at a
wholesale and did not pay tax. But the spray equipment
that we bought, we got a big
discount, because we buy wholesale from this
company all the time. We did not pay retail price. But because the
paint equipment had reached its final
destination, even though we paid a wholesale
cost, we still had to pay tax. All right, that’s
important to know. Although for our
examples in this video, we don’t have any
examples with tax. Now we need to go over and
talk about cash discounts. So I’m going to click on
the sheet, Cash Discounts. Now what is a cash discount? It’s a discount on
the invoice total if the invoice is paid early. Now cash discounts
are discounts that are offered on invoices
to encourage the purchaser to pay quickly. Said a different way,
they are incentive to pay the invoice total early. Now I keep saying total. But cash discounts are
applied to the subtotal for product purchase only. So here’s the
subtotal not including shipping, tax, insurance,
and things like that. Now there are many different
types of cash discounts. And you always have to read
your contract carefully. But we will look
at these methods– the ordinary, the end of month,
as of, and receipt of goods. Now the big
confusion when people are learning these different
discounts for the first time is confusing trade discounts
with cash discounts. Trade discounts, discounts
applied to list price in order to calculate
wholesale costs. Totally different when
we get to cash discounts. Offer of a discount
on the invoice total as an incentive
to pay the bill early. So guess what? $13.72, we learned how to
calculate that with the trade discount last video. But that trade discount just
helped us go from list price to an individual wholesale
cost for a product. So trade discounts help
us get to wholesale cost. Cash discounts are a discount
on our subtotal on our invoice if we pay early. Now let’s learn how to determine
if we get a cash discount and how to calculate
them correctly. Let’s go over to the sheet, O1. O is for ordinary. All right, so ordinary
dating cash discount method. Here it is. 2/10, net 30. The red 2, that means that’s
the cash discount percentage. 2 means 2% discount
on the invoice total without shipping,
tax, insurance, and so on, if paid within
10 days of the invoice date. So these two numbers are linked. That’s the 2% discount. And the 10 is number of
days to take the discount. So 10 means if you pay within
10 days of the invoice date, you get a 2% discount. Then the net 30, number
of days until the invoice is considered late. Net 30 means if you do
not pay invoice total within 30 days of the invoice
date, a late fee may apply. Again, you have to
read your contract. That kind of stuff
is all over the map. Now in our class, we’re going
to do all of the examples where we actually
either get the discount or don’t get the discount. We won’t do any calculations
in this video for past due and then calculating
finance fees. All right, now let’s see how
to use this 2/10, net 30. There’s an example down below. Now the 2 part, that’s
the cash discount. So we write that here. And I’m going to write
the decimal, 0.02. That means two pennies for every
$1 is going to be our discount. Number of days to take the
discount, that’s the 10. So we have 10 days from
the invoice date in order to pay and get the discount. Number of days until the
invoice is considered late, that’s the net 30. So I put a 30 here. I’ve already entered
invoice total. This is the date on the invoice. And this is the date
that we paid the invoice. Now we have a number
of calculations and we’re going to
start off by asking, what’s the last day
to earn a discount? Well, they told us we have 10
days from the invoice date. Well, luckily in Excel, we
can simply do date math. Equals, there’s
the invoice date. And all I have to
do is add that 10. So when I hit Enter, we
have until 3/10/2018. Now we can clearly
see with our eyes that if we paid on the 10th
and we had until the 10th, we do get the discount. Yes, indeed. If you pay exactly on last
day to earn a discount, you do get the discount. Now we might also want to
calculate the final due date. Well, they told us 30 days
from the invoice date. So we say equals invoice
date plus 30 days. And when I hit Enter, we
better pay before 3/30, or else we might get a late fee. Now we can see that
we get the discount. But eventually, we want
our formulas to see for us and calculate for us. So now we want to calculate how
many days between payment date and invoice date. This is the perfect job for
later date minus earlier date. So you ready? Equals, that’s the later date. That means the day we paid
minus the invoice date. That’s the earlier date. And Enter. So now we have a 10 here. That’s how many days
it took us to pay. Now in order to determine
whether we get the discount, you can either compare
last day to earn the discount and
your payment date, meaning we’re
comparing date to date. Or you can calculate
how many days it took you to pay and
compare that to number of days to take the discount. Now I’m going to do it
both ways in this example. But later, I usually use
the actual number of days and compare it to the
days they gave us. Now that means
we’re going to see both ways to ask the question,
is cash discount earned? Have we paid in
10 or fewer days? First I’m going
to take the number of days it took us to pay. And I’m going to
ask the question, is that less than or equal
to the hurdle, number of days they gave us? Right now, 10 is equal to 10. So that will come out true. That’s the first way of
comparing these two numbers. Now let’s compare
these two dates. Equals, is the day
we paid less than or equal to the final
day to get our discount? Both will give us true. Both methods will give
you the same exact answer. Now we can see visually
that it says true. So now we can
calculate our discount. 500 times 0.02. We definitely run the risk
of extraneous decimals. We’re definitely using money. And we’re definitely
going to use this discount amount in a subsequent formula. So we have to use ROUND. $500. Notice there’s $500 in there. And we have to
multiply it by the two pennies they’re going to give
us as a discount for every $1. Comma 2, because we’re
rounding to the penny, close parentheses. Control-Enter, and we get $10. Now the amount to pay equals
the full amount of the invoice. We don’t have any
shipping there or anything that we have to add. So all we have to do is
subtract the discount. And when I hit Enter, $490. That’s the discounted invoice
total that we have to send in. Now I’m going to
zoom in here a bit. And we want to test this. I want to come up and
change 3/10 to 3/11. And when I hit Enter,
these are telling us false. But sure enough,
this did not update. Since it’s false, that really
should be the full invoice amount. So let’s change this formula. F2. Since we want one of two
things, either this subtraction to get the discounted
amount, or we want $500, we can use the IF function. Now we’re going to assume
with the IF function that we already have
all of these cells here. So guess what? I’m totally allowed
for a logical test to click on a cell that
gives me true or false. Comma. If that comes out true, then
I want the discounted amount. Comma. Otherwise, the value if
false, that’s the $500. Close parentheses and Enter. Wow, so now it’s $500. If I change this back to 3/10,
just like that, it updates. Now sometimes we definitely
want to do it in these steps and see the intermediate steps. But sometimes we want
just a single formula to tell us the amount to pay. The trick is going to be we need
one of two things in the cell. I either need the
full amount, or I need the discounted amount. Now before we do our IF formula
and put discounted amount or full amount, let’s
come off to the side and make it easy on
ourselves and see if we can calculate
in a single cell the actual discounted
amount, because up here, we did it in two steps. We calculated the discount and
then subtracted the discount from the $500. Well, no problem. We’ve been studying percentages
and decimals so often in this class. We know that when we’re
given 2%, or 0.02, that’s two parts for every 100. That means two pennies
of discount we get. But if we take two
pennies of discount, that means we’re really
paying 98 pennies for every $1 in that $500 amount. So let’s come down here,
and we have to use ROUND. I’m going to take $500
times, and not the 2%, but in parentheses 1 minus
the 2%, because of course, 1 minus 0.02 will
give us 98 pennies. So we’re multiplying 98
pennies times the $500. Comma 2, close parentheses. And Enter. So $490. That’s the formula element. And I’m actually going to copy
this in edit mode, Control-C. That’s going to be
one of the two things that we put into that cell. Enter. Now I’m going to
come up to this cell. And we’re going to
use the IF function and we have to in a logical test
build our entire logical test. We need to compare
our 10 days that it took us to pay and
see if it’s less than or equal to the hurdle. Well how did I
calculate that 10? I took payment date
minus invoice date. That will give us our
number of days to pay. And we ask the question,
hey, are you less than or equal to the hurdle? If that comes out true,
comma, value if true, the discounted amount. Now I already copied it. So when I Control-V, there’s
the whole value of true. Now I come to the end. Comma, value if false,
that’s the $500. So we have either $500 or $490,
all based on our logical test. Now close parentheses and Enter. If I come up and change
this to 11 and hit Enter, they’re both working. If I were to highlight all
of these and remove them, that’s going to work every time. Change this to a 10,
and just like that, our single cell
formula is working. Now Control-ZZ, and I’m going
to delete that one there. All right, so on this sheet
we saw our first example of the ordinary dating
method, 2/10, net 30. And we saw a few
different options for calculating the
final invoice amount. Now we want to go
over to the sheet, O2. Now here’s our invoice. And the terms for this
invoice are 1/15, net 45. So we need to take
these formula inputs and put them into the cells. So for cash discount, that’s 1%. Now notice it’s
already pre-formatted, so the percentage comes up. Number of days to take the
discount, that’s the 15. So I put a 15. Number of days until the invoice
is late, that’s the net 45. So I put 45 and Enter. Invoice date, I’m
going to say equal sign and just grab it right
from the invoice. And Enter. There’s the day we pay. Now let’s first calculate
last day to earn a discount. There is the invoice date. They give us 15 days. So we simply equals, there’s
the invoice date, plus 15. And enter. So 3/6. Again, we can pick
this out visually, seeing that, in fact,
we did get the discount. But we want to
make it automatic. How about days between
date paid and invoice date? That’ll tell us how many days
it took us to pay the invoice. Later date minus
earlier date, and Enter. So it took us 14 days. Is the discount earned,
paid 15 or fewer days? Well, let’s find out. Number of days to
pay the invoice, are you less than or equal
to the number of days they gave us? Of course, that’ll
come out true. Now I’m going to grab
the invoice subtotal, equals, and there
it is right there. Enter. Amount of the discount. Well, we do get the discount. So we’re going to take
this time times 1%. And we are going to round. So ROUND. There’s invoice
subtotal times 1%, comma 2, close
parentheses, and Enter. Shipping, I’m just going to
get that from the invoice, because we can’t forget that. This one has shipping, and we
cannot calculate the discount on that. Now the amount to
pay, equals, there’s the full invoice amount minus
the discount plus the shipping. Notice we had to do two things– subtract the discount
and add shipping. So there it is, $839.77. Now of course, we got to test
it to make sure it works. So if I change this to
the 7th, there’s a false. But we still got a discount. So let’s fix this one. F2. What do we want? One of two things,
either the ROUND or 0. So I’m going to, right
after the equal sign, IF. Since I already have a
true-false in the cell, that’s going to be
my logical test. Comma, logical test,
value if true, comma 0. That means no discount. Close parentheses
and Control-Enter. Now if I test this, we’re going
to change this to the 5th. And there we go. This is $8.33. And this is reduced, taking into
account shipping and discount. Now if we want to do
this all in one cell, we definitely can do that. And I’m going to do it
a slightly different way this time, because
we do have shipping. I’m going to start
off just to see if I can get the
amount of the discount to come out $8.33 or
0, because let’s look at this final formula
minus the discount. Well, if that cell
is either $8.33 or 0, that will work fine. So we literally
can put down here the total for the invoice
minus, and that’ll be our IF, either 0 or the
discount, plus the shipping. All right, let’s
try it down here. I’m going for the discount. ROUND, and I want just
the discount amount. And I want to do it
off the invoice amount. So I’m either using
these numbers up in the white or these
numbers over here. Times the 1%, comma
2, close parentheses. That will give us the $8.33. But I need to build the
logical test to use $8.33 or 0. IF, the logical test, I’m going
to build it right in the cell. I need to figure out how
many days it took me to pay. So I subtract payment
minus invoice date. Then I need to compare it. Are you less than or
equal to the hurdle of 15? Comma. So we have our logical
test, true or false. That’s what we want
for the discount. If it comes out true, comma 0. That’s the value of false. Control-Enter. Now if I test this up here, 7. All right, so that’s working. We’re just going to think
of that whole IF as that. We’ll put the invoice
amount and the shipping. And we will have our formula. F2. So right at the beginning,
I’m going to say, hey, full invoice amount
minus that whole thing is just the discount. And then I come to the
end, plus the shipping. Control-Enter. And so now if I
change this to 5, everything should be working. If I clear all of
this, that is working. If I change this to 7, it better
give me the entire amount. Control-ZZ. All right, so our second
example of ordinary, still we had just a single
offer of a discount. In our third
example of ordinary, we’ll see two discounts. I’m going to click
on the sheet, O3. Now in this invoice, the
terms list 2/15, 1/30. That means you get a 2% discount
if you pay within 15 days, 1% if you pay within 30 days. Otherwise, pay in 45. Now I’ve entered all
of the information here, including
there are clearly two different situations. Discount rate number
one, number of days to get that discount rate. Here is discount number two. If we miss the 15 days, then
within 30 days, you get a 1%. Otherwise, if we pay
between 30 and 45, we won’t get any discount. All right, so here’s
the day we paid. And here’s the invoice date. We’ll simply calculate
how many days it took us to pay, later
date minus the earlier date. So it took us 17 days. Now since we have two
rates, and we eventually want to automate
this, we’re actually going to have to check
two different things. First we have to
check whether we got the discount earned based
on 15 or fewer days and then 30 or fewer days. Well, that’s no problem. We just have to compare
the number of days that it took us to pay
and ask the question, are you less than or equal
to the first hurdle of 15? Now we have to do a
different logical test. We’re still looking
at number of days. But now we’re asking the
question, hey, are you less than or equal to the
next hurdle, which is 30? In this case, we get a true. So that means our
rate that we get to use for discounting is
one penny for every dollar. Now discount to use,
I’m simply going to say equals and
click on that 1%. Enter. Amount of the discount
equals the ROUND. I’m going to come
over to the invoice and get the subtotal
amount times the 1%, comma 2, close
parentheses, and Enter. So the amount to pay, and I’m
coming over to the invoice to get subtotal minus the
discount plus our shipping. And Enter. Now let’s test it. If I come up here
and change this to 1, now we get a true here. So we definitely
should be using the 2%. But we’re not. If I change it to 30, we should
be getting no discount at all. That doesn’t say 0. And that doesn’t say 0 either. Now I’m going to Control-ZZ
to put it back to 3/8. Now this is the first time
in the classroom where we’ve had a cell
that’s getting one of three things in the cell. I either want 2%, 1%, or 0. Now we know that when we have
one of two things in the cell, we use the IF function. If you have one of
three things, then you have to use two IF
functions together. So this will be the first
time we get to see this. All right, equals IF. And I’m going to use
these true-falses here. The first test, if we paid
in fewer than 15 days. That’s the logical test. Comma. Well, if we have, we get 2%. Comma, otherwise value if false. Now here’s the trick. Here’s how you know when
you have to use two IFs. You ask the question, well,
how many things are left? I still have two
things, either 1% or 0. So guess what? Right in the value if false,
we’re going to put a second IF. That second IF will dump one of
two things into value if false. Now we’ll go watch the formula
evaluator after we’re done and prove that that
actually does happen. So we still have two
possible things left. So I put a second IF. Now the logical test,
that’s this true or false in that cell. If that comes out true, comma. Then what do I want? 1%. Comma. Otherwise, there’s
only one thing left. So I put it there, 0. Now watch this. I’m going to have to
close parentheses. But notice that
parentheses is not black. So I keep typing parentheses
till I see the black. And I know I’m done. So close parentheses and Enter. Now here’s the big moment. Let’s test it. I’m going to come up here. And when I change this
to 1, I can clearly see this is 10, which
is less than 15. That says true. And sure enough, our
double IF is delivering 2%. Now I’m going to
change this to 30. Enter. And sure enough,
it delivers a 0. Now we have built a formula
that can put one of three things into the cell. Now I’m going to
change this back to 8. And I want to go look at
how this formula calculates. So this is the discount to use. It’s got that double IF. Let’s come up to Formulas, over
to Formula Auditing, Evaluate Formula. The underline means it’s
going to evaluate it. You either click
Evaluate or hit Enter. When I hit Enter, it sees false. Now it immediately jumps
inside the second IF, because it knows it
can’t put H6 in there. So it’s trying to
evaluate what’s sitting in value if false. But this is the second IF. So it looks in there. It sees a true. Now it can evaluate
the entire IF, which means it has to look into H8. So I hit Enter. There’s H8. So the second IF dumped
H8 into the first IF. Now since it says
false, it’s not going to take value if true. It’s going to take
value if false. So when I hit Enter,
there’s the 1%. And Enter. Now I’m going to change this
and do it one other way. I’m going to change this to 30. So now it’s evaluating to the 0. So let’s click on
Evaluate Formula. It’s looking. It sees a false. Now it goes to the second IF. It sees a false. Now it’s going to get that 0. And sure enough, the
second IF dumped a 0 into the value of
false of the first IF. And now it’ll
evaluate and dump a 0. So we have the multi-step
method working. The only trick is
how in the world are we going to put
that all into one cell? Well, same way. F2. Remember, we’re just going to
need to do the hard part, which is this IF. Now I’m going to
start off by trying to build this whole double
IF without these two cells. So you ready? Equals IF. Well, the first
logical test is I need to test if we
paid in under 15 days. Well, I got to get
number of days. So I say the day I paid
minus the invoice date. And then I have to
ask, are you less than or equal to the first hurdle? So that’s our
first logical test. Comma, if that’s
true, well, I need 2%. Comma, value if false. Remember, since we
have two things left, I have to use a second IF. So I type IF. Oh, I’m going to have to do
the same test on the 30 days. So I say later date
minus earlier date. And I ask the question,
are you less than or equal to the second hurdle? That’s the second logical test. If that comes out true, because
the first one came out false and it came to this one, then
value of true, that’s the 1%. Comma. Otherwise if both logical
tests came out false, then please throw
a 0 into the cell. Close parenthesis,
close parenthesis. I see the black one,
and Control-Enter. Now let’s test it. I’m going to try 1. It better give me
2%, and it does. Change it to 8, and
it better give me 1%. Now I’m going to hit F2. That whole big, huge
thing, it’s just delivering the discount rate. So now at the very
beginning, I’m going to click on the
invoice amount and times. That will give me
the correct discount. But it’s not rounded. So right after the equal
sign, I type ROUND. Tab. That whole scary thing
is just the discount that needs to be rounded. So I come to the end. Comma 2, close parentheses. Control-Enter. So $8.33. Let’s test it. I’m going to come up here
and change this to 30. Better be 0. Change this to 1, and
it better be $16.66. Wow, that’s amazing. That whole thing, F2,
that’s just the discount. So now we continue on. There is the subtotal
minus the discount. And then I come to the
end, plus the shipping. Control-Enter. Now I come up, and I’m
going to change it. 30 and Control-Enter. Totally working. Change it to 8, and
that is amazing. Now when you’re doing a test
or doing this on your own, by all means, if you want
to do the multi-step method, then do it that way. If you want to try and do
it that way and then mash it all together, you
can do that also. Now we want to look
at a few other methods of calculating cash discount. We have as of and
receipt of goods. Now I’m going to click on
the as of answer sheet. And look. Guess what? This will be easy, because
the only difference between as of date
cash discount method and the ordinary
dating cash date method is you don’t use
the invoice date. You use the as of date. That means prominently
on the invoice, it will say, please use
as of date rather than the invoice date. So that means for
all your calculation like number of days, you don’t
take payment minus invoice. You take payment
minus as of date. All the rest of the
calculations are the same. If we go over to the receipt
of goods answer sheet, it’s the same thing. The terms will
clearly say you’re supposed to use
receipt of goods date rather than the invoice date. That means the day you
received the goods. Now there’s one other thing. If you see 2/10
ROG, wait a second. There’s no net here. Now I want to go
look at the PDF notes on page 8 in the ordinary method
for calculating cash discounts. There’s a note here. If no net is given, it’s assumed
to be 20 days past the discount period. And that’s for all the different
methods, whether it’s ordinary, as of receipt of goods,
and even end of month. So back over here,
it says 2/10 ROG. That means the net is 20 days
past whatever the last day is for the discount. So when we calculate
up here number of days until the invoice is
late, we just hard code the 20. That’s the number
of days we were given past the receipt of goods
date to take our discount. So we simply add 20. That means if you see a 10
here, you know net is 30. All of the other calculations
are the same except for the one calculation,
where instead of using the invoice date,
you use receipt of goods. Here’s the answer sheet. You can go ahead and
try that over here. Now we got to talk about our
last cash discount method. I’m going to click
on the EOM sheet, because it’s a lot different
than the ordinary dating method. Here is what your invoice
terms will say, 3/15 EOM. The 3, that’s the same
as it has always been, cash discount percent. 3 mean 3% discount
on invoice total. The 15, last day in the
month to earn a discount. And this gets crazy. The 15 means if the day
number in the invoice date is less than 26, that means,
for example, down here, the invoice date is 19. That’s less than the
26th of the month. So the 15 will mean if your
invoice day is less than 26, the last day to earn a discount
is the 15th of next month. Otherwise, if the day
number in the invoice date is 26 days or more, the
last day to earn a discount is the 15th of two months ahead. And the final due
date, since there’s no net day, that EOM
without net means you must calculate the
final day to pay the invoice without a late payment by
adding 20 days to the last day to earn a discount date. Now that sounds
pretty confusing. But there’s all the
rules right here. And as we step through,
it’ll make more sense. So there’s our terms,
3/15 end of month. We list our 3%. There’s the 15th of either
next month or two months ahead. There’s the invoice date. And there’s the day we pay. Now the very first
thing we have to do is we’ll always
have to look and see what is the day in the month. It should be 19 here. Over in the second
example, it’s 26. So can we automate that? You betcha, because luckily,
they named this function. Smartly, they named
it the day function. And also luckily, we know
what a serial number is. We click on whatever the
serial number date is. And it will always
just pick out the day. Now if we eye this, we can see
we’re under the hurdle of 26. So that means we need
to jump to next month, from February to next month. And it would be the 15th. Now notice it gives you the
15 from the end of the month. So we’re in February. So guess what? I’m going to jump down here. And we need to calculate
the last day in the month to earn a discount. And the way we do this is we
use the end of month function. We click on the
invoice date, comma 0, because our goal is to go
to the end of this month. If I enter this, you could see
in 2018, February has 28 days. F2, and we add the 15. So there is the
last day that we’re allowed to get a discount. Now if we look at
our terms, since it doesn’t say net
something something, we simply take that
date and add 20. So the final due date
is whatever the last day to earn a discount is
plus hard coded in, 20. So we have our two dates. If we pay by 3/15,
we get the discount. Otherwise, we have
to pay by April 4th. Now we can eye our example here. And we can see we have not
paid before this discount date. Now I want to F2 on the
formula with our end of month. And I want to notice,
we put a 0 into months. That will always be the case
when the day is less than 26. Now I want to come over to
our second example over here. The invoice day is the 26th. And I want to do our same
couple of calculations. We’re going to start with what
is the day of our invoice date. That tells us 26th. Eyeing it, I know that
I get the discount. So I’m going to come down. This is the last day in the
month to earn a discount. So I say end of month. And there’s the invoice date. But comma, instead of jumping
0 to the end of February, I need to add a 1 here to
jump to the end of next month. So I put a 1, close parentheses. If I enter that, that
gives me March 31st. But I know it’s
15 days past that. So I add the 15 and Enter. Now I can equals that plus 20. So for this invoice where
the day is 26, 4/15. We have until April 15th. Otherwise, pay by
the 5th of May. Now I want to try
and automate this. So no matter what date I put
here, everything will work. And the way we’re going
to do that is we’re going to notice that any time
the day in the month is 26 or later, the end of
the month needs a 1. Over here, the day
is less than 26. End of the month
is going to get 0. That means the month’s
argument in end of month needs one of two things– 0 or 1. That sounds like the perfect
job for the IF function. Now let’s do the logical test. And actually, we’re going
to see something even better than the IF function. We still need one of two things. End of month needs 0 or 1. But let’s build our logical
test smartly and then see if we can adapt it. Now our logical test is
going to be is the day– that means whatever the
day function spits out– is it 26 or bigger? Another way to say that is,
hey, whatever day puts out, are you greater than
or equal to the hurdle? When I hit Enter, that
will give me false. Now our end of month needs 0. So watch this trick. Any time we have a
true-false logical value, we can convert a false to 0
and a true to 1 by adding 0. So I’m going to
say, hey, whatever’s in that cell, logical
value, please add 0. When I hit Enter,
it turns that to 0. If I were to come up here
and change this to 26, watch what happens. True, add 0, it becomes 1. So we can use that 01
rather than our IF. Put it inside of end of month. And end of month
will always know to jump to the end of
this month or next month. Now I’m going to change
this back, Control-Z. And now we want to come
F2 and highlight 0. And click on our cell
with either 0 or 1. And Enter. So now it’s the 15th. When I change this to the
26th, it’s totally working. True. This is a 1. That feeds into end of month. And it gives us 4/15. And there’s the final due date. And we need to Control-Z. Our next task is to determine
if we earned a discount. That means we have to
compare that date to when we paid our invoice. No problem. I’m going to say equals
our payment date. Are you less than or equal to
last day to earn a discount? Right now, it’s false. Let’s change this
to 26, and Enter. Now it’s true. Now we have to calculate
the amount of discount given that it’s true. I’m going to use
the ROUND function. There’s the invoice
subtotal times our 3%. Comma 2, close parentheses. Now right now, it is not
linked to that true and false, because what we really
want in this cell is either $24.99 or 0. Now no problem. We could use the IF function. But we saw up here how we
can convert true and false to 1 and 0. Now up here, we added 0. But any math operation will
convert true and false, true to 1, false to 0. Well, how about
that right there? That’s $24.99 right now. What would happen if we
multiplied that by false or 0? It would be 0. What if we multiplied
it by true or 1? 1 would not change it at all. So instead of using
the IF here, we’re going to just take our
logical test, which happens to be right there in the cell. And we’re going to multiply
it straight across. By using the result
of the logical test, that will be 1 or 0. Now I can Control-Enter, $24.99. If I change this to 19 and
hit Enter, now it is 0. That’s exactly what we want. Now we can finish our formula. Equals there’s the
invoice subtotal minus the amount of
discount plus the shipping. When I hit Enter,
that should work. Now no matter what date
I put up here, the 19th, it gives us no discount. And if I change it
to 26 and Enter, now we have the discount. And there’s the
correct amount to pay. Now I’m going to Control-Z. Just for practice, let’s
do this again over here. We’re first going
to ask the question, is the day bigger
than the hurdle? Equals there’s the day. Are you greater than or
equal to– we got to include the equals– and Enter. Now we want to add 0 to this to
convert it, in our case, to 1. And if it turns out to be
false, it’ll convert it to 0. And Enter. Now I’m going to link,
F2, the end of the month. Right in month’s
argument, I’m clicking on that cell with a 0 or 1. And Enter. Now I want to come down here. Did we earn a discount? We have to compare
our payment date. Are you less than or
equal to the last day to get the discount? This comes out true. Amount of the discount,
well, I definitely have to use the ROUND. I’m going to take invoice
subtotal times the 3%. And now I’m going to times
the true-false value. Comma 2, close
parentheses, and Enter. Now we can calculate
amount to pay, equals, and I’m going to get
subtotal minus the discount plus the shipping and Enter. If I change this up
here, 19 and Enter. Everything is working. Control-Z. All right, that was
a wild cash discount method, end of month. Now for this video, there are
a lot of homework problems. There’s 1 to 17 amazing
homework problems for you to practice with. Now in this video, we
talked about invoice totals. And when we’re given terms,
like 3/15 end of month, we have the opportunity
to earn a discount. A cash discount is subtracted
from the invoice subtotal. Now we talked about end
of month on this sheet and saw the end
of month function. And we saw some
calculations where we’re actually taking
true-false values and using them in formulas. We were able to on
this sheet calculate the final invoice amount to pay. On the ROG sheet, we talked
about receipt of goods. On the As Of sheet,
we talked about as of. Over on O3, we talked about what
to do when you’re given terms where you’re offered
multiple discounts and you have to pick the
correct one for the calculation. Over on O2, we saw how to
use our terms from an invoice and calculate the amount to pay. Over on O1, we introduced
ourselves to ordinary dating cash discount method. We talked about cash discounts. We talked about sales
tax, shipping terms. We looked at an invoice. And we started it all off
talking about date number formatting and date math. Now if you like that video, be
sure to click that thumbs up, leave a comment, and sub,
because there’s always lots more videos
to come from Excel is Fun, including
next video, number 39. We’ll actually build the
invoice from scratch. All right, we’ll
see you next video.

You May Also Like

About the Author: Oren Garnes

15 Comments

  1. I know you know that i still don't see the vidoe but i would love to be the first one to comment on this video i'm really sure it gonna be super as always thank you so much because i believe in your work that you always done the best thank you Mr. Mike

  2. Thank you Mike. not only an expert in Excel but also on INCO terms, good work! I noticed something: the M of month is in Capital but days (d) and years (y) are not. Why is that?? I had some problems with german TEXT(date, "dd-mm-jj") that did not work but TEXT(date, "dd-MM-jj") did. So aparantly the m is Case sensitive. Strange…..?

  3. Thanks Mike. I did get something new today. I had situations where I had an array like {"John";"Steve";"Karl";"22";"23";"24";"30"} , if I add a +0, that will convert the numbers in numeric values and then I will use the isnumber function. However I never bothered to add +0 to a simple logical test….True+0 = 1!!! This is Great :)…… (I hope that I explained myself well) This never crossed my mind. Thanks GOD Bleesss you

  4. Working with dates & invoicing: 2 tasks that can quickly & easily be done with Excel. Thanks for the great real-life examples & detailed explanations.

  5. Hello sir yoda thinks a lot for your instruction along this video , many thank's and you'r a crazy gay with a lot of skills

  6. I REALLY appreciate your willingness to share this valuable information especially as it pertains to date calculations. Thank You!

  7. Hello I don"t know have to reach out other thing putting in a comment. I am trying to take info from one worksheet and put on another. So lets say on sheet 1 cell a1 i need that info in b1 on sheet 2. I understand how to do this. But I need to pull the info from sheet 1 onto 52 sheets all needed to go to the same cell on each sheet just changing were it getting it from sheet 1. So sheet 2 would need to pull from a2 and sheet 3 would pull from a3. Can you point me to the video i need to watch. Cant find anything.

  8. Super video, Mike. I wanted to mention that EDATE does not always report what we suspect when the data encompasses the last days of a month. For example, if our date in A1 is Dec 31, 2017 and we use EDATE(A1,2) the function reports Feb 28, 2018. And if A1 is Apr 30, 2018, and we use =EDATE(A1,-2), we also get Feb 28, 2018. This concept comes in handy for end-of-month transactions.

Leave a Reply

Your email address will not be published. Required fields are marked *