If you use the Google Sheets and you're trying to work with dates or times in
formulas, you're probably noticing that it doesn't really work the way numbers
would. Oftentimes, you'll get an error in the formula or it'll give you a result
that doesn't make any sense. We're gonna go over three ways that you can use
dates and times in formulas and it will work every time. Each one of them is a
little bit different and you can use them in different situations. Each
one of the solutions is easy so whichever one works for you. You could
start using right after you watch this video. For the first one, I'll just give
you an example what doesn't work. Hey December 31st 2017, let's just
stick without and show it doesn't work. If you hit enter, it thinks that you wanted to
divide 12 by 31 by 17. It doesn't even have to be much of a formula. This is
just a one value formula and it already breaks down. But, you can see what
happened. What you have to do, use the equal sign again to say I'm using a
formula, just put it in quotes. "12/ 31/17", close the quote off, hit enter, and then
you have a date. You'll notice it also shifted the value to left. Sheets will do
that with a date as opposed to a number that it will put to the right. Let me
throw in a time as well. I'll make that formula just a little bit more
complicated. Let's do 12/20/14 at 1:00 p.m. If you put
quotes around that, oops I forgot something, and I could I could tell
something was wrong because it wasn't all green. I forgot the quote at the
beginning. Let me erase a little bit I of this and I'll just keep
typing. You see, it's telling me everything's good right now because it's
green. It recognizes everything and the little value that's in the pop-up here
is telling you what's going to happen if you hit enter right now - 1:00 p.m. I
hit enter there it is, valid time.
I'm gonna go to the right of the quote and subtract another date or time
value. I'm just gonna do this one, just with a date, so it's gonna make it
midnight and a hit enter. That worked. You can see the amount of time between
these two is about a year so 345 you know is probably the right value.
Using quotes is the first way. The second way to work with dates and times in a
spreadsheet is to type them into a cell or have them in a cell already first and
then reference them with your formula. If I start the formula here and I just
subtract these two, it knows it's 365 days. It's not going to say 1 because the
default format is to display number of days and, underneath, these two
dates are just stored as numbers of days. So, when you subtract the two, the result is
naturally 365. You can also do this with time. If you do 12/31/17
at midnight, which will be the same as the value above, and you go into this
formula you change it to B5, the result will be the same. The third and final way
to use dates in spreadsheets or times is to use a date
function. Let's act like you have dates listed in a table in a large
spreadsheet and say this is the month this is the day and this
is the year. You can turn those into a date by using the DATE function.
The DATE function looks for a year, looks for the month, and the day. When you close
it off with parentheses and hit enter you get March 4th 2017. If I copy and
paste that formula below, it's gonna figure out the May as well.
You can embed that DATE function within a longer
formula to work with two dates. Another thing that we can do, let's go
into this, and you can really use any date function. We're going to use the
NOW function. Let's subtract the amount of time between March 4, 2017
and now. NOW needs an opening and a closing parenthesis because it is a
function, but you don't need anything in there. If you hit Enter, that's the result
of that. NOW gives you the date and time. If you just want the date do TODAY. Boom! This
gives you three different ways to work with dates and times in your formulas. You
have using parentheses if you want to type it in, you have using cell references,
and you have using date functions. So I hope that was helpful. If you want to
see written instructions on it, just go to the website that's in the upper right
hand corner and you can follow it through on there.