ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

excel ®É¶¡¬O§_¥i¥H¬Û´î

excel ®É¶¡¬O§_¥i¥H¬Û´î

·Q½Ð°Ý¤@¤U®É¶¡¬O§_¥i¥H¬Û´î
¸ó¤p®É©M¸ó¤Ñ, ¬Û´î¤U¨Ó·|¦³°ÝÃD,
·Q½Ð°Ý¤@¤U, ­n¦p¦ó­×§ï
  1. Sub TimeCal(ByVal RowNo)
  2.     Dim T1_H%
  3.     Dim T1_M%
  4.     Dim T2_H%
  5.     Dim T2_M%
  6.     Dim Diff_All!
  7.     Dim Diff_H%
  8.     Dim Diff_M!
  9.     Dim Str_T1 As Object
  10.     Dim Str_T2 As Object
  11.    
  12.     Set Str_T1 = Sheets("list").Range("G" & RowNo)    ' Str_T1 = 2015/3/9  06:49:59
  13.     Set Str_T2 = Sheets("list").Range("H" & RowNo)    ' Str_T2 = 2015/3/1  05:41:39
  14.    
  15.     T1_H = Hour(Str_T1)
  16.     T1_M = Minute(Str_T1)
  17.     T2_H = Hour(Str_T2)
  18.     T2_M = Minute(Str_T2)

  19.     Diff_H = Abs(T1_H - T2_H)
  20.     Diff_M = Abs(T1_M - T2_M) / 60
  21.     Diff_All = Diff_H + Diff_M
  22.    
  23.     Sheets("list").Range("K" & RowNo).Value = Diff_All

  24. End Sub
½Æ»s¥N½X

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-3-10 08:31 ½s¿è

¦^´_ 1# sghsgh1111
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim a, b
  4.     a = #1/1/2105 5:40:50 AM#
  5.     b = #1/3/2105 2:10:05 AM#
  6.     With Range("A2")
  7.         .Value = (b - a) * 24
  8.         .NumberFormat = "0.00®É"
  9.     End With
  10. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 1# sghsgh1111
¥i¥H¦Û¤v¸Õ¸Õ DateDiff ¨ç¼Æ
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

ÁÂÁ«ü¾É, ¤§«e¤]¬O³o¼Ëª½±µ¬Û´î, ¦ý´î«á³£¬O 0.01xxx
¦ý§Ú¬Ý¤W­±¦³¦b X 24 , ³o­Ó·N«ä¤£¬O«ÜÀ´, ¥i¥H»¡©ú¤@¤U¶Ü
¬°¤°»ò¤£¬O X 60 ???
¤£¹L¥H³o­Óºâªk¨Ó, ¬Ý°_¬Ook

TOP

¥i¥H¦A¦h°Ý¤@­Ó°ÝÃD¶Ü

QueryTables ­n¦p¦ó滙¤J¨ä¥Lªº excel ÀÉ®×
³o¬O¿ý¸m¥¨¶°©Ò²£¥Íªºµ{¦¡½X, ¦ý¤£¤F¸Ñ³o­Ó»yªk, ¥i¥H¸Ñµª¶Ü
  ³o­Ó¥¨¶°¥i¥H¦A²¤Æ¶Ü  

  With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDBrovider=Microsoft.Jet.OLEDB.4.0assword="""";User ID=Admin;Data Source=I:\VBA.xls;Mode=Share Deny Write;Extended Prop" _
        , _
        "erties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=3" _
        , _
        "5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Datab" _
        , _
        "ase Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=F" _
        , "alse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
        Destination:=Range("A2"))
        .CommandType = xlCmdTable
        .CommandText = Array("Status$")
        .Name = "VBA_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "I:\VBA.xls"
        .Refresh BackgroundQuery:=False
    End With

TOP

¦^´_ 5# sghsgh1111
¤é´Á¬Û´î«áªº¼Æ¦r,¾ã¼Æ¬O¤é¼Æ(1¤Ñ¦³24¤p®É),¤p¼ÆÂI«áªº¼Æ¦r¬O¤p®É¼Æ(*60=¤ÀÄÁ¼Æ)
d1-d2 ¦p=2.15 -> 2.15*24=51.6¤p®É,2.15*24*60=3096¤ÀÄÁ,2.15*24*60*60=185760¬í

¹ï¶×¤J¥~³¡¸ê®Æ¤£¼ô±x¥i°Ñ¦Ò VBAªº QueryTable ª«¥ó»¡©ú
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁ¤j¤jªº»¡©ú

TOP

¦^´_  sghsgh1111
¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2015-3-10 08:13


½Ð°Ý¦pªG¬O¥HCELLªºINPUTÀ³¦p¦ó§ï?
¦p:
A1           A2            Result
13:00     15:00        2¤p®É
22:00     03:00        5¤p®É

TOP

  1. Sub ex()
  2.     Dim a, b, c

  3.     For c = 1 To 20
  4.         a = Range("A" & c + 1).Value
  5.         b = Range("B" & c + 1).Value
  6.         If a = "" Then Exit For
  7.         If b < a Then b = b + 1
  8.         With Cells(c + 1, 3)
  9.             .Value = (b - a) * 24
  10.             .NumberFormat = "0.00®É"
  11.         End With
  12.     Next
  13. End Sub
½Æ»s¥N½X
¦^´_ 8# xmi

TOP

¦^´_ 9# sghsgh1111

ÁÂÁ§A...
½Ð°Ý¦pªG¬O¥H¤U§Î¦¡¤SÀ³¦p¦ó§ï?
¦p:
A1           A2            Result
13:00     15:00        2¤p®É
22:00     27:00        5¤p®É
23:00     25:45   2.75¤p®É

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD