all right so in this video i'm gonna go over why you want to use date function in your formulas
so let's try to do some sort of logical test we have some dates here
so if i wanted to compare those dates to a date in my formula i would go here and do equals
and then if i try to compare if this is let's say greater than 11 1 20 21 this is not going to work
let me just hit enter and get the results so you can see all of them are true now the reason all
of them are true here you can see i was trying to compare if this date is greater than november date
and it says true which should not be true but the problem we're having here is that this
is not evaluating as a date it's actually a division so it takes 11 divides by 1 and then
it divides it by 2021 so we get this number 0.005 and then we compare if that number is less than
this date and because our dates are basically whole numbers then it's always going to be
bigger than this so the next thing you would probably think of trying is to put this in quotes
so if i do that and drag it down you see i got a false here but now i get a false everywhere
so again if i check see i was checking if this is greater than november 1st so again this one
isn't so that's false but this one should be greater than this is 11.3 now i still get a
false here as you can see because now we have a problem that we check not against date we
actually check against text so now we compare if this number is greater than text basically it's
doing alphabetical comparison and in alphabetical comparison numbers go on top and then text goes
below so based on that logic it's going to compare this and say well this is not greater than
this so it doesn't work so one way to fix this problem is to use this date value function
and what date value function does it takes your date as string as text and converts it to an
actual date so right now if i close parentheses and hit enter and drag this down this should work
so see i get a true on this one because it's greater than november 1st so all good
seems to work just fine now the reason you wouldn't want to do this is that
right now i'm fine with this because i'm using usdates i'm going with 11 1 20 21 but what if
we change our date format for the spreadsheet now our format for dates is instead of being month day
year it's day month and then year like european sort of dates so then we got a problem because
we have a hard-coded thing here text going on and now that hard-coded text for dates doesn't work
so the way to make this compatible for both is to use date function instead
so you use your date function and for date function you provide your year so i'll do 20 21
comma then i have the month then that's going to be 11 comma and the day is gonna be one and now
i can drag this down it's gonna work the same way because it's going to create a date from these
parameters and these parameters are always going to be where they are and it's going to work so now
i have something that's actually compatible with different settings of spreadsheets and i don't
have to worry about this and this is why when you have to hard code a date in your formulas
the best way to do this is to just use date function and that should do it for this video
thanks for watching please subscribe and i'll see in the next one