日付入力で自動的に月末日にしたい

商品の使用期限入力を簡略化したいと思います。

使用期限は、例えば2020年3月31日　のように必ず月末の日になります。
大量に入力するため、キータッチ数をできるだけ少なくしたいと思います。

例えば、2020年3月31日の場合、203の入力だけでよいようなセルの書式を設定はできるでしょうか？
入力結果のセル内表示は2020.3.31でも2020.3または20.3でも構いません（データとして末日が入っていれば）

セルの書式設定の日付で月までの表示を選ぶと、日は自動で初日（1日）になってしまします。

よろしくおねがいします

No.10 ベストアンサー 回答者： GooUserラック 回答日時： 2017/12/20 23:28

No.5 のお礼について

多分コードを書き込むところが違っていると思います。
下図の赤矢印の所をダブルクリックして上の｢Worksheet｣｢Change｣を選択したところに書き込んで下さい。（もちろん矢印のシートは対象のシートにしてください）

この回答へのお礼

本当に親切に教えていただきありがとうございました。すべてうまくいきました。感謝します。

これで、仕事がの作業効率が上がります。ありがとうございました

お礼日時：2017/12/20 23:51

No.9 回答者： GooUserラック 回答日時： 2017/12/20 22:57

No.5 の追加

｢Application.EnableEvents = False｣と｢If Target.Column = 1 Then｣の間に｢If Left(Target.Text, 1) = "#" Then GoTo エラー処理｣を追加してください。大きな数の入力時に停止してしまうのを防げます。

No.8 回答者： GooUserラック 回答日時： 2017/12/20 22:22

No.5 の追加

大きな数を入力するとエラーしてしまって、中断させると次回からイベントが発生しなくなってしまうので、それを解除するものです。標準モジュールにおいてエラー後には必ず実行してください。
--------------------------------------------------------------------------------
Sub イベント再開()
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------

この回答へのお礼

ありがとうございます。新しいbookに貼り付けるとうまくいきました。
ただ、今作成中で、すでにVBAが書かれているEnd subの下に貼り付けると表示がおかしくなってしまします。
内容をここに貼り付けたいのですが、字数オーバーになりできません

お礼日時：2017/12/20 23:06

No.7 回答者： mike_g 回答日時： 2017/12/20 20:35

》 203の入力だけでよいようなセルの書式を設定はできるでしょうか？

入力欄をＡ列、隣のＢ列に計算された日付を表示するってのは如何？
式 =IFERROR(EOMONTH(DATE(2000+LEFT(A2,2),RIGHT(A2,LEN(A2)-2),1),0),"") をセル B2 に入力しておき、数値 203 をセル A2 に入力するのです。Ｂ列の書式を予め yyyy.m.d でも yyyy.m または yy.m にしておけば御の字でしょ？

この回答へのお礼

ありがとうございました。
試してみます。
先ずはお礼まで

お礼日時：2017/12/20 23:57

No.6 回答者： GooUserラック 回答日時： 2017/12/20 20:13

No.5 の訂正です。

すみません説明の記入ミスがありました。

「※ 今年以外ならば｢yy.mm｣形式で入力してください。例：2018年1月⇒｢08.01｣、2018年10月⇒｢08.10｣または｢08.1｣（｢08.1｣は使わない方が良いと思います）」は、もちろん「※ 今年以外ならば｢yy.mm｣形式で入力してください。例：2018年1月⇒｢18.01｣、2018年10月⇒｢18.10｣または｢18.1｣（｢18.1｣は使わない方が良いと思います）」の間違いです。

No.5 回答者： GooUserラック 回答日時： 2017/12/20 20:10

こんなのはいかがでしょうか？

--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Str_入力 As String
Dim Str_年月 As String
Application.EnableEvents = False
If Target.Column = 1 Then
If IsDate(Target.Value) Then
Str_入力 = CStr(Format(Target.Value, "00.00"))
If Len(Str_入力) > 5 Then
GoTo エラー処理
End If
If Right(Str_入力, 2) = "00" Then
Str_年月 = Format(Date, "yyyy") & "/" & Left(Str_入力, 2) & "/01"
If IsDate(Str_年月) Then
Target.Value = DateAdd("m", 1, CDate(Str_年月)) - 1
Else
GoTo エラー処理
End If
Else
Str_年月 = Left(Format(Date, "yyyy"), 2) & Left(Str_入力, 2) & "/" & Right(Str_入力, 2) & "/01"
If IsDate(Str_年月) Then
Target.Value = DateAdd("m", 1, CDate(Str_年月)) - 1
Else
GoTo エラー処理
End If
End If
End If
End If
Application.EnableEvents = True
Exit Sub
エラー処理:
Target.ClearContents
Target.Select
MsgBox ("入力は｢yy.mm｣または｢m｣形式で入力してください。")
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------
※ A列を対象にしています。他の列でしたら｢If Target.Column = 1 Then｣の｢1｣を対象の列番号にしてください。
※ 対象のセルの書式は｢yyyy/mm/dd｣のような日付形式にしておいてください。
※ 今年なら｢m｣形式で入力してください。例：1月⇒｢1｣または｢01｣、10月⇒｢10｣
※ 今年以外ならば｢yy.mm｣形式で入力してください。例：2018年1月⇒｢08.01｣、2018年10月⇒｢08.10｣または｢08.1｣（｢08.1｣は使わない方が良いと思います）
※ 違う形式で入力した場合、数値の場合はエラーメッセージを表示してクリアします。文字の場合はそのまま表示されます。

この回答へのお礼

お礼を書く場所を間違えました。

ありがとうございます。新しいbookに貼り付けるとうまくいきました。
ただ、今作成中で、すでにVBAが書かれているEnd subの下に貼り付けると表示がおかしくなってしまします。
内容をここに貼り付けたいのですが、字数オーバーになりできません

お礼日時：2017/12/20 23:08

No.4 回答者： fujillin 回答日時： 2017/12/20 14:17

こんにちは

以下は、シートマクロで実現する例ですが、対象セルの書式を「文字列」にしておく条件で作成しています。
対象セルの書式が日付でも可能ですが、誤入力をした場合に気が付きにくくなるため、敢えて文字列書式としています。

日付書式とした場合、仮に、「1234」のような誤入力をした際に、マクロでは処理を無視するようにしていますが、エクセルが日付に直して「1903/5/19」のような表示に変えてしまいます。
日付をきちんとチェックすれば気が付きますが、表示が日付形式に変わるのでうっかりすると間違えたことに気付かないという状態が発生する考えたからです。
文字列書式の場合は、「1234」と入力した場合に、マクロは処理を行わないので「1234」の表示のまま残りますので、気付きやすいであろうと考えました。
このため、結果として表示される日付はいわゆるシリアル値ではなく、文字列となっていますのでご注意ください。
（表示用文字列の書式は、通常の書式フォーマットと同様に指定可能です）

セルの書式を日付とした場合でも同様の処理は可能ですが、上記への対策として、誤入力と見做した場合には表示色を変えるなどとすることで気付きやすくしてあげることが必要そうに思います。


対象とするセル範囲は、仮にA列として設定してあります。
※ 対象とするシートのシートモジュールに記載のこと。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tRange As Range, r As Range, c As Range
Dim y As String, m As String

Const form = "yyyy/mm/dd" '←表示用の書式設定
Set tRange = Columns(1) '←対象とするセル範囲（仮設定：A列）

Set r = Intersect(Target, tRange)
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In r
　If Len(c.Value) > 2 Then
　　y = Left(c.Value, 2)
　　m = Mid(c.Value, 3)
　　If IsNumeric(y) And IsNumeric(m) Then
　　　If CInt(m) < 13 Then
　　　　c.Value = Format(DateSerial(2000 + CInt(y), CInt(m) + 1, 1) - 1, form)
　　　End If
　　End If
　End If
Next c
Application.EnableEvents = True
End Sub

この回答へのお礼

ありがとうございます。入力ミスにまで気をまわしてくれて恐縮です。
文字列で処理後、期限切れ商品の抽出する場合、なた、あとから追加されるデータがＣＳＶ表データで、使用期限の列が日付データであることを考えてよく検討してみます。

とりあえずお礼まで。

お礼日時：2017/12/20 14:55

No.3 回答者： GooUserラック 回答日時： 2017/12/20 12:52

マクロ（ＶＢＡ）を使えば割と簡単に出来ますが、使っても良いのでしょうか？

この回答へのお礼

ＶＢＡでトライしてみます

お礼日時：2017/12/20 14:50

No.2 回答者： chonami 回答日時： 2017/12/20 11:21

入れる日付が必ず月末なら、何年分か書き出しても100も200もあるわけではないから、例えば

203→2020/3/31
204→2020/4/30

のようにパターン化し条件付き書式で、
=A1=203
の時に表示形式のユーザー定義で「”2020/3/31”」と設定してしまう。これをすべてのパターン分登録する、とかなら可能でしょうかね。
ただし、もちろん日付データとしては扱えませんし実際のデータは203のままですが、それに関しては問題ないということでしたね？

この回答へのお礼

ありがとうございました。
文字や、数値の方が化けなくてよいかもしれませんね。

お礼日時：2017/12/20 23:54

No.1 回答者： chonami 回答日時： 2017/12/20 11:10

別セルならできますが、入力値と実際のデータを同セルで変えるのは一般機能では難しいのでは？

この回答へのお礼

ありがとうございました

お礼日時：2017/12/20 14:48