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:
- dd/mm/yyyy
- mm/dd/yyyy
- dd.mm.yyyy
- mm.dd.yyyy
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.
- Create a column in Google Sheets where you will enter Dates to be used with Databox.
- In the first cell of the created column, using the MID formula type in:
= MID(string, starting_at, extract_length)
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
For example:
=MID(A2,5,13)
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. - DATEVALUE formula to convert this text into a Date. Use DATEVALUE in front of the previously obtained value, which you need to put in brackets:
=DATEVALUE(MID(A2,5,13))
This will provide a value for the given cell (a serial number for the given date), which then needs to be formatted as Date. - Go to Format > Number > Date to re-format this cell to a Date that can be used in Databox.
You can then drag this setting across other cells. - You can also use the ARRAYFORMULA to expand the settings across other cells in the column. In that case, make sure that you use the formula in the first row of the column that you want populated with the new Dates data and that the Date Format is selected for all cells in the column:
=ARRAYFORMULA(DATEVALUE(MID(A2:A,5,13)))In the formula, A represents the letter of the existing date column, 2 is the number of the first row in that column, and 5 and 13 are used to obtain the correct offset and length for the string you want to select.
