[[20200603145627]]　『エクセルの日付を変換して、最終的にのぞむ形にし』（052）　ページの最後に飛ぶ

『エクセルの日付を変換して、最終的にのぞむ形にしたい』（052）

ご教示頂けますと幸いです。

・元データ
TimeStanmp, ID
MM/DD/YYYY 00:00:00, XXXXXXX

・Vlookupであてたいデータ
Date, 週表示
YYYY/MM/DD, X月第4週

・最終的な表示データ
ID, Date, 週表示
XXXXXXX, YYYY/MM/DD, X月第4週

・その他条件
元データのTimeStampは日本時間になっているので、
アメリカ時間に変更（例＋16時間）
最終的な表示データのDateは日本時間におけるYYYY/MM/DDの表示にしたいです。

この時、元データをコピペして貼り付けるだけで、最終的な表示データがでるようなものを作りたいのですが、どのようにすればよろしいでしょうか（VBAではなく、関数で完成させたいです）

MID関数と＆などを組み合わせて使い、TimeStampをDateのYYYY/MM/DD形式まではできたのですが、VlookであてたいデータをのDateをキーにして週表示を表示させようとすると、エラーになります（データの中に時間形式が含まれているから？00:00:00）。

恐らく簡単な関数の組み合わせで解決できるかと思いますが、解決できず困っております。ヘルプください。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 ＞・元データ 
 ＞TimeStanmp, ID 
 ＞MM/DD/YYYY 00:00:00, XXXXXXX

 まず確認したいのですが、元データのTimeStampは文字列ですか？
 それとも日付シリアル値をセルの書式でそう見せているのですか？

 ＞元データのTimeStampは日本時間になっているので、 
 ＞アメリカ時間に変更（例＋16時間）

 ２．ここちょっと分からないのですが、日本時間からマイナス16時間じゃないですか？

 ３．言葉だけでなく、具体的な「日付データ」「旨く行かなかったVlookupの数式」を
     アップ頂けないですか？（無理にとは言いませんが）

（半平太） 2020/06/03(水) 16:31

半平太様

ご回答遅くなり申し訳ございませんでした。

＞まず確認したいのですが、元データのTimeStampは文字列ですか？

テキスト形式になっており、それがすべての原因のようです。
この「元データ」は、とあるツールからダウンロードしたデータをエクセルに貼り付けているのですが、
具体的には下記のような形になっており、日付として認識されません。
5/11/2020 19:04:08
セルの書式設定で、例えば、○○月○○日形式に変更しようとしても変更されません。

それが原因で時差の計算（仰る通りマイナス16時間でしたすいません）などもそのままでは、反応しないのだと思います。
そのため、MID関数で無理やりYYYY/MM/DD形式に表現して、マイナス16時間をまでの計算はできるのですが、
そのデータには、日時のデータが隠れているので、「Vlookupであてたいデータ」YYYY/MM/DD形式とVlookであてるとエラーになるのではないかと思います。

（052） 2020/06/04(木) 15:58

 ＞5/11/2020 19:04:08

 それが文字列だとして、
 １．取得したいシリアル値は　2020/5/11 03:04:08 なんですね？
 　　それとも単に2020/5/11のシリアル値が得られればいいんですか？
     ※既にそちらでも対応できているとは思いますが。

 ２．検索先の左端のデータはどうなっていますか？
 　　日付文字列なんですか？
 　　それともシリアル値なんですか？

 　　そっちも、同様に時刻がついているんですね？

 　　＞そのデータには、日時のデータが隠れているので、「Vlookupであてたいデータ」
 　　＞YYYY/MM/DD形式とVlookであてるとエラーになるのではないかと思います。

 　　そうだとして、、時刻までピッタリ同じデータがあるとも思えないの
 　　エラーになるのは当然ですよね。

 　　それを無理やりヒットさせたいんですか？
 　　Yesの場合、何が一致すればいいんですか？　日付部分だけ一致？

 　　※そちらのVlookupの数式が見れないと、推測の部分が多くて考えにくいんですがねぇ。。

（半平太） 2020/06/04(木) 17:02

半平太様

説明が下手で申し訳ないです。

 ＞１．

→取得したいという点では、
この形式で取得するデータを
（A）5/11/2020 19:04:08（テキスト形式）

時差を反映した上で

（B)2020/5/11
のシリアル値（日付のデータ）にできれば解決します。

自分の考えでは、
・（A)を日時データに変換
・時差を計算
・（B)に形に変換（この○時○分は必要なくなります）

（B)の形に変換できれば「Vlookであてたいデータ」にしっかりヒットして、
「最終的な表示データ」になります。

＞２

１で説明した流れを実現する手段として、考えた流れなのですが、
ご指摘の通りうまくいかないので、この手段以外でもし良い方法があればとも思うのですが。。。
（Mid関数も万能ではありませんでした。月や日が二桁の場合、一桁の場合で値がずれてしまうためです）
ちょっと困りました。。。

（052） 2020/06/04(木) 17:37

 ＞この手段以外でもし良い方法があればとも思うのですが。。。

 相変わらず、検索先のデータが実際にどうなっているか分からないです。

 取り敢えず、以下のサンプルだとして、以下の式でトライしてみてください。

 B6セル =(RIGHT(LEFT(A2,FIND(" ",A2)-1),4)&"/"&LEFT(A2,FIND(" ",A2)-6))+RIGHT(A2,8)-"16:00"
 C6セル =VLOOKUP(TEXT(B6,"yyyy/m/d")&"*",TEXT(D2:E100,"yyyy/m/d"),2,FALSE)

 <サンプル>
  行  _________A_________  _________B_________  ____C____  _________D_________  ___ E ___
   1  元データ             ID                              検索先                        
   2  5/11/2020 19:04:08   XXxxxXX                         2020/05/11 12:30:00  X月第4週 
   3                                                                                     
   4                                                                                     
   5  ID                   DATE                 週                                       
   6  XXxxxXX              2020/05/11 03:04:08  X月第4週

（半平太） 2020/06/04(木) 21:24

半平太様

ありがとうございました。
こちらの方法で試してみます。
（052） 2020/06/05(金) 09:55

コメント返信：

[ 一覧(最新更新順) ]

[[20200603145627]] 『エクセルの日付を変換して、最終的にのぞむ形にし』（052） ページの最後に飛ぶ

[[20200603145627]]　『エクセルの日付を変換して、最終的にのぞむ形にし』（052）　ページの最後に飛ぶ