Friday | General Excel Questions & Answers | Excel Forum • My Online Training Hub

Friday

rathanak

Member

Members

Forum Posts: 58

Member Since:
June 25, 2016

Offline

July 7, 2016 - 2:27 am

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 7, 2016 - 7:28 am

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)+1

Or:

=A1+5-WEEKDAY(A1)+IF(WEEKDAY(A1)>5,7,0)+1

Both formulas are returning the date of the next Friday, depending on cell A1.

Jerry Cook

Fairfax, VA, USA

Member

Members

Forum Posts: 3

Member Since:
October 9, 2014

Offline

July 7, 2016 - 7:29 am

=A1+8-WEEKDAY(A1+2)

Cheers,

Jerry

Answers Post

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 221

Member Since:
April 21, 2015

Offline

July 7, 2016 - 8:28 am

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

rathanak

Member

Members

Forum Posts: 58

Member Since:
June 25, 2016

Offline

July 7, 2016 - 9:21 am

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,

Mynda Treacy

Admin

Forum Posts: 723

Member Since:
July 16, 2010

Offline

July 7, 2016 - 10:12 am

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

rathanak

Member

Members

Forum Posts: 58

Member Since:
June 25, 2016

Offline

July 7, 2016 - 12:53 pm

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,

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 7, 2016 - 1:11 pm

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.

rathanak

Member

Members

Forum Posts: 58

Member Since:
June 25, 2016

Offline

July 7, 2016 - 1:55 pm

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

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 221

Member Since:
April 21, 2015

Offline

July 8, 2016 - 7:09 pm

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?

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 8, 2016 - 8:12 pm

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

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 221

Member Since:
April 21, 2015

Offline

July 8, 2016 - 8:59 pm

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)

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 8, 2016 - 10:08 pm

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

=AGGREGAAT(15;3;RIJ($A$10000:$A$60000)/((RIJ($A$10000:$A$60000)>=A1)*(WEEKDAG(RIJ($A$10000:$A$60000))=5));1)+1

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 221

Member Since:
April 21, 2015

Offline

July 9, 2016 - 5:44 am

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!