MakeTimestamp logo
MakeTimestamp

Tutorial

Excel のタイムスタンプを処理する方法

はじめに

Excel では、日付が 1900 年 1 月 1 日 (Mac の場合は 1904 年 1 月 1 日) からの日数を表すシリアル番号として保存されます。 Excel がタイムスタンプを処理する方法を理解することは、データ移行、API 統合、および日付ベースの分析の操作にとって不可欠です。このガイドでは、変換手法、計算、ベスト プラクティスについて説明します。

Excel の日付システムを理解する

Excel が日付を保存する方法

Excel では 2 つの日付システムが使用されます。

1900 日付システム (Windows - デフォルト)

  • 1日目 = 1900年1月1日
  • 小数は時刻を表します (例: 0.5 = 正午)
  • バグが含まれています: Excel は 1900 年を閏年として扱います (閏年ではありませんでした)

1904 日付システム (Mac)

  • 1 日目 = 1904 年 1 月 2 日
  • 1900 年うるう年のバグを修正するように設計されました
  • 古い Mac バージョンの Excel でデフォルトで使用されます

重要: 変換を実行する前に、Excel ファイルでどの日付システムが使用されているかを必ず確認してください。同じブック内で日付システムが混在していると、重大なエラーが発生する可能性があります。

シリアル番号の構造

Excel Date Number = Days since epoch + Fraction of day

Example: 44927.5
  = 44927 (days since Jan 1, 1900)
  + 0.5 (half day = 12:00 PM)
  = January 1, 2023, 12:00 PM

基本的な変換

Excel の日付を Unix タイムスタンプに変換

方法 1: Excel の数式

Excel のシリアル日付を Unix タイムスタンプ (1970-01-01 からの秒数) に変換します。

excel
=(A1 - DATE(1970,1,1)) * 86400

Where:
  A1 = Excel serial date (e.g., 44927.5)
  DATE(1970,1,1) = Unix epoch in Excel (25569)
  86400 = seconds per day

プロのヒント: 読みやすくするために名前付き範囲を使用します。

excel
> =EPOCH_DATE * 86400
>
```> EPOCH_DATE は 25569 として定義されています

### 方法 2: Excel VBA 関数

変換用の再利用可能な VBA 関数を作成します。

vba Function ExcelToUnix(excelDate As Double) As Long ' Convert Excel serial date to Unix timestamp Dim epochDate As Date epochDate = #1/1/1970# ExcelToUnix = (excelDate - epochDate) * 86400 End Function

### 方法 3: オンライン コンバーター

[Excel Timestamp Converter](/excel-timestamp) ツールを使用すると、数式を使用せずに即座に変換できます。

## Unix タイムスタンプから Excel 日付へ

### 方法 1: Excel の数式

Unix タイムスタンプを Excel シリアル日付に変換します。

excel =(A1 / 86400) + DATE(1970,1,1)

Where: A1 = Unix timestamp in seconds 86400 = seconds per day DATE(1970,1,1) = Unix epoch in Excel (25569)

### 方法 2: Excel VBA 関数

vba Function UnixToExcel(unixTimestamp As Long) As Date ' Convert Unix timestamp to Excel date Dim epochDate As Date epochDate = #1/1/1970# UnixToExcel = (unixTimestamp / 86400) + epochDate End Function

## 日付の計算

## 日付の算術演算

### 日数の加算と減算

excel ' Add 7 days to a date =A1 + 7

' Subtract 30 days from a date =A1 - 30

' Calculate days between two dates =B1 - A1

### 時間の加算と減算

excel ' Add 4 hours to a datetime =A1 + (4/24)

' Add 30 minutes to a datetime =A1 + (30/1440)

' Add 45 seconds to a datetime =A1 + (45/86400)

' Where: 24 = hours per day 1440 = minutes per day 86400 = seconds per day

> **ベスト プラクティス:** ハードコーディングを回避するには、時間単位のセル参照を使用します。
>

excel

=A1 + (B1/86400)


### 営業日の計算

excel ' Calculate business days between two dates (excludes weekends) =NETWORKDAYS(A1, B1)

' Calculate business days excluding holidays =NETWORKDAYS(A1, B1, C1:C10)

> **国際バージョン:** カスタム週末パターンには NETWORKDAYS.INTL を使用します。
>

excel

=NETWORKDAYS.INTL(A1, B1, 11)



## Excel の日付関数

## 必須の日付関数

### 現在の日付と時刻

excel ' Today's date (no time) =TODAY()

' Current date and time =NOW()

' Current time only =NOW() - TODAY()

' Extract time from datetime =MOD(A1, 1)

### 日付コンポーネントの抽出

excel ' Extract year =YEAR(A1)

' Extract month number (1-12) =MONTH(A1)

' Extract day of month =DAY(A1)

' Extract hour (0-23) =HOUR(A1)

' Extract minute (0-59) =MINUTE(A1)

' Extract second (0-59) =SECOND(A1)

### コンポーネントから日付を作成する

excel ' Create date from year, month, day =DATE(2023, 1, 15)

' Create time from hour, minute, second =TIME(14, 30, 0)

' Combine date and time =DATE(2023, 1, 15) + TIME(14, 30, 0)

### 平日の勤務

excel ' Get day of week (1=Sunday, 7=Saturday) =WEEKDAY(A1)

' Get day of week name =TEXT(A1, "dddd")

' Get weekday name (3-letter abbreviation) =TEXT(A1, "ddd")

' ISO week number =ISOWEEKNUM(A1)

' Week number in year =WEEKNUM(A1)

## 高度な日付操作

## 日付の丸めと切り捨て

### 期間の先頭で切り詰める

excel ' Truncate to start of day (remove time) =INT(A1)

' Round to nearest hour =ROUND(A1*24, 0)/24

' Round to nearest day =ROUND(A1, 0)

' Round down to start of month =EOMONTH(A1, -1) + 1

' Round down to start of year =DATE(YEAR(A1), 1, 1)

### 期末計算

excel ' End of current month =EOMONTH(A1, 0)

' End of next month =EOMONTH(A1, 1)

' End of previous month =EOMONTH(A1, -1)

' End of current year =DATE(YEAR(A1), 12, 31)

## 条件付き日付ロジック

### 日付で IF を使用する

excel ' Check if date is in the past =IF(A1 < TODAY(), "Past", "Future")

' Check if date is within last 30 days =IF(AND(A1 >= TODAY()-30, A1 <= TODAY()), "Recent", "Old")

' Calculate age =IF(MONTH(TODAY()) >= MONTH(A1), YEAR(TODAY()) - YEAR(A1), YEAR(TODAY()) - YEAR(A1) - 1)

### 複雑な日付ロジック

excel ' Calculate fiscal year (starts July 1) =IF(MONTH(A1) >= 7, YEAR(A1) + 1, YEAR(A1))

' Calculate quarter =ROUNDUP(MONTH(A1)/3, 0)

' Add business days (skipping weekends) =WORKDAY(A1, 10)

## 日付の書式設定

## カスタム日付形式

### フォーマットコード
```text
Format Code Examples:

d          Day (1-31)
dd         Day with leading zero (01-31)
ddd        Day abbreviation (Mon)
dddd       Full day name (Monday)

m          Month (1-12)
mm         Month with leading zero (01-12)
mmm        Month abbreviation (Jan)
mmmm       Full month name (January)

yy         2-digit year (23)
yyyy       4-digit year (2023)

h          Hour (0-23)
hh         Hour with leading zero (00-23)
m          Minute (0-59)
mm         Minute with leading zero (00-59)
s          Second (0-59)
ss         Second with leading zero (00-59)

AM/PM      AM/PM indicator

フォーマットの適用

excel
' Apply date format via formula
=TEXT(A1, "yyyy-mm-dd")

' Apply datetime format with seconds
=TEXT(A1, "yyyy-mm-dd hh:mm:ss")

' Custom format: "January 15, 2023 at 2:30 PM"
=TEXT(A1, "mmmm dd, yyyy at h:mm AM/PM")

' ISO 8601 format
=TEXT(A1, "yyyy-mm-ddThh:mm:ss")

重要: TEXT() は文字列値を返します。最初に数値演算を使用し、最後にフォーマットを使用します。

よくある落とし穴

日付システムの競合

1900 年と 1904 年の日付システム

問題: 日付を混在させると重大な計算エラーが発生します。

excel
' Check if workbook uses 1904 date system
' Excel Options > Advanced > When calculating this workbook > Use 1904 date system

解決策: 日付を操作する前に、必ず [ツール] > [オプション] > [詳細設定] を使用して日付システムの設定を確認してください。

うるう年のバグ (1900 年)

Excel は、1900 年 2 月 29 日 (存在しませんでした) を含め、1900 年をうるう年として誤って扱います。

excel
' March 1, 1900 in Excel
=DATE(1900, 3, 1)  ' Returns 61 (correct)

' February 28, 1900 in Excel
=DATE(1900, 2, 28)  ' Returns 59 (correct)

' February 29, 1900 (DOESN'T EXIST)
=DATE(1900, 2, 29)  ' Returns 60 (incorrect - this day never happened)

回避策: 1904 日付システムではこのバグは修正されていますが、Windows Excel ファイルとの互換性の問題が発生します。

タイムゾーンの問題

Excel では、タイムゾーン情報なしで日付が保存されます。

excel
' Current time in Excel
=NOW()  ' Returns local system time

ベスト プラクティス: UTC タイムスタンプを別の列に保存し、変換用のタイムゾーン メタデータを維持します。

テキスト形式と日付形式

問題: テキストとして保存された日付は計算に使用できません。

excel
' Check if cell contains date or text
=ISNUMBER(A1)  ' TRUE for dates, FALSE for text

' Convert text to date
=DATEVALUE("2023-01-15")
=TIMEVALUE("14:30:00")

' Parse combined date and time text
=DATEVALUE("2023-01-15") + TIMEVALUE("14:30:00")

パフォーマンスの最適化

計算パフォーマンス

揮発性関数を避ける

問題: NOW() や TODAY() などの関数は変更のたびに再計算を行うため、大きなワークブックの速度が低下します。

解決策: volatile 関数の結果を静的セルに保存します。

excel
> ' BAD: Every row recalculates
> =IF(A1 > NOW(), "Future", "Past")
>
> ' GOOD: Calculate once in cell B1
> B1: =NOW()
> A2: =IF(A1 > $B$1, "Future", "Past")
>
```>
> ### 名前付き範囲を使用する
>
>

excel

' Define named ranges (Formulas > Name Manager) EPOCH_DATE = 25569 SECONDS_PER_DAY = 86400

' Use in formulas =(A1 - EPOCH_DATE) * SECONDS_PER_DAY

> **利点:** 名前付き範囲により可読性が向上し、数式の保守が容易になります。

### バッチ操作の配列式

excel ' Convert multiple Unix timestamps at once (Excel 365) =A2:A100/86400 + DATE(1970,1,1)

' Calculate ages for a range =YEAR(TODAY()) - YEAR(A2:A100)

## 統合の例

## CSV へのエクスポート

Excel の日付を CSV にエクスポートする場合:

excel ' Format dates as ISO 8601 before exporting =TEXT(A1, "yyyy-mm-ddThh:mm:ss")

> **ベスト プラクティス:** 日付形式を保持するには、UTF-8 エンコードを使用して CSV として保存します。

## API からインポートする

API からの JSON 日付を解析します。

excel ' Assuming cell A1 contains: 1673761800 (Unix timestamp) ' Convert to Excel date =(A1/86400) + DATE(1970,1,1)

' Format as readable date =TEXT((A1/86400) + DATE(1970,1,1), "yyyy-mm-dd hh:mm:ss")

## データベースの統合

SQL データベースの操作:

excel ' Prepare date for SQL INSERT =TEXT(A1, "yyyy-mm-dd hh:mm:ss") ' Returns: "2023-01-15 14:30:00"

' Convert SQL DATETIME to Excel =DATEVALUE(LEFT(A1, 10)) + TIMEVALUE(MID(A1, 12, 8))

## ベスト プラクティスの概要

## 日付処理チェックリスト
```text
✅ Always verify date system (1900 vs 1904) before calculations
✅ Use named ranges for constants (EPOCH_DATE, SECONDS_PER_DAY)
✅ Store volatile function results (NOW(), TODAY()) in static cells
✅ Use TEXT() only for final display formatting
✅ Validate date formats before calculations (ISNUMBER, DATEVALUE)
✅ Use NETWORKDAYS for business day calculations
✅ Apply consistent date formatting across workbooks
✅ Document date assumptions (timezones, epoch references)
✅ Test edge cases (leap years, month boundaries)
❌ Don't mix text and date formats in calculations
❌ Don't use volatile functions in large datasets
❌ Don't assume dates are in UTC without documentation
❌ Don't hardcode timezone offsets in formulas
❌ Don't ignore the 1900 leap year bug for historical dates

一般的な問題のトラブルシューティング

問題: 日付が数字で表示される

excel
' Solution 1: Format as date
' Select cells > Right-click > Format Cells > Date

' Solution 2: Use TEXT() function
=TEXT(A1, "yyyy-mm-dd")

' Solution 3: Check for text dates
' If ISNUMBER(A1) returns FALSE, use:
=DATEVALUE(A1)

問題: 日付の計算が正しくない

excel
' Check 1: Verify date system
' Excel Options > Advanced > Use 1904 date system

' Check 2: Ensure consistent units
' Days: =A1 + 7
' Hours: =A1 + (4/24)
' Minutes: =A1 + (30/1440)
' Seconds: =A1 + (45/86400)

' Check 3: Verify date components
' Ensure DATE() has correct arguments: YEAR, MONTH, DAY

問題: タイムゾーン変換エラー

excel
' Solution: Maintain separate timezone column
A1: Excel timestamp
B1: Timezone offset (e.g., -5 for EST)
C1: =A1 + (B1/24)

関連ツール

追加リソース

大規模な日付処理 (10,000 行を超える) の場合は、パフォーマンスを向上させるために Power Query または VBA マクロの使用を検討してください。 Excel 関数は対話型での使用に最適化されており、大規模なデータセットでは速度が低下する可能性があります。