PDA

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



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

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

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

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

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

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

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

สมเกียรติ
9 Jun 2008, 13:24
เมื่อปีค.ศ.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 (http://www.ExcelExpertTraining.com)")
If UserChoice = vbYes Then
UserComment = MsgBox("Need Comment?",
vbYesNo + vbDefaultButton1,
"Excel Expert Utility from www.ExcelExpertTraining.com (http://www.ExcelExpertTraining.com)")
PivotYear = InputBox("Possible maximum AD Year" & Chr(10) & Chr(10) & "",
"Excel Expert Utility from www.ExcelExpertTraining.com (http://www.ExcelExpertTraining.com)", 2442)
If Val(PivotYear) < 2442 Then
MsgBox "Below 2442 is not possible", ,
"Excel Expert Utility from www.ExcelExpertTraining.com (http://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

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

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

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

สมเกียรติ
15 Jun 2008, 10:19
ข้อควรระวังของการใช้สูตร =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

สมเกียรติ
16 Jun 2008, 12:38
ผมแก้ไข 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 นี้ไม่ได้

สมเกียรติ
16 Jun 2008, 15:01
สรุปกรณีต้องการแก้ไขโดยใช้วิธีคำนวณ
ไม่ควรใช้วิธีนำเลข 198327 ไปลบกับวันที่ซึ่งบันทึกปีพ.ศ.เอาไว้ เพราะยากจะทราบก่อนล่วงหน้าว่าเซลล์วันที่ใดที่จะคืนค่าได้ถูกต้อง และถ้าใช้วิธี Paste Special แบบ Subtract ทับลงไปในเซลล์วันที่เลย ยิ่งทำให้ยากจะทราบว่าเซลล์ใดที่วันที่ถูกแก้ไขแล้วไม่ตรงกับวันที่เดิมที่บันทึกไว้เดิม
ถ้าใช้สูตรปรับเลขปีพ.ศ.โดยนำไปลบกับเลข 543 เพื่อให้ได้เลขปีค.ศ. โดยใช้สูตร =Date(Year(cell)-543, Month(cell), Day(cell)) หรือสูตรใดๆที่คิดขึ้นโดยเกี่ยวข้องกับสูตร Year(), Month(), หรือ Day() จะใช้แก้ไขได้เฉพาะเซลล์ที่มีค่าเป็นตัวเลขชิดขวาของเซลล์เท่านั้น (สูตรนี้จะใช้แก้ไขวันที่ 29/2/25xx ที่บันทึกไว้ไม่ได้เพราะค่าชิดซ้ายของเซลล์)
กรณีวันที่ซึ่งถูกบันทึกไว้แบบ 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))
เพื่อแก้ปัญหาข้อ 2 แนะนำให้ใช้สูตร =Text(cell,"d/m/yyyy") เปลี่ยนวันที่ในเซลล์ให้เป็น Text ก่อนให้หมดแล้วจึงนำสูตรข้อ 3 มาใช้ต่อ จะได้ใช้สูตรเดียวปรับค่าได้ทั้งกรณีที่เป็นเลขและ Text ไปพร้อมกัน (ทั้งนี้ห้ามนำวันที่แบบ Text ไปใช้ในการคำนวณอย่างอื่น)
หากใช้ BE2AD.xla จะแก้ไขในเซลล์นั้นๆได้โดยตรง โดยติดตั้ง Add-in นี้ก่อนแล้วเลือกพื้นที่ตารางที่ต้องการแก้ จากนั้นใช้เมนูคำสั่ง BE2AD แก้วันที่ชิดขวาครั้งหนึ่งแล้วตามด้วยการแก้ไขวันที่ 29/2 ที่ชิดซ้ายอีกครั้งหนึ่ง

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