comment: 7
โปรแกรมคำนวณช่วงเวลาใน Excel
เอ๊ะ งานเริ่มตอน 9:05 ไปถึง 10:40 แล้วต่อตอน 11:00 - 11:50 นี่ รวมแล้ว กี่นาที ?
คำถามแบบนี้ มักเกิดกับคนที่ต้องดูแลบันทึกสถิติภาระงาน หรือคำนวณเวลาที่ใช้ในการจัดประชุม จะได้คำนวณค่าตอบแทนได้
ผมเคยเขียนฟังก์ชันใช้งานเองสำหรับช่วยคำนวณในงานเช่นว่านี้
เช่น ในเซลล์ C27 บันทึกช่วงเวลาว่า 905-1040, 11-1150
ในเซลล์ข้าง ๆ คือ D27 เราอยากให้คำนวณออกมาเป็นนาที
ในช่อง D27 เราก็พิมพ์ว่า =NetTimeRange(C27)
คือมีฟังก์ชันชื่อ NetTimeRange ที่สามารถเรียกใช้ได้ ค่าที่รายงาน จะมีหน่วยเป็นนาที
กรณีนี้ โปรแกรมก็จะคำนวณให้เป็น 145 นาที
กติกาในการเขียนเวลา
- สามารถเขียนหลายช่วงเวลาได้ คั่นด้วย , หรือ ;
- เวลาสามารถเขียนเลียนแบบการอ่านเวลาแบบบ้าน ๆ ได้ เช่น 9 ครึ่ง ก็เขียน 930 หรือ 11 โมงตรง เขียนเป็น 11 เฉย ๆ หรือจะเขียนเป็น 1100 ก็ตามใจ
- ช่วงเวลาจากเวลาหนึ่งถึงอีกเวลาหนึ่ง ใช้ - คั่นกลาง
ตัวอย่างที่ใช้ได้
- 9-930
- 230-1630
- 1315-16, 17-1745
ขั้นตอน
กรณีนี้ ต้องติดตั้งโค้ด VB ลงใน Excel ก่อน เสร็จแล้ว เราสามารถเรียกใช้บริการฟังก์ชันที่ผมเขียนไว้
เริ่มต้นใน Excel ทำอย่างนี้

ขึ้นจอใหม่ รีบสั่ง Insert Module เลย
ก็จะเกิดพื้นขาวว่าง ๆ ด้านข้าง ให้แปะโค้ดต่อไปนี้ แปะเสร็จ ก็คลิกปิดหน้าจอ Module ไปเลย กลับเข้าสู่โหมดการใช้ Excel
Function MinutePart(msg As String) As Double
If InStr(msg, ":") Then
Cut = InStr(msg, ":")
MinutePart = Val(Mid(msg, Cut + 1))
Else
If Len(msg) <= 2 Then
MinutePart = 0
Else
MinutePart = Val(Right(Trim(msg), 2))
End If
End If
End Function
Function HourPart(msg As String) As Double
msg = Trim(msg)
If InStr(msg, ":") Then
Cut = InStr(msg, ":")
HourPart = Val(Left(msg, Cut - 1))
Else
If Len(msg) <= 2 Then
HourPart = Val(msg)
Else
HourPart = Val(Left(msg, Len(msg) - 2))
End If
End If
End Function
Function NetTimeRange(emsg As String) As Double
Dim a As String, b As String, msg As String, e As String
Dim m0 As Double, m1 As Double, h0 As Double, h1 As Double, Cum As Double
Dim Cut As Integer, i As Integer
For i = 1 To Len(emsg)
e = Mid(emsg, i, 1)
If e = "," Or e = "/" Or e = "&" Or e = "+" Or e = "|" Then Mid(emsg, i, 1) = ";"
Next i
If InStr(emsg, ";") = 0 Then
'it is one time range, such as 1530-1645
Cum = TimeRange(emsg)
Else
Cum = 0
Do While InStr(emsg, ";")
Cut = InStr(emsg, ";")
msg = Left(emsg, Cut - 1)
emsg = Mid(emsg, Cut + 1)
Cum = Cum + TimeRange(msg)
Loop
Cum = Cum + TimeRange(emsg)
End If
NetTimeRange = Cum
End Function
Function TimeRange(msg As String) As Double
Dim a As String, b As String
Dim m0 As Double, m1 As Double, h0 As Double, h1 As Double
Cut = InStr(msg, "-")
If Cut Then
a = Trim(Left(msg, Cut - 1))
b = Trim(Mid(msg, Cut + 1))
m0 = MinutePart(a)
m1 = MinutePart(b)
h0 = HourPart(a)
h1 = HourPart(b)
TimeRange = 60 * (h1 - h0) + (m1 - m0)
Else
a = ""
b = ""
TimeRange = 0
End If
End Function
ข้อควรระวัง
บางเครื่อง จะมีระบบป้องกันไวรัสแบบเข้มข้น อาจไม่ยอมทำงานให้ ต้องไปลดระดับลงมาหน่อย ให้ยอมใช้งาน macro ได้ (จำวิธีไม่ได้ครับ ถาม ๆ เอาจากเทพใกล้บ้านละกัน ^ ^)
บันทึกอื่นๆ
- เก่ากว่า « ราก ทางภาษา
- ใหม่กว่า » หัดภาษาด้วย [ซับนรก]
comment
ต่อครับ.....
cell A1 เติมเวลา เป็น 09:30
cell B1 เติมเวลา เป็น 10:40
cell C1 พิมพ์ว่า =minute(B1-A1) ก็จะได้ความแตกต่างเวลาเป็นนาทีครับ :-)
minute ใน OpenOffice.org ไม่สามารถใช้งานได้เหมือนกับ function ที่ อ.วิบุล เขียนไว้ครับ เพราะมันจะหาความแตกต่างของส่วนที่เป็นนาที โดยไม่สนใจตัวเลขชั่วโมงเลยครับ
- ขอบคุณทุกท่านครับ
- ตั้งใน Excel ให้ลดระดับความ "ด้น" ลงมานี่ ผมจำไม่ได้ หากสะดวกจะขยายความ ก็ขอเชิญนะครับ ^ ^
เป็นจริงอย่างที่ อ.ปัญญรักษ์ ว่าไว้ครับ ก็เปลี่ยนตามนี้ครับ
cell A1 เติมเวลา เป็น 09:30
cell B1 เติมเวลา เป็น 10:40
cell C1 พิมพ์ว่า = hour(B1-A1)*60+minute(B1-A1) ก็จะได้ความแตกต่างเวลาเป็นนาทีครับ :-)
![]() |
การคำนวณเวลา8.00-18.30แต่มีเวลาพักขั่นกลาง2รอบจะคำนวณในโปรแกรมเอ็กเซลยังไงค่ะ
สมมติว่าพัก 10:30-10:45, 12:00-13:15, 14:15-14:25 เราก็สามารถสั่งต่อเป็นพรืดในเซลล์เดียวอย่างนี้
8-1030, 1045-12, 1315-1415, 1425-1830
เวลาอ่านคือ ให้คิดช่วง 8:00 ถึง 10:30 + ช่วง 10:45-12:00 + ช่วง 13:15-14:15 + ช่วง 14:25-18:30 น
หลักการคือ "จากเวลา-ถึงเวลา" ให้หมายถึงช่วงที่เราจะนับ ช่วงไหนพัก ก็ไม่ต้องไปยุ่งครับ
19 มกราคม 2553 07:15
#53088
ใน OpenOffice Calc ผมใช้ฟังก์ชันของ Date&Time ดังนี้ครับ
เช่น cell A1 อาจเติมเวลาเช่น 09:30