如何用Excel/Google Sheet函数将波斯（Shamsi）日期转公历（Miladi）日期？

Excel 实现方式 #

Excel无内置波斯历转公历函数，可通过自定义VBA函数实现：

1. 按 Alt + F11 打开VBA编辑器
2. 右键工作簿 → 插入 → 模块
3. 粘贴以下代码到模块：

Function ShamsiToGregorian(shamsiYear As Integer, shamsiMonth As Integer, shamsiDay As Integer) As Date
    Dim gy As Integer, gm As Integer, gd As Integer
    Dim jy As Integer, jm As Integer, jd As Integer
    Dim g_days_in_month As Variant, j_days_in_month As Variant
    
    g_days_in_month = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
    j_days_in_month = Array(31, 31, 31, 31, 31, 31, 30, 30, 30, 30, 30, 29)
    
    jy = shamsiYear
    jm = shamsiMonth
    jd = shamsiDay
    
    If jy > 979 Then
        gy = jy + 621
        If jm < 7 Then
            gm = jm + 6
            gd = jd
        Else
            gm = jm - 6
            gd = jd
            gy = gy + 1
        End If
    Else
        gy = jy + 621
        If jm < 7 Then
            gm = jm + 6
            gd = jd
            If (gy Mod 4 = 0 And gy Mod 100 <> 0) Or (gy Mod 400 = 0) Then
                If gd > 29 And gm = 2 Then gd = gd - 1
            Else
                If gd > 28 And gm = 2 Then gd = gd - 1
            End If
        Else
            gm = jm - 6
            gd = jd
            gy = gy + 1
        End If
    End If
    
    While gd > g_days_in_month(gm - 1)
        If gm = 2 And (gy Mod 4 = 0 And gy Mod 100 <> 0) Or (gy Mod 400 = 0) Then
            If gd > 29 Then
                gd = gd - 29
                gm = gm + 1
            End If
        Else
            gd = gd - g_days_in_month(gm - 1)
            gm = gm + 1
        End If
    Wend
    
    ShamsiToGregorian = DateSerial(gy, gm, gd)
End Function

4. 保存工作簿为.xlsm格式（启用宏）
5. 使用示例：
   • 直接传入参数：=ShamsiToGregorian(1401,6,6)，返回2022/08/28
   • 拆分文本格式日期：若A1为1401/06/06，用=ShamsiToGregorian(INDEX(TEXTSPLIT(A1,"/"),1),INDEX(TEXTSPLIT(A1,"/"),2),INDEX(TEXTSPLIT(A1,"/"),3))

Google Sheet 实现方式 #

方式1：自定义Apps Script #

1. 打开Sheet → 扩展程序 → Apps Script
2. 删除默认代码，粘贴以下脚本：

function SHAMSITOGREGORIAN(shamsiYear, shamsiMonth, shamsiDay) {
    const gDaysInMonth = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
    const jDaysInMonth = [31, 31, 31, 31, 31, 31, 30, 30, 30, 30, 30, 29];
    
    let jy = shamsiYear;
    let jm = shamsiMonth;
    let jd = shamsiDay;
    let gy, gm, gd;
    
    if (jy > 979) {
        gy = jy + 621;
        if (jm < 7) {
            gm = jm + 6;
            gd = jd;
        } else {
            gm = jm - 6;
            gd = jd;
            gy += 1;
        }
    } else {
        gy = jy + 621;
        if (jm < 7) {
            gm = jm + 6;
            gd = jd;
            if ((gy % 4 === 0 && gy % 100 !== 0) || gy % 400 === 0) {
                if (gd > 29 && gm === 2) gd -= 1;
            } else {
                if (gd > 28 && gm === 2) gd -= 1;
            }
        } else {
            gm = jm - 6;
            gd = jd;
            gy += 1;
        }
    }
    
    while (gd > gDaysInMonth[gm - 1]) {
        if (gm === 2 && ((gy % 4 === 0 && gy % 100 !== 0) || gy % 400 === 0)) {
            if (gd > 29) {
                gd -= 29;
                gm += 1;
            }
        } else {
            gd -= gDaysInMonth[gm - 1];
            gm += 1;
        }
    }
    
    return new Date(gy, gm - 1, gd);
}

3. 保存脚本（命名为ShamsiConverter），返回Sheet使用：
   • 直接传参：=SHAMSITOGREGORIAN(1401,6,6)
   • 拆分文本日期：A1为1401/06/06时，用=SHAMSITOGREGORIAN(INDEX(SPLIT(A1,"/"),1),INDEX(SPLIT(A1,"/"),2),INDEX(SPLIT(A1,"/"),3))

方式2：内置函数组合（简化版） #

若Sheet区域设置支持波斯历，可直接用公式：

=DATEVALUE(SUBSTITUTE(A1,"/","-")&" [Persian]")

设置单元格格式为公历日期即可显示转换结果。

内容的提问来源于stack exchange，提问作者Mahdi Omrani