June 25, 2016

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,

Dashboards

Power BI

Power Query

April 21, 2015

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

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

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

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

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

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

Dashboards

Power BI

Power Query

April 21, 2015

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

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

Dashboards

Power BI

Power Query

April 21, 2015

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

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 😀 )

Dashboards

Power BI

Power Query

April 21, 2015

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!

Most Users Ever Online: 57

Currently Online: Matthias

1 Guest(s)

Currently Browsing this Page:

1 Guest(s)

Top Posters:

SunnyKow: 651

Frans Visser: 210

David_Ng: 96

mey tithveasna: 71

A.Maurizio: 60

rathanak: 58

yhooithin05: 54

Anders Sehlstedt: 47

julian: 46

PaulFogel: 37

Newest Members:

Michael Bruce

R vd Bosch

Shawn Geiger

Naser Aljed

Komal Limbu

Traicey Dwyer

Jan-Eric Borén

Rao Sharafat Ali

Abhishek Nar

ruben pillay

Forum Stats:

Groups: 2

Forums: 18

Topics: 935

Posts: 4405

Member Stats:

Guest Posters: 1

Members: 42350

Moderators: 1

Admins: 3

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea

Moderators: Genevieve Tupas