Share this content
7

Excel date function

Excel date function

Didn't find your answer?

Search AccountingWEB

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

Please login or register to join the discussion.

By James Reeves
16th Jan 2015 17:57

Use WEEKDAY

E.g.

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

Thanks (1)
By Dick Lloyd
16th Jan 2015 18:07

Brilliant!

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

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

did you try ...

Google - 'excel week ending date'

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
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)
avatar
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)
avatar
By paulwakefield1
20th Feb 2015 16:49

Google is a great resource

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)
avatar
By alan.falcondale
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)
Share this content