2

I want to enter dates in yyyymmdd format, but have Google Sheets format the values as yyyy-mm-dd.

I don't want to enter any dashes (or slashes, or spaces) because it slows down my data entry, but instead have Sheets add them automatically between the year, month, day.

For example, I want to type 20231203 and see 2023-12-03 in the cell.

I tried using the date format yyyy-mm-dd but it converts 20231203 to 57291-02-23.

Any ideas are appreciated.

1
  • I'm not sure how fast you type. Would be interesting to know if the script-based solution can keep up. I can't outpace it myself...
    – Blindspots
    Commented Dec 6, 2023 at 3:27

5 Answers 5

4

One approach might be to use one column formatted as plain text to enter the dates in yyyymmdd format, then use an array formula in another column to generate the dates in the correct format. For instance:

  1. Select column A and use Format/Number/Plain text to force all cells to plain text
  2. Select column B and use Format/Number/Custom date and time and select the yyyy-mm-dd format, then in B1 use the following formula: =arrayformula(iferror(date(left(A1:A,4),mid(A1:A,5,2),right(A1:A,2))))
1
  • 1
    Good catch. Probably worth stating that if you wanted to do something similar with dates in ddmmyyyy format then in this case you would need to use plain text format for the input cells, as otherwise you'd run into problems with the leading zero of single digit days being omitted (and so all the digits being out by one) Commented Dec 4, 2023 at 15:07
2

You can format those numbers so that they look like 2023-12-03 like this:

Format > Number > Custom number format > 0000-00-00

That will not convert the values to dates, though. The underlying value will remain a largish number like 20231203 (twenty million and change.)

To convert the values to dates, use this formula in another column:

=to_date(value(to_text(D2)))

You can format the new column as yyyy-MM-dd and use it in formulas downstream instead of referring to the original column.

See Working with date and time values in Google Sheets.

2

You can enter numbers as numbers...

Enter 20231225 in A1, and in an adjacent cell, add the following formula.

=DATE(A1/10000, MOD(INT(A1/100),100), MOD(A1,100))

If your dates are all in this century, you could save two keystrokes per entry with:

=DATE( 100 + A1/10000, MOD(INT(A1/100),100), MOD(A1,100))

This converts 231225 to the SS's value for 25 Dec 2023.
Merry Christmas!


I'm indebted to other answers here for showing ARRAYFORMULA() and its use. (Nice!) Wish this had been available decades ago when creating spreadsheets was a frequent task.

1
1

@FE2O3's answer is elegant in its simplicity, employing a single mathematical formula to convert the initial number into a datevalue, without the need for special formatting.

It requires a helper column, as do the other solutions including my regex-based one, so I adapted it into a custom function that uses an onEdit simple trigger.

Sheets will return the datevalue in the default date format if the column number format is set to automatic (default). Alternatively, the column can be custom formatted (eg. yyyy-mm-dd).

FE2O3's Formula

=DATE(n/10000, MOD(INT(n/100),100), MOD(n,100))

Custom Function

function onEdit(e) {

  if (e) {
 
    const range = e.range;
    const value = e.value || range.getValue().toString();
    const sheet = range.getSheet();
    const col = range.getColumn();
    const names = ["Sheet1", "Sheet2"];
    const columns = [ 2 ];
 
    if ((names.indexOf(sheet.getName()) > -1) &&        // valid sheet
        (columns.indexOf(col) > -1) &&                  // valid column
         value.match(/^(2[01]\d\d[01]\d[0-3]\d)$/)) {   // date string          

      // number to datevalue formula  
      // @Fe2O3 https://webapps.stackexchange.com/a/173128
      // =DATE(n/10000, MOD(INT(n/100),100), MOD(n,100))
   
      const date = [(value/10000).toFixed(0),
                    (value/100).toFixed(0) % 100,
                    (value % 100)];
 
      const newDate = new Date([date]);

      if ((newDate.getFullYear() == date[0]) &&
          (newDate.getMonth()+1 == date[1])  &&
          (newDate.getDate() == date[2])) {
       
        sheet.getRange(range.getRow(), col).setValue(newDate);

      } else {

        sheet.getRange(range.getRow(), col).setFontColor("red");

        throw new Error("Invalid or mismatched newDate parsed from e.value:" +  '\n' +
                        "e.value:  " + value + '\n' + "newDate:  " + newDate + '\n' );
                       
      }    
   
    }

  }

}  

Conditions

The conditions for the script to modify an edited value:

  1. The value is a group of 8 numbers that follow the pattern 2###m#d# where m is either 0 or 1 and d is a a number from 0 to 3
  2. The value is in a sheet whose name is in the array names. To remove this condition, remove (names.indexOf(sheet.getName()) > - 1)
  3. The value is in a column number that is in the array columns. To remove this condition, remove (columns.indexOf(col) > -1)
5
  • 1
    Very nice. Thank you for the mention. :-)... It's a bit of code... Hope the OP and subsequent readers have lots of data to recoup the keystrokes! :-) Cheers!
    – Fe2O3
    Commented Dec 6, 2023 at 4:51
  • 1
    I am assuming the question is based on significant data entry given the fact that they are complicating their life in order to avoid an additional 2 keystrokes per date entered, and the relevant keys are co-located on the numpad. The code would be shorter knowing the specific parameters of the use case but, In any case, the lion's share of the keystrokes invested are mine and yours rather than theirs ;-)
    – Blindspots
    Commented Dec 6, 2023 at 20:05
  • 1
    You can replace the Math.log() bit with e.value.length. See these onEdit(e) optimization tips. Commented Dec 7, 2023 at 22:32
  • 1
    The best practice is to use e instead of API calls like range.getValue(). One complication is that e.value is always a text string. It represents the text the user entered in the cell, verbatim, and is thus never a number. Because of this, Number.isInteger(e.value) will always return false. I would usually test with something like e.value && e.value.match(/^(2[01]\d\d[01]\d[0-3]\d)$/). To do it with coercion to a number, get the value with Number(e.value) and additionally test the result with Number.isNaN(). Commented Dec 8, 2023 at 9:42
  • Formula edited to test for a valid date. If the value passes initial tests but fails to parse to a valid date (too many months/days etc) then an error is thrown in the log and the edited cell font is changed to red instead but no other transformation is performed.
    – Blindspots
    Commented Dec 14, 2023 at 0:54
0

A similar approach (but longer) to @The God of Biscuits' solution uses REGEXREPLACE to divide up the initial value.

Initial value can be a number or text, and results column should be formatted as dates.

=ARRAYFORMULA(
   IFERROR(
     REGEXREPLACE(
       TEXT(1/(1/A:A), "@"),
       "^(\d{4})(\d{2})(\d{2})",
        "$1-$2-$3")/1)) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.