Skip i'r prif gynnwys

Sut i dynnu pob cofnod rhwng dau ddyddiad yn Excel?

Dyma ystod o ddata yn Excel, ac yn yr achos hwn, rwyf am dynnu pob cofnod rhes rhwng dau ddyddiad fel y dangosir isod y llun, a oes gennych chi unrhyw syniadau i drin y swydd hon yn gyflym heb chwilio am ddata a'u tynnu fesul un â llaw?

cofnod dyfyniad doc rhwng dyddiadau 1 saeth saethu i'r dde cofnod dyfyniad doc rhwng dyddiadau 2

Tynnwch yr holl gofnodion rhwng dau ddyddiad yn ôl fformwlâu

Tynnwch yr holl gofnodion rhwng dau ddyddiad gan Kutools ar gyfer Excelsyniad da3

Tynnwch yr holl gofnodion rhwng dau ddyddiad yn ôl fformwlâu

I dynnu pob cofnod rhwng dau ddyddiad yn Excel, mae angen i chi wneud fel y rhain:

1. Creu taflen newydd, Sheet2, a theipio'r dyddiad cychwyn a'r dyddiad gorffen mewn dwy gell, er enghraifft, A1 a B1. Gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 3

2. Yn y C1 yn Nhaflen 2, teipiwch y fformiwla hon, =SUMPRODUCT((Sheet1!$A$2:$A$22>=A2)*(Sheet1!$A$2:$A$22<=B2)), y wasg Rhowch allwedd i gyfrif cyfanswm nifer y rhesi paru. Gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 4

Nodyn: yn y fformiwla, Sheet1 yw'r ddalen sy'n cynnwys y data gwreiddiol rydych chi am dynnu ohono, $ A $ 2: $ A $ 22 yw'r ystod o ddata, A2 a B2 yw'r dyddiad cychwyn a'r dyddiad gorffen.

3. Dewiswch gell wag y byddwch chi'n gosod y data sydd wedi'i hechdynnu, teipiwch y fformiwla hon =IF(ROWS(A$5:A5)>$C$2,"",INDEX(Sheet1!A$2:A$22,SMALL(IF((Sheet1!$A$2:$A$22>=$A$2)*(Sheet1!$A$2:$A$22<=$B$2),ROW(Sheet1!A$2:A$22)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5)))), y wasg Shift + Ctrl + Enter allweddi, a llusgo handlen llenwi Auto dros golofnau a rhesi i echdynnu'r holl ddata nes bod celloedd gwag neu werthoedd sero yn ymddangos. Gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 5

4. Tynnwch y seroau, a dewiswch y dyddiadau sy'n dangos fel rhifau 5 digid, ewch i Hafan tab, a dewis Dyddiad Byr yn y gwymplen Gyffredinol i'w fformatio wrth fformatio dyddiad. Gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 6

Tynnwch yr holl gofnodion rhwng dau ddyddiad gan Kutools ar gyfer Excel

Os ydych chi am drin y swydd hon yn haws, gallwch chi gael cynnig ar y Dewiswch Celloedd Penodol nodwedd o Kutools ar gyfer Excel.

Kutools ar gyfer Excel, gyda mwy na 300 swyddogaethau defnyddiol, yn gwneud eich swyddi yn haws. 
Lawrlwythiad Am Ddim
am ddim llawn-ymddangos mewn 30 diwrnod

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel isod:（Lawrlwythiad Am Ddim Kutools ar gyfer Excel Nawr!)

1. Dewiswch y data rydych chi am dynnu ohono, cliciwch Kutools > dewiswch > Dewiswch Gelloedd Penodol. Gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 7

2. Yn y Dewiswch Gelloedd Penodol deialog, gwirio Rhes gyfan dewis, a dewis Yn fwy na ac Llai na o'r gwymplenni, teipiwch y dyddiad cychwyn a'r dyddiad gorffen yn y blychau testun, cofiwch wirio Ac. Gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 8

3. Cliciwch Ok > OK. Ac mae'r rhesi sy'n cyfateb i'r dyddiadau wedi'u dewis. Gwasg Ctrl + C i gopïo'r rhesi, a dewis cell wag a'i phwyso Ctrl + V i'w gludo, gweler y screenshot:
cofnod dyfyniad doc rhwng dyddiadau 9

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (19)
No ratings yet. Be the first to rate!
Megan
Megan
This comment was minimized by the moderator on the site
Hi, this formula worked ok, except it only returned one data set for each date in the range. Is there an adjustment to allow for multiple entries with the dame date value?
Reply
0
0
Christiaan82
Christiaan82
This comment was minimized by the moderator on the site
This formula works awesome thanks, I need some extra help though. I have 200 employees, each with certification that expires on different dates. The heading for each certification is at the top with their expiry dates below. I want to extract the rows that with all info if there is an expiry reached and throw them into a new sheet. Exactly like the example above just dated from multiple columns.
Reply
Report
0
0
Sunny
Sunny     Christiaan82
This comment was minimized by the moderator on the site
Hi, Christiaan82, could you upload the a screenshot about you workhsheet, or give me more details about the information of sheet format, only with your description, I do not get it clearly.
Reply
Report
0
0
Christiaan82
Christiaan82     Sunny
This comment was minimized by the moderator on the site
All sorted. Instead of trying to retrieve the rows from multiple columns, I added a new hidden sheet where all the info populates the info on top of each other. So the information only needs to be retrieved from one column, as per your example above.
Thank you Sunny
Reply
Report
0
0
Neha
Neha
This comment was minimized by the moderator on the site
"drag Auto fill handle over columns and rows to extract all data until blank cells or zero values appear."
This doesn't seem to be working for me. The first row gives me the dates but the same gets repeated in the next two rows.
Reply
0
0
Sunny
Sunny     Neha
This comment was minimized by the moderator on the site
Hi, Neha, could you check if your auto calculate is turn on? Click Formulas > Calcuations Options > Automatic.
Reply
Report
0
0
Muneera
Muneera
This comment was minimized by the moderator on the site
it worked well for single sheet. can u pls help me to get the same from multiple sheets of similar data. first 4 sheets contain similar data. i need all records of same 'date' in sheet5.
Reply
0
0
Tim
Tim
This comment was minimized by the moderator on the site
Can you extend the formula to check for dates that encompass more than one day? For example, using the same start and end dates from 1/1/15-12/31/16, but the data in sheet 1 have start and end dates too rather than just existing for a singular date.
Reply
0
0
Angry Savage
Angry Savage
This comment was minimized by the moderator on the site
This formula worked brilliantly. I was concerned as I have multiple date duplicates and there was no mention of how it handles those; no issues. It returned all relevant data.
Stoked.
Reply
0
0
Sunny
Sunny     Angry Savage
This comment was minimized by the moderator on the site
Thanks for your support, you can combined the duplicate dates cells after extracting.
Reply
Report
0
0
Math
Math
This comment was minimized by the moderator on the site
The trouble I am having because I need to find dates that run through the date range specified. So I want anything that runs through something like 1/2016 to 12/2017, however it needs to include start dates that may be before the range so I can identify the records I need to look at.
Reply
0
0
Kayla
Kayla
This comment was minimized by the moderator on the site
I used this code and it is pulling up dates outside of the window... how do I fix it?
Reply
0
0
Sunny
Sunny     Kayla
This comment was minimized by the moderator on the site
Which code Kayla? Do you mean the formula？
Reply
Report
0
0
Michelle
Michelle
This comment was minimized by the moderator on the site
I recreated this example in Excel 2010, and the result was that the first row of data would show up, but everything underneath had a number error.
Reply
0
0
Brian
Brian     Michelle
This comment was minimized by the moderator on the site
Same thing happens to me n Excel 2010. Something in the formula doesn't work correctly.
Reply
0
0
Sunny
Sunny     Michelle
This comment was minimized by the moderator on the site
Have you pressed Shift + Ctrl + Enter keys?
Reply
Report
0
0
brian
brian     Sunny
This comment was minimized by the moderator on the site
I see what's wrong. You have to press the shift + CTRL + Enter keys while still in edit mode.
I was hitting those keys after entering the formula and hitting ENTER.
I wish it would have been clearer.
Reply
0
0
Sunny
Sunny     brian
This comment was minimized by the moderator on the site
Yes, brian, after typing the formula and still in edit mode, directly press Shift + Ctrl + Enter keys (the keys instead of Enter key), to get the correct result.
Reply
Report
0
0
Jo Higgins
Jo Higgins     Sunny
This comment was minimized by the moderator on the site
Still doesnt work for me? I am doing exactly as above and still returning the NUM error? Any other ideas?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations