excel の令和対応チェック
以下の表をエクセルに貼り付けて、「判定」列がTRUEならOK。
ここから
平成～令和
| 西暦 | 和暦 | 判定 |
|---|---|---|
| 2019/05/03 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="令和1年05月03日" |
| 2019/05/02 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="令和1年05月02日" |
| 2019/05/01 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="令和1年05月01日" |
| 2019/04/30 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="平成31年04月30日" |
| 2019/04/29 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="平成31年04月29日" |
| 2019/04/28 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="平成31年04月28日" |
昭和～平成（おまけ）
| 西暦 | 和暦 | 判定 |
|---|---|---|
| 1989/01/10 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="平成1年01月10日" |
| 1989/01/09 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="平成1年01月09日" |
| 1989/01/08 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="平成1年01月08日" |
| 1989/01/07 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="昭和64年01月07日" |
| 1989/01/06 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="昭和64年01月06日" |
| 1989/01/05 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="昭和64年01月05日" |
大正～昭和（おまけ）
| 西暦 | 和暦 | 判定 |
|---|---|---|
| 1926/12/27 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="昭和1年12月27日" |
| 1926/12/26 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="昭和1年12月26日" |
| 1926/12/25 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="昭和1年12月25日" |
| 1926/12/24 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="大正15年12月24日" |
| 1926/12/23 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="大正15年12月23日" |
| 1926/12/22 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="大正15年12月22日" |
明治～大正（おまけ）
| 西暦 | 和暦 | 判定 |
|---|---|---|
| 1912/08/01 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="大正1年08月01日" |
| 1912/07/31 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="大正1年07月31日" |
| 1912/07/30 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="大正1年07月30日" |
| 1912/07/29 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="明治45年07月29日" |
| 1912/07/28 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="明治45年07月28日" |
| 1912/07/27 | =TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"[$-411]ggge年mm月dd日") | =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="明治45年07月27日" |
ここまで
ドラッグしてコピー、エクセルに貼り付け（貼り付け先の書式に合わせる）！