How to format Dates in your Google Sheet
To display your data from Google Sheets the way you'd like, you need to include entries in your Google Sheet that specify which date(s) you want to push your data to.
Dates in your Google Sheets should be formatted as mm/dd/yyyy or dd/mm/yyyy. Either way, the Date needs to include information on the day, month and year that this Metric value should be pushed to.
Pro Tip: With the appropriate Language Settings in Google Sheets, you can use . or / as separators. This means the following date formats are accepted by Databox:
If you are using specific Date formats in Google Sheets that are not formatted in a way that works with Databox, it's recommended that you create an additional column in your Google Sheet for Dates that can be used with Databox.
To automate this, you can use the MID formula in Google Sheets to adapt your custom Date entries into a Date format that's accepted by Databox. Using the ARRAYFORMULA in combination with the DATEVALUE formula can also be useful in case you need to apply the Date Format across other cells.
To do this, follow the steps outlined below.
string – signifies the cell, where you want Date data to be extracted from (this is typically 1 cell over from where you're entering the formula)
starting_at – signifies the index (starting from the left) of the String that will be copied to the cell where you're entering the formula (the first character in the String has the index 1)
extract_length - signifies the length of the String to copy to the cell where you're entering the formula
This formula will extract content from cell A2, starting from the 5th character in the string, and it will copy 13 characters from there on.
"Tue, Mar 10, 2020, 5:00:00 AM"
Don't stop there! There are a few more steps to automate the Date Format across other cells in Google Sheets to convert this text to a Date in Google Sheets.
You can then drag this setting across other cells.