0
1858

# Excel date function

Excel date function

• ### Just an observation ....

Hello all you Excel wizards. I would like some help. I want to find out if there is a formula that can be used to return the date of the Friday of the week ending based on a cell in which a date has been entered.eg In cell A1, a date has been entered, such as 13/01/2015. I want cell B1 to show the date corresponding to the week ending date (the Friday) based on the date in cell A1. In this example, B1 needs to return the date 16/01/2015, being the week ending (Friday) date corresponding to 13/01/2015. Then, whenever cell A1 is amended, B2 should automatically change as appropriate. Can anyone help?

### Replies

16th Jan 2015 17:57

Use WEEKDAY

E.g.

B1 =A1+7-WEEKDAY(A1+1)

Thanks (1)
16th Jan 2015 18:07

Brilliant!

Many thanks, James. It is precisely what I was looking for.

Thanks (0)
By JC
17th Jan 2015 08:45

did you try ...

Google - 'excel week ending date'

Thanks (0)
20th Feb 2015 12:15

Have you ever tried Googling things like that?

Hi JC - this isn't because I'm desperate to lure potential ad traffic away from Google to AccountingWEB, but whenever I Google the answers to odd software niggles (including Excel techniques) I usually end up spending 15-20mins trying to find the most relevant of several answers, comparing the differences and piecing them together to try and tackle the situation I face.

And I won't mention the hundreds of occasions when I've tried that route, only to find that there are no posts that are directly relevant to my situation.

Dick got his answer back from James within 15mins in a format that directly solved his problem. That's exactly how the Any Answers page is supposed to work.

I would hate to see people not bothering to tap into the shared wisdom here because Google is seen as the ultimate answer to every question.

Thanks (1)
By JC
20th Feb 2015 13:12

Agreed – but …

@john … agreed that Aweb is a great resource

However, there is the danger that instead of making any attempt to find out the answer for themselves people will expect others to do the work for them. So I guess the question must be – how much time did the OP spend trying to find the information themselves – it took me all of 2 minutes before suggesting they use Google

In fact just re-entered the search criteria suggested - this comes top of the list

http://www.mrexcel.com/forum/excel-questions/470361-formula-find-week-ending-sunday-date.html

Allowing for the fact that the response referred to Sunday rather than Friday – 3rd response down by ‘lenze’ is:

Default Re: Formula to find a "week ending" Sunday Date

Code:

=TODAY()+(7-WEEKDAY(TODAY(),2))

Or

Code:

=A1+(7-WEEKDAY(A1,2))

Although, there are a number of other suggestions & postings on the subject

Thanks (1)
20th Feb 2015 16:49

but it would be a shame if people didn't ask here. I enjoy (trying to) answer the questions and it is a useful learning experience for me.

Thanks (0)
23rd Feb 2015 13:00

easy to check

It is easy to check the weekending day is correct by formatting the cell as DDD dd/mm/yyyy thereby getting something like "Fri 13/02/2015"

just a thought

Thanks (0)