Board logo

¼ÐÃD: [µo°Ý] ®É¶¡½d³ò¤ñ¹ï°ÝÃD [¥´¦L¥»­¶]

§@ªÌ: child    ®É¶¡: 2011-10-12 10:17     ¼ÐÃD: ®É¶¡½d³ò¤ñ¹ï°ÝÃD

¥»©«³Ì«á¥Ñ child ©ó 2011-10-12 10:23 ½s¿è

·Q°w¹ï¬Y¬q®É¶¡§ä¥X²Å¦X®É¶¡½d³ò¤ºªº¸ê®Æ¡A»¡©ú¦p¤U:

¨Ò:
°w¹ï2011/10/11 8:00~2011/10/11 14:30ªº®É¶¡½d³ò¡A¤ñ¹ï«Ø¥ß®É¶¡(BÄæ)¨ì­×§ï®É¶¡(CÄæ)ªº®É¶¡½d³ò(¦hµ§¸ê®Æ)¡A­Y«Ø¥ß®É¶¡~­×§ï®É¶¡ªº®É¶¡½d³ò¤º¦³®É¶¡­è¦n¸¨¦b»Ý¤ñ¹ïªº®É¶¡½d³ò¤º¡ADÄæ¤ñ¹ïµ²ªGÅã¥ÜY¡F­Y¤£¦b»Ý¤ñ¹ï®É¶¡½d³ò¤º«hÅã¥ÜN¡C

«ÜÁÂÁ¤j®aªºÀ°¦£^_^

ÀɮצWºÙ        «Ø¥ß®É¶¡            ­×§ï®É¶¡                             ¤ñ¹ï
AA        2011/10/1 08:30        2011/10/11 12:00        Y
AB        2011/10/1 09:05        2011/10/6 20:15        N
AC        2011/10/6 09:32        2011/10/11 13:43        Y
DD        2011/10/11 15:00        2011/10/12 17:43        N
§@ªÌ: GBKEE    ®É¶¡: 2011-10-12 15:12

¦^´_ 1# child
¸Õ¸Õ¬Ý
  1. Sub Ex()
  2.     Dim A As Long, B As Long, i As Integer
  3.     A = #10/11/2011 8:00:00 AM#
  4.     B = #10/11/2011 2:30:00 PM#
  5.     For i = 1 To [C1].End(xlDown).Row
  6.         If Cells(i, "C") >= A And Cells(i, "C") <= B Then
  7.             Cells(i, "D") = "Y"
  8.         Else
  9.             Cells(i, "D") = "N"
  10.         End If
  11.     Next
  12. End Sub
½Æ»s¥N½X

§@ªÌ: child    ®É¶¡: 2011-10-12 16:17

¦^´_ 2# GBKEE

ª©¤j¦n^_^
ÁÂÁª©¤jªºÀ°¦£

¤£¦n·N«ä!­è­è¨S¦³±N°ÝÃDÂI»¡©ú²M·¡¡A¥D­n¬O·Q°w¹ï«Ø¥ß®É¶¡©M­×§ï®É¶¡¨â­ÓÄæ¦ì¤§¶¡ªº®É¶¡½d³ò¤ñ¹ï¡C
¥H2011/10/11 8:00~2011/10/11 14:30ªº®É¶¡½d³ò¡A¤U¦C¸ê®Æ­Y¥u¦³¤ñ¹ï­×§ï®É¶¡·|¤ñ¹ï¤£¨ì¡C

»¡©ú:
¬d¸ß®É¶¡2011/10/11 8:00~2011/10/11 14:30ªº®É¶¡°Ï¬q¡A­Y³o¬q®É¶¡¦³®É¶¡¸¨¦b«Ø¥ß®É¶¡~­×§ï®É¶¡³o¬q®É¶¡½d³òùØ¡A«h¥Nªí¦³¤ñ¹ï¨ì¡C

ÀɮצWºÙ        «Ø¥ß®É¶¡            ­×§ï®É¶¡                             ¤ñ¹ï
AA        2011/10/1 08:30        2011/10/12 12:00        Y
AB        2011/10/11 09:05      2011/10/13 20:15        Y
BA        2011/10/1 09:05        2011/10/6 20:15           N

«ÜÁÂÁª©¤jªºÀ°¦£¡A·P¿E¤£ºÉ^_^
§@ªÌ: GBKEE    ®É¶¡: 2011-10-12 16:42

¦^´_ 3# child
If Cells(i, "C") >= A And Cells(i, "C") <= B Then  §ï¦¨ ->  If Cells(i, "B") >= A And Cells(i, "C") <= B Then
§@ªÌ: child    ®É¶¡: 2011-10-12 17:40

¦^´_ 4# GBKEE


¤£¦n·N«ä¤@ª½³Â·Ðª©¤j¡A¤§«e²Ä¤@¦¸´ú¸Õ¦³­×§ï¦¨If Cells(i, "B") >= A And Cells(i, "C") <= B Then¡A
¦ý¬O¦]¬°­n¤ñ¹ï¬O®É¶¡½d³ò¡A©Ò¥HµLªk§e²{¦Û¤v·Q­nªºµ²ªG¡A¦p¤U:
¬d¸ß®É¶¡°Ï¬q:2011/10/11 8:00~2011/10/11 14:30
¤ñ¹ï2011/10/11 09:05~2011/10/13 20:15 ¡A2011/10/11 09:05 >2011/10/11 14:30¤ñ¹ï¤£¨ì¡C
¤ñ¹ï2011/10/1 08:30~2011/10/12 12:00 ¡A2011/10/1 08:30<2011/10/11 8:00¡B2011/10/12 12:00 >2011/10/11 14:30¤ñ¹ï¤£¨ì¡C


µ{¦¡¤ñ¹ï¤è¦¡»¡©ú:
2011/10/11 8:00~2011/10/11 14:30ªº®É¶¡°Ï¬q¤ñ¹ï¤U¦C®É¶¡°Ï¬q:
¦³³¡¤À®É¶¡¸¨¦b2011/10/11 09:05~2011/10/13 20:15¤§¶¡¡÷¦³¤ñ¹ï¨ì
¦³³¡¤À®É¶¡¸¨¦b2011/10/1 08:30~2011/10/12 12:00¤§¶¡¡÷¦³¤ñ¹ï¨ì
µL®É¶¡ÂI¸¨¦b2011/10/1 09:05~2011/10/6 20:15¤§¶¡¡÷¨S¦³¤ñ¹ï¨ì

§e²{µ²ªG:
ÀɮצWºÙ        «Ø¥ß®É¶¡            ­×§ï®É¶¡                             ¤ñ¹ï
AA        2011/10/1 08:30        2011/10/12 12:00        Y
AB        2011/10/11 09:05      2011/10/13 20:15        Y
BA        2011/10/1 09:05        2011/10/6 20:15           N

¯u¬O©êºp¨S¦³±N°ÝÃD»¡©ú²M·¡¡A«ÜÁÂÁª©¤jªºÀ°¦£^_^
§@ªÌ: Hsieh    ®É¶¡: 2011-10-12 18:10

¦^´_ 5# child
  1. Sub nn()
  2. a = #10/11/2011 8:00:00 AM#
  3. b = #10/11/2011 2:30:00 PM#
  4. For Each C In Range([C2], [C2].End(xlDown))
  5. d = C.Offset(, -1)
  6. If (a >= d And a <= C) Or (b >= d And b <= C) Then C.Offset(, 1) = "Y" Else C.Offset(, 1) = "N"
  7. Next
  8. End Sub
½Æ»s¥N½X

§@ªÌ: child    ®É¶¡: 2011-10-12 20:11

¦^´_ 6# Hsieh


    «ÜÁÂÁÂHsiehª©¤jªºÀ°¦£¡A­è­è´ú¸Õµo²{­Y¤ñ¹ï®É¶¡­è¦n¦b¬d¸ß®É¶¡2011/10/11 8:00~2011/10/11 14:30ªº½d³ò¤º¡A¦n¹³´N¤ñ¹ï¤£¨ì¡C

ÀɮצWºÙ        «Ø¥ß®É¶¡            ­×§ï®É¶¡                            ¤ñ¹ï
DD        2011/10/11 09:05        2011/10/11 10:05        N

ÁÂÁÂHsiehª©¤jªºÀ°¦£^_^
§@ªÌ: Hsieh    ®É¶¡: 2011-10-12 20:33

¦^´_ 7# child


    ¨S¿ù°Ú!
[attach]8225[/attach]
§@ªÌ: child    ®É¶¡: 2011-10-12 21:06

¦^´_ 8# Hsieh


    ÁÂÁÂHsiehª©¤j±zªºÀ°¦£¡A­è­è´ú¸Õµo²{­Y­×§ï®É¶¡¦b2011/10/11  13:00:00 PM¤§¤º¡A¦n¹³´N¤ñ¹ï¤£¨ì¡A¦ý­Y¤j©ó2011/10/11  13:00:00 PM«h¥i¥H¡C´ú¸Õµ²ªG¦p¤U:

ÀɮצWºÙ        «Ø¥ß®É¶¡                                 ­×§ï®É¶¡                                                   ¤ñ¹ï
AA        2011/10/11 08:30:00 AM        2011/10/11 12:59:59 PM        N
AB        2011/10/11 09:05:00 AM        2011/10/13  13:00:00 PM        Y
DE        2011/10/11 09:00:00 AM        2011/10/11 10:43:00 AM        N

ÁÙ¬O»Ý­n§@¤°»ò³]©w?
§@ªÌ: Hsieh    ®É¶¡: 2011-10-12 23:01

¦^´_ 9# child
§PÂ_¦¡ªº±ø¥ó¬Ý¬Ý³o¼Ë°÷¤£°÷
  1. Sub nn()
  2. a = #10/11/2011 8:00:00 AM#
  3. b = #10/11/2011 12:30:00 PM#
  4. For Each C In Range([C2], [C2].End(xlDown))
  5. d = C.Offset(, -1)
  6. If (a >= d And a <= C) Or (a <= d And a <= C) Or (b >= d And C <= b) Then C.Offset(, 1) = "Y" Else C.Offset(, 1) = "N"
  7. Next
  8. End Sub
½Æ»s¥N½X

§@ªÌ: child    ®É¶¡: 2011-10-12 23:53

¦^´_ 10# Hsieh


     ¦A¦¸ÁÂÁÂHsieh©MGBKEE ª©¤jªºÀ°¦£^_^ ·P¿E¤£ºÉ!
     ÁöµM´ú¸ÕÁÙ¬O¦³¨Ç®t²§¡A¦ý¬Oª©¤jªº¼ö¤ß´£¨Ñ¤F¤p§Ì³\¦h«ä¦Òªº¤è¦V¡A«ÜÁÂÁ§A­ÌªºÀ°¦£^_^
§@ªÌ: Hsieh    ®É¶¡: 2011-10-13 21:01

¦^´_ 11# child

¥Î²Â¤èªk¸Ñ¨M
  1. Sub nn()
  2. Dim i#
  3. Set d = CreateObject("Scripting.Dictionary")
  4. a = CDbl(#10/11/2011 8:00:00 AM#)
  5. b = CDbl(#10/11/2011 2:30:00 PM#)
  6. For Each c In Range([C2], [C2].End(xlDown))
  7. k = Int(a) - Int(c.Offset(, -1))
  8. c.Offset(, 1) = "N"
  9. For i = IIf(k = 0, c.Offset(, -1), Int(c.Offset(, -1)) + k - 1) To c Step TimeValue("00:01:00")
  10.    If i >= a And i <= b Then c.Offset(, 1) = "Y": Exit For
  11. Next
  12. Next
  13. End Sub
½Æ»s¥N½X





Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)