- ベストアンサー
夏・冬時間による日本時間の表示
- Excel2010で、夏時間と冬時間による日本時間を表示する方法について教えてください。
- 「損益計算」シートの開始日時と終了日時を日本時間で表示したいのですが、計算式が分かりません。夏時間と冬時間を考慮して表示する方法を教えてください。
- 具体的な手順として、夏時間のGMTと開始日時、終了日時を「設定」シートに入力し、「損益計算」シートの開始日時と終了日時を日本時間で表示するための計算式を教えてください。
- miya_HN
- お礼率100% (1683/1683)
- Excel（エクセル）
- 回答数37
- ありがとう数37
- みんなの回答 （37）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (36)
- bunjii
- ベストアンサー率43% (3589/8249)
>（夏冬時間はアメリカ（EST/東部時間）時間に対応している。） EST（アメリカ東部時）はGMT-5であり、EDT（アメリカ東部時の夏時間）はGMT-4です。 従って、根本的に元データのタイムゾーン（サーバーの設置場所）を再確認の必要があります。 >夏時間：GMT＋3 >冬時間：GMT＋2 夏時間は冬時間（標準時）-1時間なので上記は有り得ません。 但し、南半球の場合は北半球と夏冬が逆転しますので、北半球の季節で夏冬を定義すると誤解を招きます。 GMT+3時間は東アフリカの経度が該当します。 GMT+2時間は中央アフリカの経度が該当します。 提示の情報が支離滅裂なので正しい時差の計算方法を提示できません。 尚、補足の文言が長文で読み難いこともあり、必要な条件を読み取れません。
お礼
回答者の皆様方ありがとうございます。 長い間、付き合っていただいたことに対しましてお詫び申し上げます。 また、ここまで回答をくださった方々に非常に感謝しております。 ありがとうございました。
補足
回答ありがとうございます。 何度も申し訳ありません。 「（夏冬時間はアメリカ（EST/東部時間）時間に対応している。）」という部分は、あくまで夏時間を採用している方式とでも言いましょうか、サーバー時間とは関係ないです。 サーバー時間は、 夏時間：GMT＋3 冬時間：GMT＋2 で間違いないです。業者の方に直接問い合わせて聞いたのですから。 そのような解釈をなさるんですね。 下記URLの上部にある画像の「MT4時間」や「日本とMT4：」をご覧になってください。 （ちなみに、業者はFXDDというところです。） http://www.fxddjpblog.com/?s=GMT&x=0&y=0 もし、お時間に余裕がおありでしたら業者について検索してみてください。 必要な条件（または、GMTなどの値の入力・変更によって「損益計算」シートのZ行（日本時間の開始日時）、AA行（日本時間の終了日時）の表示結果が異なるように計算式を作る。）についてですが、説明下手なのは自覚しておりますが、このサイトで何度も質問させていただいておりますがここまでご理解いただけないのは初めてです。 時間を割いていただき、何度も質問に答えて頂いて大変感謝しているとともに申し訳なく思いますが、逆にお聞きしますが、どう説明すればご理解いただけるのでしょうか？ 動画でも載せた方が分かりやすいのでしょうか？（その場合、時間がかかってしまいますが。） また回答を頂くことになり、お手数ですが、回答よろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>結果から言いますと表示結果が正しくないです。 B3は2014.02.25 09:20:48であり冬時間です。 従って、GMTとの時間差は+3とされていますのでZ3は2014年2月25日 15:20:48（GMT+6:00:00）が正しいはずです。 因みに、設定シートは誰が作成したものですか？ 設定シートのD列に夏時間についての情報はありますが、冬時間が提示されていません。 貼付画像は回答No.3への補足に記載されたURLの一部ですがGMTとの時間差（赤枠）が夏時間：+2、冬時間：+3となっていますので、その情報を使っています。 尚、夏時間の開始と終了は設定シートで提示された日時を使っています。
お礼
大変お待たせいたしました。 業者の方に確認しました。 業者の方は、 口座履歴の時刻は、Excelで計算した場合、夏時間であるGMT±の値のみを計算に入れ、冬時間の期間に入ったとき冬時間を計算に入れなかった場合、正しい日本時間の表示結果が表示されないとのことでした。 また特に必要のない情報ですがその業者では、下記の通りでした。 夏時間：GMT＋3 冬時間：GMT＋2 夏時間の期間：3月第2日曜日2:00～11月第1日曜日2:00 冬時間の期間：11月第1日曜日2:00～翌年3月第2日曜日2:00 （夏冬時間はアメリカ（EST/東部時間）時間に対応している。） 営業時間・夏時間（日本時間で）：月曜5:00～土曜5:45 営業時間・冬時間（日本時間で）：月曜6:00～土曜6:45 ※時刻は全て午前です。 また、 夏時間と冬時間が逆の業者というのは存在しているのか？という問いに対しては、業者ごとに違いがある可能性もあるとの回答でした。 （例えば、A社の夏時間がGMT+3、冬時間がGMT+2だとして、B社の夏時間がGMT+2、冬時間がGMT+3という場合があるのか？という意味です。） 以上です。 この情報で大丈夫でしょうか？ これは重要なことですが、 自分はこの業者の場合のみではなく、 他社の場合にも対応した 「損益計算」シートのZ3（日本時間としての開始日時）、 AA3（日本時間としての終了日時） を表示したいと思っています。 つまり、 1.夏時間・冬時間のGMTの値 2.夏時間の開始日時・終了日時 3.冬時間の開始日時・終了日時 を変更することによって 「損益計算」シートのZ3（日本時間としての開始日時）、 AA3（日本時間としての終了日時）も変更できるようにしたいのです。 この点を忘れないでください。 大変お待たせいたしまして申し訳ありません。 お手数ですが、回答の方をよろしくお願い致します。
補足
補足から失礼します。 回答ありがとうございます。 できれば、業者からの正しい情報を得た上で回答をいただきたかったのですが、回答を頂けたことには大変感謝しております。 >B3は2014.02.25 09:20:48であり冬時間です。 従って、GMTとの時間差は+3とされていますのでZ3は2014年2月25日 15:20:48（GMT+6:00:00）が正しいはずです。 大変申し訳ありません。 これと類似したファイルがあり、現在の日時と混同してしまい本質問のデータと勘違いをしてしまいました。 お気を悪くされたなら大変申し訳ありませんでした。 ただ、「設定」シートのD3（夏時間としてのサーバー時間（GMT±？））を変更しても「損益計算」シートのZ3（日本時間としての開始日時）、AA3（日本時間としての終了日時）は変わりませんでした。 この部分に関しては改善していただけないと困ります。 この部分を見落としていないでしょうか？ >因みに、設定シートは誰が作成したものですか？ >設定シートのD列に夏時間についての情報はありますが、冬時間が提示されていません。 私です。 夏時間のGMT±？さえ入力できれば、「設定」シートのE3（夏時間の開始日時）、F3（夏時間の終了日時）を「損益計算」シートのZ3（日本時間としての開始日時）、AA3（日本時間としての終了日時）の計算式に入れれば正しい表示結果が出るだろうと思ったからです。 「設定」シートのE3（夏時間の開始日時）から、F3（夏時間の終了日時）の期間以外は冬時間として認識できるだろうという考えからです。 この考え方は間違いでしょうか？ >貼付画像は回答No.3への補足に記載されたURLの一部ですがGMTとの時間差（赤枠）が夏時間：+2、冬時間：+3となっていますので、その情報を使っています。 尚、夏時間の開始と終了は設定シートで提示された日時を使っています。 はい、ありがとうございます。 ただ、あくまで「回答No.3への補足に記載されたURL」と、「設定」シートのE3（夏時間の開始日時）とF3（夏時間の終了日時）に入力された値は例に過ぎないことをご理解ください。 前述の通り、業者の方へ確認してみますのでその後改めて回答を頂きたいと思います。 大変お手数をおかけしますがよろしくお願いします。 それと、念を押して言いたいのですが「設定」シートのD3（夏時間としてのサーバー時間（GMT±？））を変更することにより「損益計算」シートのZ3（日本時間としての開始日時）、AA3（日本時間としての終了日時）の方も変更される計算式を考えてください。 それとも、回答者様の下記のご指摘の、 >設定シートのD列に夏時間についての情報はありますが、冬時間が提示されていません。 で冬時間のGMT±？がないと表示結果に問題があるのでしょうか？ そこまでExcelに知識がないので自分には分からないのですが。 業者に確認し、その後再び質問致しますのでどうかよろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>B3：2014.02.25 09:20:48（サーバー時間による開始日時。） B3セルを元に日本の時刻に修正するには次の数式で良いと思います。 Z3=IF(B3="","",SUBSTITUTE(B3,".","/")+(9-3)/24+((SUBSTITUTE(B3,".","/")+0)>DATE(LEFT(B3,4),3,15-WEEKDAY(DATE(LEFT(B3,4),3,14)))+"2:00:00")*((SUBSTITUTE(B3,".","/")+0)<DATE(LEFT(B3,4),11,8-WEEKDAY(DATE(LEFT(B3,4),11,7)))+"2:00:00")/24) 現地日時は文字列でしょうから次の数式でシリアル値に変換できます。 =SUBSTITUTE(B3,".","/")+0 → 41695.38944 → 2014/02/25 09:20:48 現地日時と日本時間の変換はGMTとの時間差から次の数式で導けます。 =(9-3)/24 → 0.25 → 06:00:00 夏時間と冬時間の補正は元データの発生地域（サーバー設置の位置）が1ヶ所とすれば夏時間の開始と終了は1組となり固定できますので次の数式で良いでしょう。 =((SUBSTITUTE(B3,".","/")+0)>DATE(LEFT(B3,4),3,15-WEEKDAY(DATE(LEFT(B3,4),3,14)))+"2:00:00")*(SUBSTITUTE(B3,".","/")+0)<DATE(LEFT(B3,4),11,8-WEEKDAY(DATE(LEFT(B3,4),11,7)))+"2:00:00")/24) 取引可能日時は現地日時の月曜日の0:00:00から土曜日の0:00:00までなので夏時間の開始および終了日時はデータが発生しないと判断しています。 AA3セルはI3セルを元にZ3と同様の数式を組めば良いでしょう。（Z3の数式のB3をI3に書き換える）
お礼
回答者の皆様方ありがとうございます。 長い間、付き合っていただいたことに対しましてお詫び申し上げます。 また、ここまで回答をくださった方々に非常に感謝しております。 ありがとうございました。
補足
回答ありがとうございます。 結果から言いますと表示結果が正しくないです。 Z3：2014年2月25日 15:20:48 と表示されたのですが正しくは、 2014年2月25日 16:20:48（「設定」シートのD3が2の場合） です。 ちなみに、計算式の「B3」を「元データ!B3」にし直しました。 これは重要なことなんですが、最初に質問した文章の中で、 「そして、「損益計算」シートのA3～H3（から下）より始まる開始日時と終了日時、「設定」シートのD3（サーバー時間の夏時間のGMT）、E3（夏時間の開始日時：文字入力）、F3（夏時間の終了日時：文字入力）から、「損益計算」シートのZ3から下に日本時間の開始日時、AA3から下に日本時間の終了日時を表示したいのですが計算式が分かりません。」 とありますので「設定」シートのD3の数値、及び、E3・F3の文字列によって「損益計算」シートのZ3、AA3の日本時間の開始日時・終了日時を変更できるようにしなければなりません。 「設定」シートのD3は必ず数値の変更によって日本時間の表示結果が変えられるようにしなければなりません。 「設定」シートのE3・F3の文字列に関してはこれらのデータが必要かどうか業者に確認する必要があります。 口座履歴のデータが夏時間、冬時間に関わらず自動的に修正し（トレードするソフト、チャートソフトと呼びます。）夏時間のままでも冬時間のとき正しい表示結果が出るかどうか確認しなければならないからです。 したがって、業者に問い合わせ、確認後、お礼のコメントにて改めて質問しますのでお手数ですがそれまで待ってもらって回答の方をよろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>サーバーは海外です。 海外のサーバーの時刻を日本時間に換算することは分かっています。 質問文が長く、要点が要領を得ないので貼付画像からの憶測で回答しました。 設定シートのD1に「サーバー時間（GMT）」となっていますのでサーバーの時刻がGMTに設定されているものと誤解しました。 また、損益計算シートの日時（Z3、AA3）は日本時間に変換した結果かと思われますが、それに対する元データの値が示されていませんので確認のために提示頂きたいと思います。 尚、夏時間の開始と終了については国（地域）によって条件が異なると思いますので複数の国について提示頂ければ幸いです。 夏時間は対象地域の現地時刻を1時間早めることであると思います。 設定シートのD3に+2としてある値についての解釈が気になります。 つまり、現地時刻はGMTに対して+2時間で、夏時間はそれを1時間早めて+3時間なのか、それとも夏時間はGMTに対して+2時間で冬時間はGMTに対して+1時間なのかを明確にしてください。
お礼
回答ありがとうございます。 非常に早い対応、大変感謝しております。 >質問文が長く、要点が要領を得ないので貼付画像からの憶測で回答しました。 申し訳ありません。 疑問に思ったことはあまり深く考えず、説明下手で考え込むよりも行動の方が先に出てしまうタイプなのですが、そういう性分なので相手に伝わりづらいかもしれませんが、ご了承ください。 >設定シートのD1に「サーバー時間（GMT）」となっていますのでサーバーの時刻がGMTに設定されているものと誤解しました。 また、損益計算シートの日時（Z3、AA3）は日本時間に変換した結果かと思われますが、それに対する元データの値が示されていませんので確認のために提示頂きたいと思います。 尚、夏時間の開始と終了については国（地域）によって条件が異なると思いますので複数の国について提示頂ければ幸いです。 夏時間は対象地域の現地時刻を1時間早めることであると思います。 設定シートのD3に+2としてある値についての解釈が気になります。 つまり、現地時刻はGMTに対して+2時間で、夏時間はそれを1時間早めて+3時間なのか、それとも夏時間はGMTに対して+2時間で冬時間はGMTに対して+1時間なのかを明確にしてください。 はい。 結果から申し上げますと「損益計算」シートの、 Z3（から下は「開始日時」）、 AA3（から下は「終了日時」） を日本時間に変換し表示したいです。 最初に、大元の「元データ」シートのデータですが、外国為替証拠金取引（FX）の口座履歴のデータ（つまり、確定されたデータです。）です。 「元データ」シートのデータには、A～N列の3行目から下に入っています。下記の通りです。 A3：23172740（チケット番号。） B3：2014.02.25 09:20:48（サーバー時間による開始日時。） C3：sell（売買タイプ。「sell」とは売りのことです。） D3：0.2（ロットサイズ。） E3：audusd（通貨ペア。） F3：0.90229（開始価格。） G3：0.90429（「S/L」。ストップ・ロス・ポイント。） H3：0.90084（「T/P」。エグジットポイント。） I3：2014.02.25 10:09:12（サーバー時間による終了日時。） J3：0.90084（決済価格。） K3：0（手数料。） L3：0（税金。） M3：0（スワップ。） N3：2 966（損益。） ここで必要な計算は、B行と、I行のみです。 夏時間の開始と終了については、FX業者によって異なってきます。 「設定」シートのD3（夏時間としてのサーバー時間（GMT±？））は、変更できる形でないといけません。 GMTに関してですが、例として下記URLを参照ください。 中央よりやや下の「取引情報」の「GMT時差」の部分です。 http://www.fxplus.com/broker/axo/ 「設定」シートのD3には、「GMT時差」の夏時間である数値の「+2」をそのまま入力するだけです。 また、回答No.1の方の下記の質問に対する回答ですが、 >例えば「A3～C3に入力されているサーバー時間」と「E3～G3に入力されているサーバー時間」のどちらか片方(或いは両方)が、「F3に入力されている日時(夏時間の実施期間が終了する日時)の1時間前」～「F3に入力されている日時(夏時間の実施期間が終了する日時)」の時間帯と同じ表示となる日時であった場合、A3～C3やE3～G3に入力されているサーバー時間が「未だ夏時間中に記録された日時」なのか「夏時間期間終了後の(GMTに合わせるために)1時間戻した日時」なのか区別する事が出来ないと思われます。 そのような「『夏時間の実施期間が終了する日時』として設定されている日時の一時間以内前の日時」が入力されていた場合には、「A3～C3に入力されているサーバー時間」と「E3～G3に入力されているサーバー時間」のどちらに「『設定』シートのD3に入力されている時差」を適用すれば良いのか判りません。 「『夏時間の実施期間が終了する日時』として設定されている日時の一時間以内前の日時」に関しては、基本FXは、土日は休みのため考慮に入れなくて結構です。 以上となりますが、これでご理解いただけたでしょうか？ まだ不明な点があればご指摘ください。 説明不足のところや不明な点があり、ご迷惑をおかけしましたがご理解いただけたら、回答の方をよろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
GMT（Greenwich Mean Time）は基準になる時刻なので夏時間は無いでしょう。 従って、サーバーの時刻がGMTで管理されていれば日本の標準時（東京）はGMT+9:00:00で一定です。（日本は夏時間制を採用していない）
お礼
回答者の皆様方ありがとうございます。 長い間、付き合っていただいたことに対しましてお詫び申し上げます。 また、ここまで回答をくださった方々に非常に感謝しております。 ありがとうございました。
補足
回答ありがとうございます。 サーバーは海外です。 回答NO.1の方と同様、お時間をいただけないでしょうか。 よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
御質問文の説明内容に矛盾や説明不足があるため、どの様な状況になっているのか不明な点が幾つかあります。 >「設定」シートのD3にサーバー時間の夏時間のGMTを入力し との事ですが、もしその御説明の通りに「『設定』シートのD3セルに『サーバー時間』に対応するGMT」が常に入力されているのであれば、サーバー時間など使わずに =IF(元データ!A3="","",D3+"9:0") で日本時間を求める事が出来る筈です。 又、もしも >「設定」シートのD3にサーバー時間の夏時間のGMTを入力し という部分の意味が、「『A3～C3に入力されているサーバー時間』に対応するGMT」という意味ではなく、「『現在のサーバー時間』に対応するGMT」という意味であるとした場合には、「『A3～C3に入力されているサーバー時間』と『そのサーバー時間に対応するGMT』との間の時差」と「『現在のサーバー時間』と『現在のGMT』との間の時差」は当然異なる場合(A3～C3に入力されているサーバー時間が夏時間を実施期間中の時間で、現在は夏時間の実施期間中ではない場合や、その逆パターンの場合)があり得るのですから、「設定」シートのD3にGMTを入力していた所で役には立たない事になります。 又もしも、 >「設定」シートのD3にサーバー時間の夏時間のGMTを入力し という御説明内容は間違いで、「設定」シートのD3に入力されているのが「『サーバー時間』に対応するGMT」などではなく、「『サーバー時間』と『サーバー時間に対応するGMT』との間の時差」だとした場合でも問題があります。 例えば「A3～C3に入力されているサーバー時間」と「E3～G3に入力されているサーバー時間」のどちらか片方(或いは両方)が、「F3に入力されている日時(夏時間の実施期間が終了する日時)の1時間前」～「F3に入力されている日時(夏時間の実施期間が終了する日時)」の時間帯と同じ表示となる日時であった場合、A3～C3やE3～G3に入力されているサーバー時間が「未だ夏時間中に記録された日時」なのか「夏時間期間終了後の(GMTに合わせるために)1時間戻した日時」なのか区別する事が出来ないと思われます。 そのような「『夏時間の実施期間が終了する日時』として設定されている日時の一時間以内前の日時」が入力されていた場合には、「A3～C3に入力されているサーバー時間」と「E3～G3に入力されているサーバー時間」のどちらに「『設定』シートのD3に入力されている時差」を適用すれば良いのか判りません。 この様に幾つか不明な点がありますので、「『設定』シートのD3に入力されている値」は一体どの様な値であるのかという事や、「『夏時間の実施期間が終了する日時』として設定されている日時の一時間以内前の日時」が入力されていた場合にはどうすれば良いのかという事を御説明願います。
お礼
回答者の皆様方ありがとうございます。 長い間、付き合っていただいたことに対しましてお詫び申し上げます。 また、ここまで回答をくださった方々に非常に感謝しております。 ありがとうございました。
補足
補足コメントから失礼します。 回答ありがとうございます。 そのようなことはあまり考えず質問してしまいました。 回答者様の疑問点はサーバー元の方に問い合わせないと分からないため少しばかりというか、時間がかかります。 それまでお待ち願えるでしょうか？ 分かり次第、お礼のコメントにて説明させていただきます。 ありがとうございました。
お礼
回答ありがとうございます。 申し訳ありません。 回答者様のおっしゃる通り、「損益計算」シートで再度調べたところ22日でした。 焦って確認したせいか抜けていた部分がありました。 日本時間では、2011年7月23日 0:31:00（開始日時）～2011年7月23日 1:06:00（終了日時）となっており、土曜日に行われていました。 土・日・元日は、取り引きが行われていないというのが一般的に言われていることなんですが、土曜日に限っては厳密に金曜日の23:59:59に終了というわけではなく、土曜日の早朝まで取り引きが行われていまして、本来ならそこまで計算に入れなければならなかったんです。 ですが、ここではもうそれができないため、新規の質問を検討したいと思います。 正しいご指摘、ありがとうございます。 また、この件に関しまして質問する際には是非とも回答をいただけたら大変ありがたいです。 ありがとうございました。