กฏกติกาของฟอรัม : แจ้งชื่อและนามสกุลจริงในการสมัครสมาชิก--ตั้งชื่อกระทู้ให้ตรงกับปัญหาที่ถาม--เขียนอธิบายคำถามทั้งในตัวกระทู้และในแฟ้มแนบให้ละเอียด--ห้ามใช้คำว่า อ่ะ คับ หรือคำผวน หรือคำที่ไม่มีในพจนานุกรมไทย--โปรดตั้งชื่อแฟ้มให้สื่อถึงปัญหาที่ถาม--อย่าระบุชื่อให้คนใดคนหนึ่งเป็นผู้ตอบ--งดให้ความร่วมมือหรือช่วยตอบคำถาม กระทู้ที่ละเมิดกฏกติกาของฟอรัม
Results 1 to 7 of 7

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

  1. #1

    Join Date
    Jun 2008
    Location
    43/4 หมู่13 บางนาตราด บางแก้ว บางพลี สมุ
    Job
    งานบุคคล
    Version
    Excel 2003/ Windows XP
    Posts
    5

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

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

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


  2. #2
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    7,050
    Blog Entries
    5

    Default

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

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

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

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

    แต่ถ้าอยากใช้เลขไปลบ ต้องใช้เลข 198326 แทนในช่วงเดือนและปีตามแฟ้มแนบนี้ครับ
    Last edited by สมเกียรติ; 10 Jun 2008 at 14:00.

  3. #3
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    7,050
    Blog Entries
    5

    Default

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

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

    Code:
    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
    Last edited by สมเกียรติ; 10 Jun 2008 at 17:59.

  4. #4
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    7,050
    Blog Entries
    5

    Default BE2AD Add-in

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

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

    หวังว่า add-in ตัวนี้จะมีประโยชน์สำหรับคนไทยโดยทั่วไปครับ
    :D
    Attached Thumbnails Attached Thumbnails BE2ADMenu.gif‎  
    Last edited by สมเกียรติ; 16 Jun 2008 at 12:31. Reason: แก้ไข add-in ให้แก้ไขเซลล์ที่บันทึก 29/2/ปีพ.ศ.

  5. #5
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    7,050
    Blog Entries
    5

    Default ห้ามใช้กรณีวันที่บันทึกไว้แบบ Text

    ข้อควรระวังของการใช้สูตร =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

  6. #6
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    7,050
    Blog Entries
    5

    Default

    ผมแก้ไข 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 นี้ไม่ได้

  7. #7
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    7,050
    Blog Entries
    5

    Default

    สรุปกรณีต้องการแก้ไขโดยใช้วิธีคำนวณ
    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 ในแฟ้มแนบครับ

Similar Threads

  1. Replies: 3
    Last Post: 12 Nov 2009, 14:01
  2. เปลี่ยนระบบภาษาจาก TIS-620 เป็น UTF-8
    By Somkiat in forum บันทึกการติดตั้งและปรับปรุงฟอรัม
    Replies: 1
    Last Post: 16 Jun 2009, 10:37
  3. Replies: 2
    Last Post: 3 Apr 2009, 17:09
  4. Replies: 6
    Last Post: 24 Jan 2009, 11:56
  5. Replies: 1
    Last Post: 6 Sep 2008, 16:48

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •