LinkBack URL
About LinkBacks
I don't understand the formula (especially IF part) on D7. It returns the date (3rd monday on January) which was explained on C7.
I don't understand the formula (especially IF part) on D7. It returns the date (3rd monday on January) which was explained on C7.
You're using Excel 2010, so you should take advantage of the WORKDAY.INTL function
This formula returns the 3rd monday of the referenced month:
In that formula, in this section: "0111111", 1's flag which days should be ignored (starting with Monday). So "0111111" indicates that every day except Monday should be ignored.
Please Login or Register to view this content.
Is that something you can work with?
Formula:
Please Login or Register to view this content.
1) Why is there "-1"?
2) Why is there "3"?
3) What does "0111111" mean?
4) I also want to understand the logic of the formula in the file.
Last edited by zanshin777; 12-14-2015 at 09:24 AM.
If you search the internet for help on that function you'll find the documentation.Originally Posted by zanshin777
Formula:Please Login or Register to view this content.
1) Why is there "-1"?
If the first day of the month is a Monday, it must be counted. Consequently, we start with the last day of the previous month and count from there.
2) Why is there "3"?
3 is the number of "workdays" to increment the base date. Since we are only counting Mondays as workdays, the formula returns the 3rd Monday.
3) What does "0111111" mean?
As I mentioned in my original post, "0111111" represents the status of each day of the week, beginning with Monday. 1's indicate weekends. 0's indicate workdays.
4) I also want to understand the logic of the formula in the file.
1) WORKDAY.INTL function was understood but it give NAME error?
Formula:Please Login or Register to view this content.
2) How about the formula in the file?
Last edited by zanshin777; 12-15-2015 at 08:06 AM.
1) Your formula has flaws.
You should use one of these formulas:
orPlease Login or Register to view this content.
and format that cell to display the date in MM/DD/YYYY formatPlease Login or Register to view this content.
Does that help?
The results of the book version formula and the you version of formula are different. February 15 and February 18.
Look at the F8 and D8. Why?
Last edited by zanshin777; 12-17-2015 at 08:05 AM.
In 2010, the 3rd Monday in February is the 15th. And that's what the formula I posted returns.
I don't understand the first argument of "WORKDAY.INTL" formulas. Why do you substract "1"?
The formula returns the 3rd Monday AFTER the start date.
If the first of the month is a Monday, you need to count it. Consequently, we need to start from the day before the first of the month.
Examples:
June 01, 2015 is a Monday.
This formula will return June 08, 2015 (the first Monday AFTER the start date):
This formula will return June 01, 2015:Please Login or Register to view this content.
Does that help?Please Login or Register to view this content.
I think this formula is not useful much. I can't use it copy paste for every date. (or Auto Complete)
Because I have to know -before writing the formula- if the start date is monday or not.
Why do I use then an Excel formula? Looking calender is easier.
Last edited by zanshin777; 12-29-2015 at 12:19 PM.
You asked why I subtracted 1 from the start date. I posted an explanation and an example.
That formula was never intended to solve your initial question.
The other formulas I posted do solve the problem from your first post.
Ron's formula does solve the problem is is much more versatile (easily changed to other days of the week or other counts (2nd Monday, 4th Monday for example). I am not sure why you think it would require looking at a calendar?
The Original Formula does not work:
First part is obvious, returns January 1, 20xx. Plus...
The Logic test in the IF statement
is looking to see if the first of the month is on Sunday (<2) or not
If that's true then
7-WEEKDAY(DATE(A1,1,1))+2 effectively adds 8 to the date which we've already determined is a Sunday (7-1+2) which brings us to the second Monday of the month.
If that's false then
2-WEEKDAY(DATE(A1,1,1))
returns a number between 0 and negative 5 which, if it's 0 puts us at the first monday, if it's < 0 then it puts us at the last monday of the preceding month.
+(3-1)*7
adds 14 days so if
January 1st falls on a Sunday, this formula will return the 4th Monday
if Jan 1 falls on Monday, it returns the 3rd Monday
If Jan 1 false on Tues-Saturday, it returns the 2nd Monday.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
What about?
Formula:Please Login or Register to view this content.
Last edited by zanshin777; 12-29-2015 at 12:32 PM.
If you want to create a more complicated formula when a simpler one will work. Go right ahead.
I prefer to spend my time making things easier for people, not more difficult.
I don't understand the confusion with Ron's formula.
It's quite clever and works very well.
Why the -1?
I think you need to first understand what the basic workday function does (disregard the .intl for now)
Workday adds x number of working days to a given date (the beginning date is NOT included)
So say the beginning date is a Monday, then Monday + 3 workdays is Thursday (Mon+1=Tue, Mon+2=Wed, Mon+3=Thu)
So the initial monday would get skipped. That's why the -1 from the begin date.
Now the "0111111" part is what is special about workday.INTL
The normal workday function considers Saturday and Sunday as the weekends.
But you can specify ANY combination of dates to be the 'Weekend' in workday.INTL
That string is a string of 7 1's or 0's.
7 numbers representing each day of the week beginning with Monday.
1 = weekend day, 0 = working day
so 01111111 indicates Monday as the only working day.
If you did 1101011, that would have Wednesday and Friday as your working days, and Mon Tue Thu Sat Sun as the weekend days.
DATE($A$1,1,1) = 2 means that the date is set to 1/2/1900, not likely. I'm not sure where this formula came from?What about =WORKDAY.INTL(IF(DATE($A$1,1,1)=2, DATE($A$1,1,1)-1, DATE($A$1,1,1)), 3,"0111111")
Nevermind, misread
I tried to create a formula which if first day of month is monday make "-1" if not go regular.
Formula:Please Login or Register to view this content.
Why?
It's not necessary.
Here's your sample book with Ron's formula shown in all the nth weekday cells (D7 D8 D10 D12 D13 D15)
nthweekdayofmonth.xlsx
Let say if you would use this formula with Auto Complete.
It doesn't actually matter if the start date is actually a monday or not.
The -1 makes the workday function account for that possibility either way.
You don't need to 'Remove' the -1 if the date is NOT actually a Monday.
Thank you very much
You're welcome.
Credit to Ron Coderre, he posted it.
What does that "-7" mean on D9?
You're asking for the 'Last' Monday in May.
That's very ambiguous because you can't specify a number like the 4th or 5th.
So instead the formula finds the First Monday of June, then subtracts 7 to become the last Monday of May.
Why does "-7" mean "days" not "months" or "years"?
Why would it be months or years ?
The -7 is NOT a part of the networkdays function.
So the -7 acts the same as it would in a function like say:
=TODAY()-7
What does the -7 do there?
Same thing it does in
NETWORKDAYS(...)-7
Say A1 = 2015
=WORKDAY.INTL(DATE($A$1,6,1)-1,1,"0111111")-7
Networkdays works out the date as the 1st Monday of June 2015, which is 6/1/2015 (or 1/6/2015 if you're using dd/mm format)
=6/1/2015-7
=5/25/2015
When working with time (dates and time), Excel uses Days as the unit of measure. So if you have
January 12, 2016 8:45 PM and subtract 2, you'll get January 10, 2016 8:45 PM
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks