Log In
Register
Home
June 25, 2016
Offline
Dear Guru,
Assume in cell A1 i have date 01/01/2016 i want to find friday after this date?how can i?
hope you all can help
Regards,
See Answer
November 8, 2013
Offline
Hi Rathanak,
You can try one of these:
Or:
Both formulas are returning the date of the next Friday, depending on cell A1.
October 9, 2014
Offline
=A1+8-WEEKDAY(A1+2)
Cheers,
Jerry
Answers Post
VIP
April 21, 2015
Offline
OK my first feedback on this amazing Forum. Thanks for starting it!
I think it helps everybody (but at least me) if there is a little explanation given with the solution. In that way we all learn not only about the question but also about the answer and the thinking behind the answer.
For instance the first two solutions went far over my head. I can't follow why this should give the asked solution. And what's even worse: I tried this solutions but couldn't get the right answers with it. Maybe I wrote something not correct but my Excel doesn't give an error.
The last (most easy looking solution) gives in my tests always the correct solution where was asked for. But also in that case it's difficult (for me) to find the reason how come.
Hope you see this as it is meant to be: positive feedback and thinking!
Frans
June 25, 2016
Offline
I
Catalin Bombea said
Hi Rathanak,You can try one of these:
=AGGREGATE(15,3,ROW($A$10000:$A$60000)/((ROW($A$10000:$A$60000)>=A1)*(WEEKDAY(ROW($A$10000:$A$60000))=5)),1)Or:
=A1+5-WEEKDAY(A1)+IF(WEEKDAY(A1)>5,7,0)Both formulas are returning the date of the next Friday, depending on cell A1.
Thanks could u elaborate me for above formula
Regards,
July 16, 2010
Offline
Hi Rath,
What do you mean by 'elaborate'? Is the formula not working?
You can learn more about the AGGREGATE function here and the WEEKDAY Function here and the ROW function here.
For tips on understanding and deciphering formulas please watch this video. The tips in here will be invaluable in helping you understand and debug formulas going forward.
Mynda
June 25, 2016
Offline
Mynda Treacy said
Hi Rath,What do you mean by 'elaborate'? Is the formula not working?
You can learn more about the AGGREGATE function here and the WEEKDAY Function here and the ROW function here.
For tips on understanding and deciphering formulas please watch this video. The tips in here will be invaluable in helping you understand and debug formulas going forward.
Mynda
Hi Dear
It is not working
Regards,
November 8, 2013
Offline
Hi Frans, welcome to our forum 🙂
Sorry I puzzled you. I admit I wrote the first one just for fun 🙂
First, make sure you are using Excel 2010+, the AGGREGATE function is not available in lower versions of excel.
@Rathanak and Frans:
You know that a date is a whole number, for each day we add 1. For example, 01/01/2016 corresponds to 42370 (there are 42370 days passed since 01/01/1900, when the excel days count starts)
Each Date function expects a whole number, from this point of view, row numbers and days are the same: both are increasing by a step of 1, and they are both whole numbers. A date, even if it can be formatted and displayed in many ways will always be a number. Knowing this, we can use row numbers as days to perform complex calculations:
WEEKDAY(ROW($A$10000:$A$60000))
In this formula, Row 10000 corresponds to 18/05/1927, and row 60000 with 07/04/2064. Aggregate function will simply take the smallest row number that meets the criterias we need: the date is higher than the date in cell A1 and weekday number is 5. That row number is the date we need.
The approach is very useful in many cases: for example, we can find this way the number of leap years between 2 dates:
=SUMPRODUCT((DAY(ROW($A$10000:$A$60000))=29)*(MONTH(ROW($A$10000:$A$60000))=2)*1)
The criterias should be: day number =29, and Month number=2. It will return 35, which is the number of leap years between 18/05/1927 (row number 10000) and 07/04/2064 (row number 60000)
Jerry's solution is the simplest one, and it's another confirmation for the fact that there are many ways to get to the same result.
June 25, 2016
Offline
U
Catalin Bombea said
Hi Frans, welcome to our forum 🙂Sorry I puzzled you. I admit I wrote the first one just for fun 🙂
First, make sure you are using Excel 2010+, the AGGREGATE function is not available in lower versions of excel.
@Rathanak and Frans:
You know that a date is a whole number, for each day we add 1. For example, 01/01/2016 corresponds to 42370 (there are 42370 days passed since 01/01/1900, when the excel days count starts)
Each Date function expects a whole number, from this point of view, row numbers and days are the same: both are increasing by a step of 1, and they are both whole numbers. A date, even if it can be formatted and displayed in many ways will always be a number. Knowing this, we can use row numbers as days to perform complex calculations:
WEEKDAY(ROW($A$10000:$A$60000))
In this formula, Row 10000 corresponds to 18/05/1927, and row 60000 with 07/04/2064. Aggregate function will simply take the smallest row number that meets the criterias we need: the date is higher than the date in cell A1 and weekday number is 5. That row number is the date we need.
The approach is very useful in many cases: for example, we can find this way the number of leap years between 2 dates:
=SUMPRODUCT((DAY(ROW($A$10000:$A$60000))=29)*(MONTH(ROW($A$10000:$A$60000))=2)*1)
The criterias should be: day number =29, and Month number=2. It will return 35, which is the number of leap years between 18/05/1927 (row number 10000) and 07/04/2064 (row number 60000)
Jerry's solution is the simplest one, and it's another confirmation for the fact that there are many ways to get to the same result.
Good explaination
Thanks dear.
L.E.:
I got it.thank every one here now i have got ideas to resolve this query
VIP
April 21, 2015
Offline
Thanks Catalin for your reaction and explanation! This works very well and could be the 'standard' of the Forum!
I found where I went wrong: I have to type in the formula because I work with the Dutch language version of Excel (2013, so no problem with the Aggregate function) and made some mistakes with that (it's always tricky in that complex formula's with the '(' and ')' and counting and closing them).
Both your first and second solution now give Thursday 7th January 2016 as solution and not the Friday.
Can it be this has something to do with the settings of the first day of the week in my version or so?
The solution Jerry gave gives the right date, so that's confusing for me.
Side question: is there a way to copy and paste the English formulas in the Dutch version? When I download a file from your website, it automatically will be 'translated' so somewhere in the behinds of Excel there must be a possibility?
November 8, 2013
Offline
Hi Frans,
The only way that excel will translate function names is when you open an excel file that was created in another language. All you have to do is to replace the function names, don't delete or add other paranthesis.
I already added few days ago 1 to the end of the formulas to compensate that, the result was indeed 1 day before Friday.
Cheers,
Catalin
VIP
April 21, 2015
Offline
thanks again Catalin. I did already had the '1', but now I see you changed the '>=A1' into '>=A1'.
Maybe that > is the code for > but I seem to translate that as well and can't find that solution 🙂
Anyway: I still have the Thursday and must look again if I misspelled something I overlooked.
Here's what I have:
| =AGGREGAAT(15;3;RIJ($A$10000:$A$60000)/((RIJ($A$10000:$A$60000)>=A1)*(WEEKDAG(RIJ($A$10000:$A$60000))=5));1) |
November 8, 2013
Offline
You're right, that > is the code for > , wordpress is converting them automatically, and this is annoying sometimes.
You don't seem to have the final +1 at the end of the formula: (hope my dutch is correct 😀 )
VIP
April 21, 2015
Offline
Yeah, that's it.
I misinterpreted your sentence "I already added few days ago 1 to the end of the formulas to compensate that, the result was indeed 1 day before Friday."
I thought the 1 at the end was that one......
But it isn't in the solution above I think?
Now we know how to do it. Thanks again!
1 Guest(s)
