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

®³¨ú¤é´Á

®³¨ú¤é´Á

¦U¦ì¤j¤j:

§Ú¼g¤F¤@¬qµ{¦¡¬O¿z¿ï¤F¬Y¤@­Ó¯S©w¤é¤l«á¦A½Æ»s¨ì¥t¤@­Ó¬¡­¶Á¡, ¦ý§Ú¥u¯à¿z¿ï¨ì¡¨05/05/20¡¨ ¦Ó¨ä¥L¤é¤l´N¿z¿ï¤£¨ì.

¼Æ¾Ú¦b"grouping"
¿z¿ï¦b"daily sales"

½Ð¦U¦ì«ü±Ð, ÁÂÁÂ

¼Ë¿ì.rar (46.57 KB)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-22 16:41 ½s¿è

¦^´_ 1# mdr0465


³o®É­Ô´N­n¥J²Ó¾\Ū ·L³nµ¹ªº¨Ï¥Î»¡©ú¤F~

Excel ªº¿z¿ï¥\¯à¦³­­¨î¡A¥u¯à¦r¦ê¤ñ¹ï~~ ¦p¤U¹Ï

©Ò¥H¤£¯à¤é´Á/¼Æ¦r¤ñ¹ï~


¿z¿ï­­¨î.png
2020-8-22 16:33


§â¤é´Á®æ¦¡§ï¤@¤U´N¦n¤F¡AÂ^¨ú§A³¡¤Àµ{¦¡¨Ó»¡©ú

D1 = DateSerial(CInt(splitstr(2)), CInt(splitstr(1)), CInt(splitstr(0)))
S1$ = Format(D1, "dd/mm/yy")
Set xR = Range("GROUPING!A1:K10000")
With xR
    .AutoFilter
    .AutoFilter Field:=2, Criteria1:=S1
    Range("GROUPING!A1").CurrentRegion.Copy
    Sheets("DAILY SALES").Range("A1").PasteSpecial xlValues
    .AutoFilter
End With
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# n7822123


ªüÀs®v¥S
ÁÂÁ§Aªº¦^ÂÐ,¦ý§Ú§ï¤F¬õ¦âªº¦r²´,¦ý³£¬O¹B¦æ¤£¤F, ½Ð°Ý§Ú°ÝÃD¥X¦b¨º¨½©O?

§Ú¿é¤J¬O
"01/06/20"
"1/6/20"
"01/06/2020"

TOP

¦^´_ 3# mdr0465

¦]¬°§Ú°O±o§Aªºµ{¦¡ÁÙ¦³¨ä¥L°ÝÃD....................

§Ú§ï¤F¤@¨Ç¡A¤w§Ñ°O§ï¤F­þ¨Ç¤F¡Aµ{¦¡¶Kµ¹§A

¿é¤J¤é´Áªº®É­Ô¤£­n¿é¤J Âù¤Þ¸¹ "

§A¦Û¤v¤ñ¹ï§a~~µ{¦¡¦p¤U


Sub Daily_Sales()
Dim xR As Range
Dim D1 As Date
Dim strformat As String
Dim splitstr() As String
Dim str As String
On Error Resume Next
Sheets("DAILY SALES").Range("A2:K1000").Clear
str = InputBox("Please Input The Date You Want to Search" & Chr(10) & Chr(10) & "Input Format ""DD/MM/YY""")
splitstr = Split(str, "/")
D1 = DateSerial(CInt(splitstr(2)), CInt(splitstr(1)), CInt(splitstr(0)))
S1$ = Format(D1, "dd/mm/yy")
Set xR = Range("GROUPING!A1:K10000")
With xR
    .AutoFilter
    .AutoFilter Field:=2, Criteria1:=S1
    Range("GROUPING!A1").CurrentRegion.Copy
    Sheets("DAILY SALES").Range("A1").PasteSpecial xlValues
    .AutoFilter
End With
Sheets("DAILY SALES").Activate
With Range([K2], [A65536].End(xlUp)) 'set borders
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThin
    .Borders.ColorIndex = xlAutomatic
    .Font.Size = 18
    .Font.Name = "Times New Roman"
    .HorizontalAlignment = xlCenter
    .EntireColumn.AutoFit
End With
k = 2  ' for exchange date format
Do Until IsEmpty(Cells(k, 2))
    Cells(k, 2).NumberFormatLocal = "dd""/""mm""/""yy;@"
    k = k + 1
Loop
Range("A1").Select
End Sub


Àɮצp¤U

¼Ë¿ì.rar (45.49 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 1# mdr0465


¸ê®Æªí¤é´Á®æ¦¡¬°:yyyy/m/d
«ç¤S¥Î dd/mm/yy ¥h¿z¿ï??? ¹H¤Ï±`³W¤é´Áªº¿é¤J¤èªk, ¬v¤Hªº???

TOP

¦^´_ 4# n7822123

ªüÀs®v¥S
¦¨¥\¤F¡AÁÂÁ§AªºÀ°¦£

TOP

¥[­Ó¨¾§b:
Sub Daily_Sales()
Dim Qs$, QQ, R&, DateFmt$, xArea As Range
ReTry:
Qs = InputBox("½Ð¿é¤J­n¬d¸ßªº¤é´Á" & Chr(10) & Chr(10) & "¿é¤J³W«h¡GDD/MM/YY")
If Qs = "" Then Exit Sub
QQ = Split(Qs & "//", "/")
Qs = 20 & QQ(2) & "/" & QQ(1) & "/" & QQ(0)
If IsDate(Qs) = False Then MsgBox "¤é´Á¿é¤J¿ù»~, ½Ð­«·s¿é¤J! ": GoTo ReTry
QQ = DateValue(Qs)
'---------------------------------------------
Application.ScreenUpdating = False
Sheets("DAILY SALES").UsedRange.Offset(1, 0).EntireRow.Delete
Set xArea = Range([GROUPING!K1], [GROUPING!A1].Cells(Rows.Count, 1).End(xlUp))
With xArea
     .AutoFilter Field:=2, Criteria1:=QQ
     .Offset(1, 0).Copy Sheets("DAILY SALES").[A2]
     .Parent.ShowAllData
End With
With Sheets("DAILY SALES").UsedRange
     R = .Cells(.Rows.Count + 1, 2).End(xlUp).Row
     If R = 1 Then MsgBox "§ä¤£¨ì²Å¦X¤é´Áªº¸ê®Æ¡I¡@": Exit Sub
     .Columns(2).NumberFormatLocal = "dd/mm/yy"
     .EntireColumn.AutoFit
End With
End Sub

ª`·N:============================
DD = DateSerial(2020, 13, 35) '¤ë¥÷¶W¹L12, ¤é´Á¶W¹L31
MsgBox DD '¤@¼Ë·|³Qµø¬°¤é´Á

DD = DateSerial(2019, 2, 29) '2019.2¤ë¨S¦³29¤é
MsgBox DD '=2019/3/1


===============================

TOP

¦^´_ 7# ­ã´£³¡ªL


   
ª©¥D,ÁÂÁ§Aªº³Æ¥Î
¦ý§Ú¦b®M¥Î§Aªºµ{¦¡,
§Ú¥Ã»·³£¬OÅã¥Ü"§ä¤£¨ì²Å¦X¤é´Áªº¸ê®Æ¡I"

²{ªþ¤W¤w¥[µ{¦¡ªºªþ¥ó,½ÐÀ°¦£¬d¬Ý,ÁÂÁÂ

¼Ë¿ì.rar (127.09 KB)

TOP

¦^´_ 8# mdr0465


If Qs = "" Then Exit Sub
QQ = Split(Qs & "//", "/")
Qs = 20 & QQ(2) & "/" & QQ(1) & "/" & QQ(0)
If IsDate(Qs) = False Then MsgBox "¤é´Á¿é¤J¿ù»~, ­«·s¿é¤J! ": GoTo ReTry
QQ = DateSerial(QQ(2), QQ(1), QQ(0))

¥i¯à¬O¹q¸£¨t²Î¤é´Á®æ¦¡³]©w¤£¦P§a!
§Ú³o¤@©w¬O´ú¹L¤Fok¤~·|½T»{ªº,
¨ºÁÙ¬O§ï¥Îdateserial¬Ý¬Ý,
¤Ï¥¿«e­±¤w§PÂ_¹L¤é´Áªº¥¿½T©Ê,
³odateserial´N¬O¤@­Ó¥¿½T¤é´Á~~

TOP

¦^´_ 9# ­ã´£³¡ªL


ª©¥D§A¦n, ¯uªºÁÂÁ§AªºÀ°¦£
§Úª¾¹D§A¤@©w¬O´ú¸Õ¦¨¥\¤~·|©ñ¤W¨Óµ¹§Ú°Ñ¦Ò
¦ý¤p§Ì¤£¤~, §Ú¨Ì¾Ú§Aµ{¦¡­×§ï¤F, ¦ý³£¬O¨ÌµM¤£¦¨¥\, §Ú¤£ª¾¹D¿ù¦b¨º¨½,
¥¦©l²×³£¬OÅã¥Ü§ä¤£¨ì¬Û¹ïªº¤é¤l:'(
Àµ½Ð³Â·Ð§AÀ°¦£«ü¥Xªþ¥ó¤¤ªº¿ù³B:handshake

¼Ë¿ì.rar (127.4 KB)

TOP

        ÀR«ä¦Û¦b : ¸Ü¦h¤£¦p¸Ü¤Ö¡A¸Ü¤Ö¤£¦p¸Ü¦n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD