fetch

Excel Magic Trick 783: Date Functions & Formulas (17 Examples)



Sharing buttons:

welcome to XML matrix 783 hey if you

want to download this workbook EXO

matrix 779 276 click on the link below

the video oh wow we have an epic video

here this is date functions and formulas

I'm going to show you seventeen examples

hopefully this will sort of be a

one-stop shopping for date functions and

formulas dates here's a column of dates

and if I look up here it says date

number format if I apply the general

format you see that it has a number

underneath a serial number if I put the

one number one here and apply a date we

see that the first day that Excel knows

is 1 1 1900 if I type a 2 here and then

apply a number formatting short date I

see 1 2 1900 so I'm going to ctrl Z on

that undo one more time right so 4 0 5 8

5 that's how many days since December

31st 1899 there are and that will

represent the number 2 11 2011 now

serial dates are underneath and we'll do

date math later first couple example

just going to see how to get information

from a date like we could say use the

day Fox and the day function requires a

serial number if you didn't know that

dates were still number you mean like

what is that so I'm going to click there

and it will give me a day oh yeah that's

the 11th day in the month if I double

click and send this down that's all the

day function it gives you a number

between 1 and 31 now if you wanted the

actual name of the day you'd have to use

the text function text function requires

any value knowing that a date is a

number actually helps us understand that

comment and then you have to know custom

number formatting well um

date number formatting is easy and now

you have to put number formatting in

double quotes and I want day right so I

put D that would give me 11 DD that will

give me 11 D DD that would give me

if it's Monday it will give me mo n and

if I put four DS it will give me the

fully spelled out name thank you so

that's Friday if I had two in there it

would give me just FRA control Z for

undo I'm going to double click and send

that down so that's the actual day now a

month

well sometimes you want to see the month

one is January two is February etc so

you can use the month function notice it

says serial number copy that down

sometimes people have this as an extra

column and then they can do an easy some

if by month if you wanted the month

spelled out like day over here you'd use

text I'd give it this value comma and

then if day is D what do you think month

is right mm mm and then you can copy it

all the way down sometimes these two

items are used in some product formulas

where you add a particular month and you

need to match a word criteria against a

serial date

how about year equals what do you think

the name of the year function is yeah

it's year and year we look at a serial

number just give the year same thing as

a month sometimes people add extra

columns like this and then it's easy to

do a sum if by year now let's get a

little going here and now watch this

when I scroll over to the side I can't

see that so I'm going to actually

highlight this and freeze it that way as

I scroll to the side I'll always see

this view and then over here freeze and

freeze the column so now when I scroll

over no problem I can see last day of

the month now last day the month if you

have 2007 and 10 there's a built-in

function called end of month in earlier

versions you have to add the data

analysis toolpak in under the Tools menu

data a Tools menu add-in if I can even

remember back that far awesome function

end of month you give it a start date

which has to be a serial number and you

just say how many months in the future

or in the past one would give us the end

of month next month so it would be after

February's March I think right so March

I give it a zero that's this month right

and that's what we want if you give it a

minus one it'll give you last month so

last day of this month would be zero now

in earlier versions or if you run into a

computer that doesn't have in a month

it's useful to know how to do this now

what we learn here is not going to be

applicable applicable much as we go

forward in the future because we have in

a month but the tricks we learn here

will help us with some more advanced

tricks I'm gonna use the date function

equals date if I could type oh man I'm

having a hard time here let's see date

all date does is you give it a year a

month in a day and it spits out the

serial number so I'm going to just going

to give you an example 2011 comma let's

say the month is two comma and the day

is 11 right that's kind of silly there

but that's what it does you put in those

parameters arguments or parameters and

it spits out the serial number formatted

as a date but that's not what we want

here we want the last day of the month

so we're going to use this function to

get the year I'm going to use the Year

function that will dump it 2011 in there

then I'm going to say the month

that'll dump a 2 in there now here's the

trick to getting the last day of the

month I'm going to add one right now

it's two if I add one it sends it to

next month comma and now the day the day

is awesome because if I put one year

sure it's 11 right month well it's not

two it's three and if I put a 1 that's a

cool way to get the first day next month

but watch this this argument right here

can take positive numbers zeros are

negative so if I give it a 0 well let me

ask you this if I put in a 1 it's the

first of the month so if I go backwards

one number what it's going to do this

trick gets us to the first day next

month we just add a zero here and it

goes back 1 so that's a tricky way to

get the last day of the month now I'm

going to double click and send that down

and notice it even knows leap year I

didn't point that out about the end of

the month all right

also this argument here can have -

numbers and sometimes advanced formulas

will do tricky things to get

to get a date and you have to have a -

here but I'm going to put zero now the

first day of next month well there's two

ways we'll do the end of month and a

month we have a start day comma and this

is the first day next month so why what

am I going to do oh I can get the last

day of this month right that's the

serial number so all I have to do is add

one and then double click and send it

down now first day next month well we

actually just did this equals date and

then I want this on what the year so I'm

going to do the year of this comma close

parentheses comma and then the month of

this and the whole trick is I want to

send it to next month so this is a - I

add one and then comma the day is simply

hard-coded one that'll give me first

date next month and then double click

and send it down alright now what about

there's lots of other tricks when it's a

vest date that just means sometimes you

have a in Human Resources you have a

column of higher dates and you want to

see when they vest to get their pension

and that criteria for this company is

five years we can use the e date edate

is awesome you give it a start date

comma and a number of months in the

future now end of month always

calculates the end of the month but

what's great about edate is it knows

that this is 211 so if i give it a 1

it'll give me the 11th next month right

or whatever you want - 1 that'll give me

the 11th of the prior month but what do

we want here notice the argument

requires month and we have year so no

problem we say that and lock it with the

f4 key because I'm going to copy it down

times 12 and that gives us our months

control-enter and there's the vest date

so a column of vest dates from our

higher dates how about

work day in the future so here it is I

need the work day in the future 16 days

in the future well to get and actually

are the example for date math is over

here maybe I should go do this first

well no I want I'll just show you

there's a function called work day and

that's all it does

now work day this assumes that Saturday

and Sunday are the only weekends but

this new function in 2010 is awesome

watch it start date comma days remember

the whole point of this is we want an

actual date it's going to deliver a

serial number here's the start date

that's the number of workdays in the

future now watch this comma and this new

weekend argument absolutely awesome this

solves the problem early in earlier

versions before 2010 there were some

nasty array formulas to do things like

this or non array formulas too but this

drop down and this weekend argument just

makes it beautiful

now I'm going to assume that we have

Sunday only so I'm going to put an 11

that means the only day that it's going

to skip over when calculating this

future workday is Sunday comma and then

holidays you can leave it out if you

have holidays I'm going to click on a

single cell you could highlight a bunch

of cells with all the holidays for

whatever but there it is holidays and

close parentheses and there it is it

calculates 16 days in the future given

this as the start date boom and I'm

going to double click and send it down I

forgot to lock it now notice when I copy

that down it's all highlighted the

active cell is right there I'm going to

hit the f2 and I simply forgot to lock

one of the cells now I'm editing this in

the active cell so I'm going to go up to

here and hit the f4 key to lock the row

reference and then this I'm going to

lock the row reference to populate all

of these highlighted cells with the new

edited formulas I'm going to hold ctrl

and tap enter so then you can go down

here and see that that worked

now last Monday this is going to be the

trickiest phone we do totally awesome

actually

in video 786 coming up

later we're going to do a chart and one

of the most important tricks is knowing

how to calculate last Monday so for

example this is Friday right so I need

the Monday before which would be equals

if this is Friday like four or five or

something there it is

so I subtract four from this but I

certainly don't want to go through and

count as I go down and do it's hard code

these no way so how do we do this well

first we got to learn what the weekday

function does weekday functions totally

awesome

here's Monday Tuesday Wednesday Thursday

Friday Saturday Sunday I added a custom

number format control one just like we

saw earlier custom number format I'm

going to leave all that junk out here it

just shows DDD mmm D D Y so we we saw it

just a moment ago in the text function

but you can certainly do it in the

format cells dialog box under the number

tab custom and then type it out and

that's what you see that serial number

is still under though under there though

all right what does week they do it's

going to tell it's either one two three

four five or in some cases sunday is one

this is two three four five and in other

cases this is 0 this is 1 this is 2

the weekday will ask us which one we

want so weekday serial number here it is

comma and oh this is awesome in 2010

another beautiful drop-down with all

these amazing options so one if you put

a 1 here it's 1 to 7 if you put a 2 its

month I'm sorry sunday is 1 thru

Saturday at 7 if you put a 2 Monday is

one Sunday at 7 and this is the one we

want for last money zero for Monday 6

for Sunday so I'm going to type a 3 and

double click and send this down now

think about this what are we going to do

I need last Monday if I have Friday how

many days do I need to subtract to get

back to Monday for how many and in fact

we could do a little trick here one two

two three four I'm going to do the day

function here equals day and I'm going

to click on this first one and I'm going

to populate all there's two things here

I'm not going to type that in because

you don't need it we'll put it in

automatically and I've have the active

cell with the formula and I want to

populate all these so ctrl enter I did

one too many now 16 17 18 that's what

the day function delivers so on this

particular day Monday is 60 but I want

to have a column full of Monday's so

here's what we do we do this - weekday I

want that you weekday international oh

wait a second I'm sorry I got confused

there I just want the weekday and that

because it'll just it doesn't need the

International it just gives us a single

number serial number I'm going to say

this one right here comma and then that

three now right now think about what

does this do it's looking at Monday so

it subtracts a zero but when I copy it

down this one's going to subtract a1 a2

a3 just as we see up here so this is the

whole trick that's custom number

formatting there I'm going to get rid of

that over here there's custom number

format I'm going to get general what it

did is the formula sucked the number

format from there you can either go

general here or control shift tilde

control ctrl + Shift + in this little

tilde or grave accent right that's the

keyboard shortcut to go back to general

oh and I got that green I don't want any

of that and now look at this there's a

bunch of 16s and that is the conceptual

trick I'm going to have to do day -

weekday to get a bunch of 16s to show

Mondays and really last Monday important

for some charting tricks again will see

this letter so you ready we'll see it

later ready we're going to do date oh I

need the year so I'm going to use the

year function

, I need the month that's just our month

right here and then I need day comma day

- week day comma week day and then I'm

going to click right there

comma 3 now that comma 3 you know I

remember when I first learned about week

10 I didn't know this cool trick I

learned it from the mr. Excel message

board but I was like zero

who wants a zero like this is the

perfect reason for it alright so I'm

going to close parentheses I put a comma

there alright so there we go

and it will give us Monday's all the way

down so this is Monday 7 this is the

prior Monday right so any one of these

date inputs is just telling me the

Monday from before all right let's keep

going over here a couple more date

tricks all right so this one I should

have done this one at the beginning this

is just basic dates since these are

serial numbers I need to figure out the

number of days between two dates right

this is using an invoicing a lot here's

here's the due date here's today how

many days late is this so equals and

it's always the later date minus the

earlier date or n minus begin sometimes

when you do this formula specially in

earlier versions that will suck the

format so then you have to apply general

but in later versions they fix that so

I'm going to double click and there it

is it tells me how many days between two

dates all sorts of date calculations

require that you calculate the number of

days between two dates how long was a

project how late is this invoice etc etc

now let's do project date and this one

will change it up a little bit I'll say

later - earlier but what's the problem

here this since these are numbers

underneath it's subtracting it it's

never going to include this first date

but for projects you often want to

include it so what do you do plus one

that's the number of the days it took

for this project now net working days

we're going to look at another awesome

new function because yes sure enough

this is a project right and

these are all the days but what if we

didn't work on Sunday or Saturday or

Sunday or Monday or whatever equals

networking just like well so this is

from earlier versions this is the new

one earlier versions just assume

Saturday and Sunday this one you have

the option so I'm going to do a start

date start date so I'm going to do that

one comma and then end date comma oh

look at this awesome drop-down there is

our choice and I'm going to say Sunday

only could be Friday Saturday whichever

one whichever weekend you have and there

you have it set Sunday comma and

holidays and I'm going to kind of want

that so I'm going to drag it and notice

I'm dragging a bunch of cells even

though they're blank that way if I have

more holidays later I can do that now I

need to lock these I'm going to hit f4

and then close parentheses and then

double click and send it down that is

just awesome so net working days between

two dates all right that was 17 date

tricks all right we'll see you next

trick

you