Hi,
I am trying to display a range of business days in excel. I would like to exclude weekends and our company holidays but am unsure of how to write the formula. For example, I would like a cell to read:
A1: 1/5/15 - 1/9/15
A2: 1/12/15 - 1/16/15
Any ideas?
in what way though? whats your starting point? are these dates elsewhere in the sheet or are they to be generated?
currently your question is the equivalent of "Im feeling ill, please cure me"
If someone has helped you then please add to their Reputation
Starting point is 12/31/14. I have a list of the dates, excluding holidays and workdays, in another column. Does that help clarify? Any other information that is needed?
Not really.....
Your starting point is 31st December 2014,
what decides which range goes into each cell?
how many cells are there?
whats the end date?
Are these weeks? ie a cell per week? and when you say you dont want to include holidays do you mean that if the Monday is a holiday then it would start on the Tuesday? ie imagine 5th jan is a holiday, would your first example be 01/06/15 - 01/09/15?
* I really cant get to grips with how Americans write dates
It would be very helpful if you could upload your spreadsheet or at least a good example of it, including both the list of dates/holidays and an example of what you want to see, I *think* this should be straightforward but want to be sure of the requirements first
So the formula works from g15 to g16 because it is adding to the correct date. As I drop the formula down, the formula adds 3 and 7 to d13 to generate the date.
The formula needs to be set up so it starts counting from the second date displayed in the range, not from the next row down.
Any thoughts? Thanks for your help!!
Got it working using this formula:
=MONTH(B2)&"/"&DAY(B2)&"/"&YEAR(B2)&" - "&MONTH(C2)&"/"&DAY(C2)&"/"&YEAR(C2)
I dont think its possible with a function however it is with VBA.
Add the following Sub to ThisWorkbook and run it, the results will be displayed in column L.
Please Login or Register to view this content.
Im confused? your example doesnt even have any values in B & C? plus that formula is just displaying the values in B2 and C2 in a set format?Originally Posted by carobwell
I am guessing he was testing elsewhere and ended up supplying that workbook. I added "Week: " in the output (Only because his example looked like he wants it to be in that final format) and removed the extra space from the second date or end of week date, other than that nice clean, simple method! I like it
Please Login or Register to view this content.
-If you think you are done, Start over - ELeGault
You could also do that with TEXT function like this
=TEXT(B2,"m/d/yyyy")&" - "&TEXT(C2,"m/d/yyyy")
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks