Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

End of week from trading dates

Posted on 2014-10-29
15
Medium Priority
?
131 Views
Last Modified: 2014-10-30
Hi experts,

Here is a task in Excel. In column A, there is a list of trading days. We want to pick out the last day each week in the list and put those days in Column B. For a simple example, in the following list. They are trading days in January 2007.

1/3/2007
1/4/2007
1/5/2007
1/8/2007
1/9/2007
1/10/2007
1/11/2007
1/12/2007
1/16/2007
1/17/2007
1/18/2007
1/19/2007
1/22/2007
1/23/2007
1/24/2007
1/25/2007
1/26/2007
1/29/2007
1/30/2007
1/31/2007


And we want column B as:
1/5/2007
1/12/2007
1/19/2007
1/26/2007

The real data is much larger than one month. Is there a easy way to do it in formula?
0
Comment
Question by:ResourcefulDB
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40411479
here you go put this =A1+7-(WEEKDAY(A1)+1)
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40411484
see it in attached file
End-of-week-from-Date.xlsx
0
 
LVL 3

Expert Comment

by:byronwall
ID: 40411501
You have a handful of options depending on how you want to process things and depending on what you mean by "end of week".

If "end of week" means Friday like the examples above, you can get this information from one of two formulas:

=TEXT(A1, "ddd") will return the three character shorthand for the day (e.g. "Fri")
=MOD(A1, 7) will return the remainder when dividing by 7 (e.g. 6, for Friday)

Option 1 is easier to understand; Option 2 is generally faster.

Either of those formulas could be copied down column B and used to filter for the Friday value.  If you just want the matching results, you can run a quick VBA through column A using either formula above to print results.  If you need updating formulas in column B which only return matching results, it can be done but gets a little fancy.  That formula would depend on ranges and be an array formula, but look something like this using the MOD approach above.

=SMALL(IF(MOD(A1:A27,7)=6, A1:A27, ""), ROW()-ROW($A$1)+1)

If end of week means the "last day of this week of the dates provided", that can be done, but it gets even fancier.

Reply if those formulas are what you need and what final form is acceptable.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40411682
ResourcefulDB

my formula does exactly as you have asked. please let me know.
0
 

Author Comment

by:ResourcefulDB
ID: 40412141
The Friday may be a holiday, such as July 4th, or January 1st. So, Friday may not always be the last trading day of the week.

In addition, in Column B, we just want the dates that are the end of week dates. A end of week date for each date from Column A is not what we wanted.

For example, in the January 2007 list, we just want 4 dates in column B.

Thanks for trying.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40412190
please read your question again.  you did not mention holidays

now this is a new requirement you are asking for.

based on your original question the provided formula  =A1+7-(WEEKDAY(A1)+1)   does produce the result you have asked.
0
 
LVL 3

Expert Comment

by:byronwall
ID: 40412218
If you can live with helper columns, this can be done the way you want.  The date is stored as a number.  When this number is divided by 7 and floored, you now have a "week number".  The idea then is to find the greatest dates present for each week present.

See the attached file for a solution.  This would probably be done much cleaner with VBA if that is acceptable.  If not, I think you are stuck with some helper columns.  At least, that's the best I can do.  You can always copy/paste values and delete the helper columns if that's OK.

Note that I have named the input dates "dates" and my helper column with week numbers "weeks".  This cleans up the formulas a bit.

Hopefully you can see how to modify this for your dates.  Note this assumes that "offending" dates will not be present in the input.  If they are there, then this really cannot be done.
Last-day-of-week-sample.xlsx
0
 

Author Comment

by:ResourcefulDB
ID: 40412415
@byronwall

Thank you. there is no problem of using helper column. I like your idea of getting the "week number" in column C and D. I wonder if Column D can handle the missing week, such as the following trading dates.

9/4/2001
9/5/2001
9/6/2001
9/7/2001
9/10/2001
9/17/2001
9/18/2001
9/19/2001
9/20/2001
9/21/2001
9/24/2001
9/25/2001
9/26/2001
9/27/2001
9/28/2001

Once we have the week number, then we can get the Friday of that week. Then arrange the original trading date in the ascending order and use vlookup to look for the date closest to the Friday of that week. It should be handle something like the following sequence.

7/1/2008
7/2/2008
7/3/2008
7/7/2008
7/8/2008
7/9/2008
7/10/2008
7/11/2008
7/14/2008
7/15/2008
7/16/2008
7/17/2008
7/18/2008
7/21/2008
7/22/2008
7/23/2008
7/24/2008
7/25/2008
7/28/2008
7/29/2008
7/30/2008
7/31/2008


See that July 4th was a Friday, but was not a trading date. But if we use vlookup to look for all the dates in this list that is approximate to 7/4/2008, it will give us 7/3/2008 and it is the last trading date of that week. Wanna go for it?

Thanks.
0
 
LVL 3

Accepted Solution

by:
byronwall earned 2000 total points
ID: 40412454
It should already work as expected without having to do additional VLOOKUPs.  When I paste in the July dates you give, the spreadsheet outputs:

7/3/2008
7/11/2008
7/18/2008
7/25/2008
7/31/2008

in column B.  I believe this is what you are expecting for output?  For the September dates, it spits out:

9/7/2001
9/10/2001
9/21/2001
9/28/2001

which also appears correct.

The main thing now is expanding the formulas so that they cover the full range of inputs that you are expecting.  If you paste in the full range of dates for column A, you can then:
 * fill columns B, C, and D down to the end of the data (B and D technically only need to go as far as the number of outputs, but you may as well drag them all the way down)
 * update the named ranges "dates" and "weeks" to end at the correct row.

If you can make those changes, you are good to go.  If not, tell me how many inputs you have, and I will update it.  Or, alternatively, I can update the spreadsheet for 1000 or so inputs, and you can batch them in.
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40413106
Expanding on ByronWall's suggestion, using CEILING or FLOOR can round a date up or down to the last day of the week.

For example:
=CEILING(TODAY(),7)   gives 1 Nov 2014.

=FLOOR(TODAY(),7)     gives 25 Oct 2014

If you want to adjust to the Friday then just put -1 on the end.

The syntax for CEILING and FLOOR are:

=CEILING(Number,Factor)

Number - any number
Factor - the multiple to which you want to round.

With dates being stored as numbers and day 1 of the Excel calendar being 1 Jan 1900 which was a Sunday, every Saturday thereafter is a multiple of 7 so rounding up or down to a factor of 7 gives the Saturday date.

Combining this with a MATCH could then check for holidays and deducting a further day to return the Thursday if required.

=CEILING(TODAY(),7)-IF(ISERROR(MATCH(TODAY(),Holidays,0)),1,2)    Holidays = list of holiday dates

Thanks
Rob H
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40413121
Looking at it again, putting this in column B against all dates would give a list of repeated dates which I don't believe you want. Therefore:

First entry in column B:

=CEILING(MIN(A:A),7)-IF(ISERROR(MATCH(MIN(A:A),Holidays,0)),1,2)    Holidays = list of holiday dates

Further entries in column B, if only one month at a time the most you will need is 5 in total; 1 as above and 4 of the following:

=IF(MONTH(B1+7)<>MONTH(B1),"",B1+7)

This will need tweaking to allow for holidays as well so I will take a look at that.

Thanks
Rob H
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40413133
Slight tweak:

=CEILING(MIN(A:A),7)-IF(ISERROR(MATCH(CEILING(MIN(A:A),7)-1,Holidays,0)),1,2)

Thanks
Rob H
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40413176
Tweaked first date calculation to allow for first of month being a Saturday:

=IF(WEEKDAY(MIN(A:A))=7,MIN(A:A)+6,CEILING(MIN(A:A),7)-IF(ISERROR(MATCH(CEILING(MIN(A:A),7)-1,Holidays,0)),1,2))

Tweaked subsequent date calculations:

=IF(MONTH(IF(ISERROR(MATCH(B1+7,Holidays,0)),CEILING(B1+7,7)-1,CEILING(B1+7,7)-2))>MONTH(B1),"",IF(ISERROR(MATCH(B1+7,Holidays,0)),CEILING(B1+7,7)-1,CEILING(B1+7,7)-2))

See attached, I have deliberately used April as Good Friday was a Holiday.

Thanks
Rob H
Friday-Dates.xlsx
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40413190
Further tweak to first date calculation; in case first of month is Saturday AND following Friday is holiday:

=IF(WEEKDAY(MIN(A:A))=7,MIN(A:A)+IF(ISERROR(MATCH(MIN(A:A)+6,Holidays,0)),6,5),CEILING(MIN(A:A),7)-IF(ISERROR(MATCH(CEILING(MIN(A:A),7)-1,Holidays,0)),1,2))

Thanks
Rob H
0
 

Author Comment

by:ResourcefulDB
ID: 40413392
@byronwall,

Yes, I tested out your formula and it did give out nice results.

I notice that you use array formula in Column B, and find out the max of of all dates for the given week. that is very clever. it can be better than vlookup in this way

Thank you.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question