Hi All,
How can I pull dates in ColumnA from 15/02/2017 to 20/5/2017 excluding Fridays and Saturdays dates...
Thank you
Hi All,
How can I pull dates in ColumnA from 15/02/2017 to 20/5/2017 excluding Fridays and Saturdays dates...
Thank you
Assume your data is in column A. In B = weekday(A1) and copy down. Then using the built in Filter, exclude 6 and 7 from the criteria.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
hi there. another way is to use an array formula.
Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
that's not I wanted.. e.g. If I type date 15/2/2017 and drag it down till I reach 20/5/2017. I want all dates excluding the dates of Fridays and Saturdays... there is builtin auto fill option and works good but that excludes Saturdays and Sundays.. I want the exact thing but want to exclude Fridays and Saturdays..
Assume you have B2 having start date and B3 having end date try
A2Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Row\Col A B C D 2 15.02.2017 15.02.2017StartDate Wed 3 16.02.2017 20.05.2017EndDate Thu 4 19.02.2017Sun 5 20.02.2017Mon 6 21.02.2017Tue 7 22.02.2017Wed 8 23.02.2017Thu 9 26.02.2017Sun 10 27.02.2017Mon 11 28.02.2017Tue 12 01.03.2017Wed 13 02.03.2017Thu
Last edited by shukla.ankur281190; 11-20-2017 at 01:26 AM.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
O Thank you!! That's what I wanted.. Is there a way to edit builtin Auto fill option and replace Fridays and Saturdays..?
Which post helps you? and whom you are addressing ?
Mr. shukla.ankur281190 post helps me.. and I am addressing the same guy.. Thank you so much for the help.!!
For date may be it can not possible but You can create custom list for creating custom days list
Press Alt-A-S-S-Order drop down->choose custom list->put new custom list for arranging days months or any kind of information as you required.
Try putting first date in A2 and then this formula in A3 dragged down
=WORKDAY(A2+1,1)-1
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks