SUMIF(.)net

Have you ever had to fix a bad date format in excel or google sheets?

As anyone who works with data knows, sometimes the data you receive isn’t quite in the format you need it to be in. This can be especially frustrating when it comes to dates, as they are often used in calculations and analyses.

Take the following example: you receive an export from Google Analytics with a column of dates that looks like this: “20221001”. At first glance, it’s hard to even tell what this date is supposed to represent. Is it a date? Is it a phone number? Is it an ATM pin?

This is where the power of Google Sheets comes in. We can use a combination of three formulas – LEFT, MID, and RIGHT – to extract the year, month, and day from this jumbled mess and turn it into a proper date.

Here’s how it works:

1. First, we’ll use the LEFT formula to grab the first four characters of the cell, which will give us the year. The LEFT formula takes two arguments: the cell you want to extract from, and the number of characters you want to extract. So for our example, the formula would look like this: =LEFT(A1, 4), where A1 is the cell containing the jumbled date.
2. Next, we’ll use the MID formula to extract the month. The MID formula also takes two arguments: the cell you want to extract from, and the starting position and number of characters you want to extract. To extract the month, we’ll start at the fifth character and grab two characters. So for our example, the formula would look like this: =MID(A1, 5, 2).
3. Finally, we’ll use the RIGHT formula to extract the day. Like the LEFT formula, the RIGHT formula takes two arguments: the cell you want to extract from, and the number of characters you want to extract. To extract the day, we’ll grab the two characters from the right. So for our example, the formula would look like this: =RIGHT(A1, 2).

Now that we’ve extracted the year, month, and day from our jumbled date, we can use the DATE formula to turn it into a proper date. The DATE formula takes three arguments: the year, month, and day. So for our example, the formula would look like this: =DATE(A1, B1, C1), where A1 is the cell containing the year, B1 is the cell containing the month, and C1 is the cell containing the day.

And that’s it! With just a few simple formulas, we’ve taken a jumbled mess of a date and turned it into something we can work with. So the next time you receive a weird date-format that you can’t make sense of, just remember: Google Sheets has your back.

I hope this tutorial has been helpful! Until next time.