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.

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

Ok, I'm the second to comment 😉

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…..?

Another great video!

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

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

Thanks for the date video (with a bonus of learning some shipping terms!)

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

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

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.

Thank you very much Mike 🙂

fabulous video

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.

Wow. Thats amazing. Thanks EXCELlent MIke

Thanks !