Extract the time from a Google Sheets timestamp.
Academy ↓
Hey there stranger!
How to Get Time from a Timestamp
About this Tutorial
Video Transcript
Hi. So we have a question from Anders. He was asking he has a timestamp and he wants to have some kind of if statement here based on this time. But this timestamp has a date. It has a time, and he just wants to know, you know, how to say. Is it above or below? 6:00 PM right? So we're gonna write like a if and the logical expression is gonna be basically this is greater than, you know, 6:00 PM this is not gonna work.
But then if that's true, then put an X and if it's not true, do nothing. So, right. Like we need to figure out how do we get this time out of here and how do we compare it to 6:00 PM Well, what we're going to use is. time value and what time value actually makes it a lot easier for us to do this because in if you check out the doc editor, the Google Sheets help, it shows you a bunch of times in here, well, what about that date?
Do we have to go and extract the date from this? Or, or rather extract just the time. And, you know, if we had to do that, we could probably do split and split. By its space and then take the index of that and only get, you know, the second the first row, but the second item, and boom, we have the actual time.
But what it's, this is in fraction. It's in the style of a fraction of a day, but we don't actually have to do that. Time value is interesting. It has this little note that says dates, for example, the day of the week are ignored. So really all we have to do in order to convert the this timestamp into a fraction of a day is apply the time value formula.
So time value, and we do a one and we get the fraction of the day well. What does this allow us to do and how do we get this into a logical expression that's gonna be true or false based on, is this before or after 6:00 PM Well, let's do, we, I did here the time value of 6:00 PM I just put in 1800 here for the time, and I put the time value of that is 0.75.
So at 6:00 PM Exactly. During the day, we are 75% of the way through the day. Right. That makes sense. Right? Like that is 75% of the day is done and. this fraction 0.54. Right now, it's 1:09 PM It's. 54 and 55% of the way through the day. So we can say this, if this is less than this then it's false. And we are not past eight 6:00 PM So our logical expression, if we wanna put this all into one cell, is going to be if, and we're gonna say if the time value of a one.
less than and we can really just do 0.75 here. But let's do time value of let's just put 1800. I think we can do this. Maybe we wanna change this later. But actually we wanna say if it is, I think it and is actually greater than or equal to that then the value we want. , let's see. Time, value, ah, we didn't end this time value there, so we got time.
Value of a one is greater than or equal to time value of 1600. If it's true, we want to go X, we wanna add an X, and if it's false, we wanna do absolutely nothing. We want nothing there. So right now, this 13. 10 28 is 54% of the way through the day. It's less than 75. So we have nothing. Well, let's see what happens if we put it here.
We have an X and if we have 1900, we have X here. So this formula here allows us to put that X there if it is greater than or equal to 1800. And what's interesting here too is this 1800. Typed it in, but we can also apply it to some let's do a six there. So it's a sorry, a five. I meant a five.
There we go. So now it's applied to here. So now I can put 1800 in here. And now this is nothing, nothing, nothing. And that's going to be, whoops. No, I know what I did. I didn't set this to whoop any dollar signs there. and now you should get nothing. Nothing and something great. So we can hard code this not hard code.
This push the, put this to one cell. Maybe it's on the settings page. Maybe it's on a part of your page. Maybe it's a header. Like you want to know, you know, is it 1800, 1900, 2100? What is it between? You wanna market, Hey, this is an X here it is, starting at this. You can set that header to this time, or, and it's malleable fu fungible or you can just hard code this, right?
We can just do 18, 0, 0, 0, 0. There you go. And if you have any different way to do time, if it's through semicolons or something Then you, then it'll be your format, whatever your sheet is based on whatever format you're ba your sheet is based on. So hopefully this is helpful to get time. This helps you get time from a timestamp with this time value, which it becomes a duration of the day between zero and one.
And then you can apply it to other time values where when, you know. Okay, 1800 is 75% during 0.75. It is some number between the day between zero and one. Thanks so much for watching.
But then if that's true, then put an X and if it's not true, do nothing. So, right. Like we need to figure out how do we get this time out of here and how do we compare it to 6:00 PM Well, what we're going to use is. time value and what time value actually makes it a lot easier for us to do this because in if you check out the doc editor, the Google Sheets help, it shows you a bunch of times in here, well, what about that date?
Do we have to go and extract the date from this? Or, or rather extract just the time. And, you know, if we had to do that, we could probably do split and split. By its space and then take the index of that and only get, you know, the second the first row, but the second item, and boom, we have the actual time.
But what it's, this is in fraction. It's in the style of a fraction of a day, but we don't actually have to do that. Time value is interesting. It has this little note that says dates, for example, the day of the week are ignored. So really all we have to do in order to convert the this timestamp into a fraction of a day is apply the time value formula.
So time value, and we do a one and we get the fraction of the day well. What does this allow us to do and how do we get this into a logical expression that's gonna be true or false based on, is this before or after 6:00 PM Well, let's do, we, I did here the time value of 6:00 PM I just put in 1800 here for the time, and I put the time value of that is 0.75.
So at 6:00 PM Exactly. During the day, we are 75% of the way through the day. Right. That makes sense. Right? Like that is 75% of the day is done and. this fraction 0.54. Right now, it's 1:09 PM It's. 54 and 55% of the way through the day. So we can say this, if this is less than this then it's false. And we are not past eight 6:00 PM So our logical expression, if we wanna put this all into one cell, is going to be if, and we're gonna say if the time value of a one.
less than and we can really just do 0.75 here. But let's do time value of let's just put 1800. I think we can do this. Maybe we wanna change this later. But actually we wanna say if it is, I think it and is actually greater than or equal to that then the value we want. , let's see. Time, value, ah, we didn't end this time value there, so we got time.
Value of a one is greater than or equal to time value of 1600. If it's true, we want to go X, we wanna add an X, and if it's false, we wanna do absolutely nothing. We want nothing there. So right now, this 13. 10 28 is 54% of the way through the day. It's less than 75. So we have nothing. Well, let's see what happens if we put it here.
We have an X and if we have 1900, we have X here. So this formula here allows us to put that X there if it is greater than or equal to 1800. And what's interesting here too is this 1800. Typed it in, but we can also apply it to some let's do a six there. So it's a sorry, a five. I meant a five.
There we go. So now it's applied to here. So now I can put 1800 in here. And now this is nothing, nothing, nothing. And that's going to be, whoops. No, I know what I did. I didn't set this to whoop any dollar signs there. and now you should get nothing. Nothing and something great. So we can hard code this not hard code.
This push the, put this to one cell. Maybe it's on the settings page. Maybe it's on a part of your page. Maybe it's a header. Like you want to know, you know, is it 1800, 1900, 2100? What is it between? You wanna market, Hey, this is an X here it is, starting at this. You can set that header to this time, or, and it's malleable fu fungible or you can just hard code this, right?
We can just do 18, 0, 0, 0, 0. There you go. And if you have any different way to do time, if it's through semicolons or something Then you, then it'll be your format, whatever your sheet is based on whatever format you're ba your sheet is based on. So hopefully this is helpful to get time. This helps you get time from a timestamp with this time value, which it becomes a duration of the day between zero and one.
And then you can apply it to other time values where when, you know. Okay, 1800 is 75% during 0.75. It is some number between the day between zero and one. Thanks so much for watching.
Topics