はじめに
こんにちは、ティーマネジメントのゆったです。
先月、猛暑のせいで虫が少ないといったニュースを見かけましたが、
少し涼しさが出てきたここ最近、確かに夏よりも虫を多く見る気がします。
過ごしやすくなるのは嬉しいのですが、夏にあまり出番の無かった殺虫剤のストックはしっかり確認しておいた方がよさそうです❀
ーーー
今回ご紹介するのは、Excelの日付がFileMakerへうまくインポートできない場合の対処法です。
FileMakerへExcelファイルからデータをインポートした際に日付が「45292」といった数字の羅列になってしまったことはありませんか？
今回はExcelからどんな書式で日付を読み込んだとしても、正しく日付としてインポートすることができるテクニックをお教えします！
Excelではなぜ日付が数字になってしまうのか
Excelでは日付や時刻をシリアル値として管理しています。
そのため、日付が入力されたセルの書式を、数値や文字列に変えると数値になります。
この数値は、1900/1/1を「1」として何日経過したかという「日数」を表しているのです。
なので、Excelだと、「1900/1/0」にこの数値を足すと、元の日付になります。 ※「1900/1/0」を日付として計算させるためにDATE関数を使用しています
FileMakerでシリアル値を日付に戻すには
FileMakerでこのシリアル値を日付に戻すために、Excelの計算式を参考にして、
1900/1/0にこの数値を足せば解決！…と思いきや、実際に計算してみると何故か1日ずれてしまいます。
※ExcelのDATE関数とFileMakerのDate関数はよく似ていますが、年月日の順番が違うので要注意
・Excel： DATE ( 年 , 月 , 日 )
・FileMaker： Date ( 日 , 月 , 年 )
実は、Excelが1900年をうるう年であると誤って設定されており、
「1900/2/29」という架空の日が存在するせいで日付の計算が1日ずれているのです！
※詳細はMicrosoftより提供されている下記サイトをご確認ください
Excel では、1900 年が閏年であると誤って想定しています
つまり、FileMakerでExcelのシリアル値から日付に変換する計算式はこうなります。
「 Date ( 1 ; -1 ; 1900 ) + シリアル値 」
つまり、インポートして全置換を行えば…
※数字だけを抜き出す為にFilter関数を使用しています
綺麗に日付としてインポートすることができました！
因みに、正しく西暦4桁で入力された日付と上記のようなシリアル値になってしまう日付などが混ざっている場合は、
データが日付形式かを判定するGetAsDate関数や、文字数をカウントするLength関数で、
5桁の数字であることを判定してから変換するとより処理が安定します！
さいごに
Excelは使用されることが多いと思いますが、思っても見ないところに独自の"癖"があることも…
特に他のソフトにデータを入れる場合には、今回のような日付や、数値で思ったようにデータが入らない事もままあるかと思います。
そんな場合には、FileMakerでデータを変換してデータとソフトと繋ぐ役割を担うこともできますので、
何かお困りごとがありましたら、ぜひお気軽にお問い合わせください❁❁❁
