Using dates in Google Sheets is critical for small business and all users. Come with us as we answer 5 questions about dates and Google Sheets that will upgrade your skills to the next level. Let's get started! To watch the video version of this blog click here or continue reading below...
These 5 questions go from easy to more difficult and build, so make sure you read the full post to get the full benefit. Our first question is
How do I put a date format in Google Sheets?
There's a few different ways, But the easiest way is just to enter the date . But we
enter date in different formats. So, I could do month, day, year, with slashes. I could do the full two-month digit, 2-digit day, and 4-digit year and get a different format. I could also go year first, month, day and get a third format. I could write it out. I could write it out and get a fourth format. So, there's many different ways that we can enter dates into Google Sheets and Google Sheets will understand what you're writing and provide the format that you request through what you type in. So now that we have these formats sitting in Google Sheets.
How do I change a date format in Google Sheets?
To answer the second question, we will go to format on the menu bar and hover over number and what we can do is we can just click on date the format, and you'll see that didn't change much there. But if I go to format here, number and date it will change my format. but maybe we don't want month. day, year format without the leading zeros. Maybe we want the leading zeros in our format. So, we can go format. hover on number. scroll down and use this date and there we get the leading zeros. We can hover over number again and use this date format and knock off the first two digits of the year, but keep the leading zeros.
There's also... let's put January 1 in here there's also a custom way to format dates by hovering over number, going to custom date and time. And now you can choose the date that you would like and then adjust to it. So, for example, you can choose this 8 5 30 month. day. year. but maybe you don't want the dashes. maybe you want to use periods. and you can apply that way and you can see the decimal points are sitting in there in that date format. So, there's a few different ways to change your date format in Google Sheets.
Now that we've looked at how to format,
How do you do a Date Formula in Google Sheets?
For me what this means is how do I enter in a date for today when I don't know when today is. Maybe I'm using the sheet over a period of time and I need to understand a relationship between dates in the past and today's date . For example, maybe I have a list of inventory and I have a ball, a glove, and a bat. and the date that they were in inventory or date manufactured was 7/1/2024. July/3/2024. and July 9. 2024. and I wanna know how long this ball, glove, and bat have been in inventory. What I can do is, I can use dates to figure this out. But what I need to understand is, and what the sheet needs to understand is what is today's date. So, the best way to do this date formula is =today() and you can get today's date: July 11. Now what you can also do is you can play with this a little bit and you can do today minus 5 and get July 6, or you can do plus 7 and get July 18th.
So there's a lot of different ways to use this today formula in Google Sheets. But this really helps us to understand the relationship between the date the ball glove and bat were manufactured and today to learn how many days the ball, glove, and bat have been an inventory.
So before we get to the next question on our journey.
Are you liking this format? We're building a website and YouTube channel for people in small businesses learning Google Sheets. Learning Google Sheets is easy. We at Small Business COO are here to show you the way. Subscribe/Join today! So when you search for Google Sheets we come up on the top of your search results and you can learn more Google Sheets.
So, the fourth question we've got is
How do I Calculate Days in Google Sheets?
So we've talked about the today function and we've got these dates manufactured. Let's go into the next step and actually use this today function in our little sheet here. So I've got days in inventory and what I can do is I can take today's date D2 minus the date manufactured. And I can understand that it's 10 days in inventory. The autofill isn't working. So we're gonna ignore that by clicking escape. And I'm going to do this a different way and I'm gonna say =Today()-D8. and I get 8 days in inventory. By hitting Ctrl-D I can copy that formula and see that the bat has been in inventory for 2 days.
So that is how you can use the today formula to help understand relationships between dates and your spreadsheet. So, this converted the dates July 1 and July 11, July 3 and July 11, July 9 and July 11, relationships into a number.
How Do I Convert a Date to a Number in Google Sheets?
But say you have a date in Google Sheets and you just want to know what the numeric value of that is. You can convert this by using format, number, and just changing it to number here. So you can see 45,474. Again, these dates are set up to have a certain numeric value. Every date, ever existed, has a certain numeric value. If I undo,
July 1, 2024 is the numeric value 45,474. And that's how these mathematical formulas can work with dates.
Before we get to the last question we want to say thank you for reading. Check out this Google Sheet Template offer as a show of our appreciation.
Okay, the last question,
How do I add a Series of Dates in Google Sheets Easily?
So let's continue on in our training, and say we have July 1, 2024. In order to get a series of dates you can grab this anchor here and you can just drag it down and you can see it fills in July 1 through July 13 based on the series that you've got.
The other thing that I can do with dates is if I don't know what July 1 is gonna be but I want to mathematically... say I wanna do this =today(), and it's July 11 today, but the next time I open the sheet it might be a different day. But I want a series of dates going into the past. I can use =H6-1. and then I'll get July 10. And then I can copy and paste down and go back in time one day each cell. So that is another good way to add a series of dates in Google Sheets easily.
Comments