ปัญหาการแปลงวันที่ พศ เป็น คศ โดยลบด้วย 198327

[ralph_vii]

การแปลงวันที่ พศ เป็น คศ โดยลบด้วย 198327 ข้อมูลวันที่ที่ได้จะถูกต้อง จนถึงเดือนสองปี 51 แต่กรณีนำเอาวันที่หลังจากเดือนสองเช่น เดือน3/3/2551 มาแปลงข้อมูลจะผิดพลาดไปหนึ่งวัน มีวิธีแก้ไขหรือไม่ครับ

วันที่ก่อนลบ วันที่หลังลบด้วย 198327
1/1/2551 1/1/2008
2/2/2551 2/2/2008
3/3/2551 2/3/2008

[สมเกียรติ]

ไม่แนะนำให้ใช้วิธีนำตัวเลขนี้มาลบครับ เพราะจะใช้ได้กับวันที่ในบางปี และบางช่วงเท่านั้น ถ้าใช้ในปีที่มี 29 วันในเดือนกุมภาพันธ์ จะได้วันต่างจากเดิม

ไม่ควรเสี่ยงใช้วิธีนี้ เพราะคนอื่นที่เอาแต่ท่องจำ อาจนำไปใช้ผิดๆ ถ้าจะใช้จริง จะต้องนำค่าในแต่ละเซลล์ที่เป็นปีพ.ศ.มาตรวจสอบหาอธิกสุรทินโดยลบปีพ.ศ.ด้วย 543 แล้วหารด้วย 4 (หรือหารด้วย 100 แล้วหารด้วย 400 ในกรณีทุกพันปี) ซึ่งถ้าบันทึกค่าไว้หลายเซลล์และวันที่กระจายไม่เรียงต่อกัน จะยิ่งวุ่นครับ เพราะเมื่อย้อนจากปีพ.ศ.ที่พิมพ์ลงไปให้เป็นค.ศ.นั้น ปีพ.ศ.ที่พิมพ์อาจเป็นอธิกสุรทิน แต่ค.ศ.ไม่ใช่อธิกสุรทิน หรือตรงกันข้ามคือปีพ.ศ.ที่พิมพ์ไม่เป็นอธิกสุรทิน แต่ค.ศ.ที่คิดได้กลายเป็นอธิกสุรทินแทน ใครที่ใช้วิธีลบด้วยเลขนี้ มักไม่เคยเทียบเลขที่วันที่ได้ว่าไม่ตรงกับวันที่เดิม

ตอนแรกผมเคยลองให้ลูกศิษย์ดูแล้วนึกว่าใช้ได้ แต่ต่อมาพอลองใช้ไปหลายๆช่วงปี ก็พบว่าวิธีนี้อันตรายมาก ห้ามใช้เด็ดขาด

ทางที่ดีควรใช้สูตรปรับค่าดีกว่าครับ
=Date(Year(Cell)-543, Month(Cell), Day(Cell))

แต่ถ้าอยากใช้เลขไปลบ ต้องใช้เลข 198326 แทนในช่วงเดือนและปีตามแฟ้มแนบนี้ครับ

[สมเกียรติ]

เมื่อปีค.ศ.2000 ผมเคยเขียนรหัส VBA ใช้แก้ไขเซลล์วันที่ซึ่งบันทึกผิดเป็นปีพ.ศ. ให้แก้ไขกลับเป็นปีค.ศ. เลยนำมาให้ทดลองใช้กันครับ ถ้าใครพบว่าทำงานผิดพลาด ขอให้แจ้งให้ผมทราบด้วย (ดูรหัสในแฟ้มแนบดีกว่าที่ผมยกมาแสดงครับ)

ถ้าเลือกเซลล์เดียว จะถือว่าให้แก้ไขทั้งชีท แต่ถ้าเลือก 2 เซลล์ขึ้นไป จะแก้ไขให้เฉพาะพื้นที่ตารางที่เลือกไว้ครับ

Sub BE2AD()
    On Error Resume Next
    Dim WorkRange As Range
    Dim DateCell As Range
    Application.ScreenUpdating = False
    If TypeName(Selection) <> "Range" Then Exit Sub
    If Selection.Count = 1 Then
        Set WorkRange = Cells
        Else
        Set WorkRange = Selection
    End If
 
    UserChoice = MsgBox("Correct in this Sheet?", 
vbYesNo + vbDefaultButton1, 
"Excel Expert Utility from www.ExcelExpertTraining.com")
        If UserChoice = vbYes Then
            UserComment = MsgBox("Need Comment?", 
vbYesNo + vbDefaultButton1, 
"Excel Expert Utility from www.ExcelExpertTraining.com")
            PivotYear = InputBox("Possible maximum AD Year" & Chr(10) & Chr(10) & "", 
"Excel Expert Utility from www.ExcelExpertTraining.com", 2442)
            If Val(PivotYear) < 2442 Then
                MsgBox "Below 2442 is not possible", , 
"Excel Expert Utility from www.ExcelExpertTraining.com"
                PivotYear = 2442
            End If
        End If
 
    Set DateCell = WorkRange.SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each cell In DateCell
            If IsDate(cell) Then
                If UserChoice = vbYes Then
                    usedyear = Application.WorksheetFunction.Text(cell, "yyyy")
                        If (Val(usedyear) > Val(PivotYear)) And (Val(usedyear) < 2810) Then
                            newyear = Val(usedyear) - 543
                            If UserComment = vbYes Then
                                Application.DisplayCommentIndicator = xlCommentIndicatorOnly
                                cell.ClearComments
                                cell.AddComment
                                cell.Comment.Text Text:="" & Chr(10) & 
"Corrected date input from year " & 
usedyear & " to " & newyear
                            End If
                        UsedMonth = Month(cell)
                        UsedDay = Day(cell)
                        newdate = DateSerial(newyear, UsedMonth, UsedDay)
                        cell.Value = newdate
                        End If
                End If
            End If
    Next cell
End Sub

[สมเกียรติ]

ผมสร้างใหม่เป็น Add-in เมื่อ load เข้ามาจะพบเมนูชื่อ BE2AD แสดงขึ้นติดกับเมนู Help ครับ จะได้นำไปใช้งานกับทุกแฟ้มที่ต้องการกันได้สะดวกขึ้น

หน้าที่ของ add-in ตัวนี้ คือ จะทำการค้นหาเซลล์ที่มีการบันทึกวันที่ซึ่งใช้เลขปีพ.ศ.ไว้ จากนั้นจะแก้ไขให้เป็นเลขปีค.ศ.ที่ถูกต้องให้ทันทีทุกเซลล์ในชีท พร้อมกับสามารถเลือกให้แสดง comment กำกับเซลล์ที่บันทึกไว้ผิดด้วยหรือไม่ก็ได้

หวังว่า add-in ตัวนี้จะมีประโยชน์สำหรับคนไทยโดยทั่วไปครับ
:D

[สมเกียรติ]

ข้อควรระวังของการใช้สูตร =Date(Year(Cell)-543, Month(Cell), Day(Cell)) และ Add-in BE2AD

• ใช้ได้กับค่าที่ชิดขวาของเซลล์เท่านั้น (ถือว่ามีค่าเป็นตัวเลข Date Serial Number ที่ Excel รู้จัก)
• กรณีที่มีการบันทึกวันที่ 29/2/เลขปีพศ จะพบว่าค่าที่บันทึกชิดซ้ายของเซลล์ จะใช้สูตร =Date(Year(Cell)-543, Month(Cell), Day(Cell)) หรือ add-in นี้แก้ไม่ได้
  
  เช่น วันที่ต่อไปนี้ 29/2/2503 29/2/2507 29/2/2511 29/2/2515 29/2/2519 29/2/2523 29/2/2527 29/2/2531 29/2/2535 29/2/2539 29/2/2543 29/2/2547 29/2/2551

[สมเกียรติ]

ผมแก้ไข BE2AD.xla ให้มีเมนูเพิ่มสำหรับสั่งแก้ไขเซลล์ที่บันทึกวันที่ 29/2/2503 29/2/2507 29/2/2511 29/2/2515 29/2/2519 29/2/2523 29/2/2527 29/2/2531 29/2/2535 29/2/2539 29/2/2543 29/2/2547 29/2/2551 ซึ่งชิดซ้ายของเซลล์ ให้เป็นปีค.ศ.แล้วชิดขวาได้ด้วย

โดยก่อนจะสั่งงานให้เลือกเซลล์ที่ต้องการแก้ไขแล้วจึงคลิกที่เมนู BE2AD > Change from BE to AD > Correct only 29/2 which is text

Add-in ตัวนี้จะเลือกแก้ไขเฉพาะเซลล์ซึ่งมีวันที่ซึ่งเป็นไปได้เท่านั้นครับ เช่น 29/2/2551 มีวันที่จริง แต่ถ้าบันทึกเป็น 29/2/2550 ซึ่งไม่มีวันที่นั้น อย่างไรก็จะใช้แก้ไขให้ถูกต้องด้วย add-in นี้ไม่ได้

[สมเกียรติ]

สรุปกรณีต้องการแก้ไขโดยใช้วิธีคำนวณ

1. ไม่ควรใช้วิธีนำเลข 198327 ไปลบกับวันที่ซึ่งบันทึกปีพ.ศ.เอาไว้ เพราะยากจะทราบก่อนล่วงหน้าว่าเซลล์วันที่ใดที่จะคืนค่าได้ถูกต้อง และถ้าใช้วิธี Paste Special แบบ Subtract ทับลงไปในเซลล์วันที่เลย ยิ่งทำให้ยากจะทราบว่าเซลล์ใดที่วันที่ถูกแก้ไขแล้วไม่ตรงกับวันที่เดิมที่บันทึกไว้เดิม
2. ถ้าใช้สูตรปรับเลขปีพ.ศ.โดยนำไปลบกับเลข 543 เพื่อให้ได้เลขปีค.ศ. โดยใช้สูตร =Date(Year(cell)-543, Month(cell), Day(cell)) หรือสูตรใดๆที่คิดขึ้นโดยเกี่ยวข้องกับสูตร Year(), Month(), หรือ Day() จะใช้แก้ไขได้เฉพาะเซลล์ที่มีค่าเป็นตัวเลขชิดขวาของเซลล์เท่านั้น (สูตรนี้จะใช้แก้ไขวันที่ 29/2/25xx ที่บันทึกไว้ไม่ได้เพราะค่าชิดซ้ายของเซลล์)
3. กรณีวันที่ซึ่งถูกบันทึกไว้แบบ Text ซึ่งจะชิดซ้ายของเซลล์และใช้เลขปีพ.ศ. 4 หลัก ต้องแก้ไขให้เป็นปีค.ศ.โดยใช้สูตร =DATE(RIGHT(cell,4)-543,MID(cell,FIND("/",cell)+1,FIND("/",cell,FIND("/",cell)+1)-FIND("/",cell)-1),LEFT(cell,FIND("/",cell)-1))
4. เพื่อแก้ปัญหาข้อ 2 แนะนำให้ใช้สูตร =Text(cell,"d/m/yyyy") เปลี่ยนวันที่ในเซลล์ให้เป็น Text ก่อนให้หมดแล้วจึงนำสูตรข้อ 3 มาใช้ต่อ จะได้ใช้สูตรเดียวปรับค่าได้ทั้งกรณีที่เป็นเลขและ Text ไปพร้อมกัน (ทั้งนี้ห้ามนำวันที่แบบ Text ไปใช้ในการคำนวณอย่างอื่น)

หากใช้ BE2AD.xla จะแก้ไขในเซลล์นั้นๆได้โดยตรง โดยติดตั้ง Add-in นี้ก่อนแล้วเลือกพื้นที่ตารางที่ต้องการแก้ จากนั้นใช้เมนูคำสั่ง BE2AD แก้วันที่ชิดขวาครั้งหนึ่งแล้วตามด้วยการแก้ไขวันที่ 29/2 ที่ชิดซ้ายอีกครั้งหนึ่ง

ทดลองสูตรและ Add-in ในแฟ้มแนบครับ