Excel Access 固定長の数字を日時に変換する完全に新しく簡単で強力な手法 How to Make Fixed Number string to Date / DateTime Absolutely New

現在のところ地球上でここしか解説していません

というか検索が難しいからだと思いますが、発見できません。現在のところ全く未知の手法です。いわれてみればああそうかと思うかもしれません。ただ、これはOfficeのロケールが関与しているのかもしれません。
追記
https://www.tipsfound.com/excel/02106
ここはAccessのSQLと同じエスケープ文字で切っています。

概要

Excelのセルで数字を日付に変えるのは
=DATEVALUE(TEXT(C2,"####""/""##""/""##"))
AccessのSQLで数字を日付に変えるのは
CDate(Format([F01Text],"####""/""##""/""##")
と入力すればよい。なお、Accessの場合は自動的にエスケープ文字列に変わる。
日時の場合は
=DATEVALUE(TEXT("20190515051930","####/##/##"" ""##:##:##"))
またVBAマクロの場合は
Debug.Print CDate(Format("20190515", "####""/""##""/""##", vbSunday))

Cdate(Format("20190515051930", "####/##/##"" ""##:##:##"))

これまで知られている通常のテキスト文字列と数値を日付に変換する方法

Traditinal:How to convert a number into a date? / How to Change Number to Date Format yyyy/mm/dd
公式サイトではワークシート関数であるDate関数を用いた方法を紹介しています。。セルC2に20190501と入っているとします。

WorksheetFunction.DATE 関数

別のプログラムで作成されたファイルを開くと、Excel はデータに含まれる日付を認識しようと試みます。ただし、日付を認識できない場合があります。原因として、数値に一般的な日付との共通点がないことや、データが文字列として書式設定されていることが考えられます。このような場合は、DATE 関数を使って情報を日付に変換できます。たとえば次の図では、セル C2 には日付が含まれており、その形式は YYYYMMDD で、文字列として書式設定されています。これを日付に変換するために、DATE 関数を LEFT、MID、RIGHT 関数と組み合わせて使っています。

C2に20190501と入っているとすると
=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))
と別のセルに数式を入れると日付のシリアル値になります。これが今まで数字を日付に変える方法を検索したらあたる手法です。ほかの言語でも似たような手法が使われますね。

完全に新しく簡単で強力な手法

Completely new, simple and powerful method
固定長の数字を日付に変えます。年月日の順に並んでいるとします。
Fixed Length numbers are in order of year / month / day, Such as
20190501
これがC2に入っているとすると、
=DATEVALUE(TEXT(C2,"####""/""##""/""##"))
そして表示形式を短い日付にします。
どうです？もう字数を気にする必要はありません。すごく簡単です。スラッシュをダブルクォーテーション2つで囲むのがポイントです。
DateValueも面倒なら
=TEXT(C2,"####""/""##""/""##")*1
とセルに入れて、表示形式を短い日付にするとOKです。(ただしこの方法はVBAのコードでは使えません。

仕組み

固定長の数字をスラッシュで切ることにより日付に近いテキスト文字列ができます。このためDateValueは日付のシリアル値に変換することができます。
=DATEVALUE(TEXT(C2,"####""/""##""/""##"))
またExcelは自動的に型変換(Cast)を行う機能があり、*1とすることで文字列を日付や数字として認識します。
=TEXT(C2,"####""/""##""/""##")*1

VBAでも可能

それではVBA、マクロではどうなのでしょうか？
Debug.Print CDate(Format("20190515", "####""/""##""/""##", vbSunday))
できます。DateValueに相当するのがCDateといえますね。
当然ですが、VBA（マクロ）の方が1900年以前の西暦でも対応します。

日付と時間でもワークシート関数、VBAともに可能

さらに日付と時間ではどうでしょうか。
=TEXT("20190515051930","####""/""##""/""##"" ""##"":""##"":""##")*1
Yes, 時刻にもできました。*１でOKです
ポイントは半角のスペースを/""##"" ""##""とこのように二重のダブルクォーテーションの間にいれることです。
CDate(Format("20190515051930", "####""/""##""/""##"" ""##"":""##"":""##"))
Yeeees,行けます。

VBAでは*1では変換されない

VBAの場合はCDateを使わないと日付になりません。
`Format("20190515091500", "####/##/## ##:##:##")*1'

エスケープ文字列はVBAでは失敗する

しかし以下のようにエスケープ文字列を使う日付、または日時変換は失敗します。
CDate(Format("20190515051930", "####!/##!/##"" ""##!:##!:##"))
CDate(Format("20190515", "####!/##!/##"))
エスケープ文字を使う例は失敗します。
Cdate(Format("20190515051930", "####/##/##"" ""##:##:##"))
こちらは成功します。
理由は不明ですが、Excelのワークシート関数のDateValue、AccessのSQLのCDateはエスケープ文字が有効で、VBAでは同じエスケープ文字が無効だということになります。SQLのCDateは同じ名前でややこしいですが、VBAかそうでないか、でわけて考えるといいと思います。
以上からExcelにおいてはエスケープ文字を使わない方法がVBA、ワークシートともに使えるということが分かります。

SQLでも日付変換可能

テーブル１の[F01Text]に20190501
異なるのはナンバー記号ではなく0を使う点です。
テーブル１の構成
実験のため2列で構成されています。ID列はオートナンバー、F01Textは255文字のテキストです。
ID F01Text

これで選択クエリを作り、デザインで式ビルダを使用して入れます。

式ビルダに入力、しかし上記のように入れても'\1'に変換されます

式1: CDate(Format([F01Text],"0000\/00\/00"))
でもエスケープ文字を使ってますねこれは
とこの記事を最初に書いたときは思いましたが、
上記の
https://www.tipsfound.com/excel/02106
でもエスケープ文字を使った方法があるので、Accessだけとは言えないようです。
Accessは式ビルダで入力しても勝手に変換される点は非常に興味深いです。

SELECT [テーブル1].[ID], [テーブル1].[F01Text], CDate(Format([F01Text],"0000\/00\/00")) AS 式1
FROM テーブル1;

Microsoftはなぜこんな便利な方法を隠蔽しているのか

ロケールで限定的に有効で一般的ではない

PowerShellで日付書式にカスタム書式パターンを指定する

[DateTime]::ParseExact("20130209","yyyyMMdd",$null)

この方法はこれに似ています。
またこのParseExactがCulturalと近い関係にあります。
DateTime.ParseExactメソッドで、1900年代の日時に変換される
http://atsukanrock.hatenablog.com/entry/20090318/1237354707

.NETで文字列を日時の値に変換するには、DateTime.ParseExactメソッドを使用するのが普通だ。このメソッドは、第2引数に日付と時刻の書式指定文字列を指定する。その指定をカスタム DateTime 書式指定文字列とした場合、注意が必要だ。
年の解析でyy（年の下2桁）を指定した場合、29までの値は2000年代に、30以降の値は1900年代に変換されるのだ。つまり、以下のサンプルのとおりの動作となる。これは、コードを実行中のマシンのシステム日時などには依存しない（ようだ）。

この説明はVBAのDateSerialと同じです。
どうもここが絡んでいるわけです。また今一つ安定しない場合が多いです。

日本は年月日の配置が特殊？

上記とも関連しますが、Windowsは月日年でしかも年が2桁が好きです。グローバル的にはこれが標準です。
このため年月日表示など変換できるとしても説明するメリットがないわけです。
Convert time fields to strings in Excel
https://support.office.com/en-us/article/convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680
このサイトではセルの文字列 12/31/16 という16の部分は2016年とするということを言っています。やっぱり2桁なんですね。検索しても本当に出てこない。
`Excel number String date !/ Cdate'
https://search.yahoo.com/search;_ylt=Awr9ImKYlWJdS4MAgK9XNyoA;_ylc=X1MDMjc2NjY3OQRfcgMyBGZyA3lmcC10BGZyMgNzYi10b3AEZ3ByaWQDNlRwZktZNWxSRmlPWmRDM0o4QXp0QQRuX3JzbHQDMARuX3N1Z2cDMQRvcmlnaW4Dc2VhcmNoLnlhaG9vLmNvbQRwb3MDMARwcXN0cgMEcHFzdHJsAzAEcXN0cmwDMzMEcXVlcnkDRXhjZWwlMjBudW1iZXIlMjBTdHJpbmclMjBkYXRlJTIwISUyRiUyMENkYXRlBHRfc3RtcAMxNTY2NzQyMDIy;_ylc=X1MDMjc2NjY3OQRfcgMyBGZyA3lmcC10BGZyMgNzYi10b3AEZ3ByaWQDNlRwZktZNWxSRmlPWmRDM0o4QXp0QQRuX3JzbHQDMARuX3N1Z2cDMQRvcmlnaW4Dc2VhcmNoLnlhaG9vLmNvbQRwb3MDMARwcXN0cgMEcHFzdHJsAzAEcXN0cmwDMzMEcXVlcnkDRXhjZWwlMjBudW1iZXIlMjBTdHJpbmclMjBkYXRlJTIwISUyRiUyMENkYXRlBHRfc3RtcAMxNTY2NzQyMDIz?p=Excel+number+String+date+%21%2F+Cdate&fr2=sb-top&fr=yfp-t&fp=1

日本語のGoogleでは出てこない、英語のYahoo.comでも出てこない。こういう状態です。本当に不思議ですね。

補足

!はエスケープなのか

機能的にはエスケープなのですが、不思議なことにこれを明言している資料がない
http://office-qa.com/Excel/ex295.htm

=TEXT(161231,"00!/00!/00") → 16/12/31 に変換 （※まだシリアル値ではない）

16/12/31は日付文字列なので、DATEVALUE関数で日付（シリアル値）に変換できます。

※!は「!」につづく1文字をそのまま表示するという意味です。

エスケープであることを説明しているのはここだけですね。

時刻はあった

Function GetMyTimeField()
    Dim myTime As Date, myStrTime As String

    myTime = [A1]
    myStrTime = Format(myTime, "hh:mm")
    Debug.Print myStrTime & " Nice!"

End Function

VBAならミリ秒もOK、ワークシート関数では数字を文字列として入力しているとできる。

これ自体は文字列にしかなりませんが、ミリ秒まで表示することはできます。
TypeNameはStringを返し、CDateではエラーになります。
つまり固定長で計算した結果を出せば
Format(cstr("20190515051930259"), "####""/""##""/""##"" ""##"":""##"":""##"".""###")
このような形でミリ秒まで含めて変換できます。
セルの場合は数字ではなく、テキストになっている場合は有効です。（やはり文字列になります。）
これはミリ秒まで考えると桁が足りなくなるためだと考えられます。17桁の数字が必要で、Excelは有効数字は15桁しかありません。