Calculate Week commencing

[JulesM]

Hi All

Can anyone help me with a formula to calculate the Week Commencing date based on a date supplied?

I found this:
http://www.bygsoftware.com/Excel/fun.../iso_dates.htm

and tried:
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)

Using a date in A1 of "28-Sept-05" (formatted) but this returns a completely wrong date "08/02/1900"

Am I doing something wrong?

Excel Version 2000

Any help appreciated. Thanks
Jules

[Roger Govier]

Hi Jules

Just format the cell with the formula as General.
It is picking up the date format and turning the result of 39 into the date
39 days after the 31/12/1899 which is the base date from which Excel stores
its dates as a number.

Regards

Roger Govier


JulesM wrote:
> Hi All
>
> Can anyone help me with a formula to calculate the Week Commencing date
> based on a date supplied?
>
> I found this:
> http://www.bygsoftware.com/Excel/fun.../iso_dates.htm
>
> and tried:
>
> =1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)
>
> Using a date in A1 of "28-Sept-05" (formatted) but this returns a
> completely wrong date "08/02/1900"
>
> Am I doing something wrong?
>
> Excel Version 2000
>
> Any help appreciated. Thanks
> Jules
>
>

[JulesM]

Thanks for ther reply Roger

I'm still not getting it to work. Sorry! Obviously I'm doing something wrong.

If I format A1 with just general format. How then should I enter the date? I tried 28/09/05 which still returned a value of 39.

Would appreciate any assistance...thanks again.
Jules

[Ron Rosenfeld]

On Wed, 28 Sep 2005 05:13:11 -0500, JulesM
<[email protected]> wrote:

>
>Hi All
>
>Can anyone help me with a formula to calculate the Week Commencing date
>based on a date supplied?
>
>I found this:
>http://www.bygsoftware.com/Excel/fun.../iso_dates.htm
>
>and tried:
>
>=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)
>
>Using a date in A1 of "28-Sept-05" (formatted) but this returns a
>completely wrong date "08/02/1900"
>
>Am I doing something wrong?
>
>Excel Version 2000
>
>Any help appreciated. Thanks
>Jules


=A1-WEEKDAY(A1)+1

will give the starting date of the week. Format as a date.


--ron

[Roger Govier]

Hi Jules

I completely misread your mail, I thought you wanted the weeknumber, not the
starting day of the week.

Ron has posted you the correct solution.

Regards

Roger Govier


Roger Govier wrote:
> Hi Jules
>
> Just format the cell with the formula as General.
> It is picking up the date format and turning the result of 39 into the
> date 39 days after the 31/12/1899 which is the base date from which
> Excel stores its dates as a number.
>
> Regards
>
> Roger Govier
>
>
> JulesM wrote:
>
>> Hi All
>>
>> Can anyone help me with a formula to calculate the Week Commencing date
>> based on a date supplied?
>> I found this:
>> http://www.bygsoftware.com/Excel/fun.../iso_dates.htm
>>
>> and tried:
>>
>> =1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)
>>
>>
>> Using a date in A1 of "28-Sept-05" (formatted) but this returns a
>> completely wrong date "08/02/1900"
>>
>> Am I doing something wrong?
>> Excel Version 2000
>>
>> Any help appreciated. Thanks
>> Jules
>>
>>

[David McRitchie]

Hi Jules,
If Sunday is the beginning of your week try the following formula
=A1-MOD(WEEKDAY(A1,2),7)

2005-09-23 Fri 2005-09-18 Sun formatted as yyyy-mm-dd ddd
2005-09-24 Sat 2005-09-18 Sun
2005-09-25 Sun 2005-09-25 Sun
2005-09-26 Mon 2005-09-25 Sun
2005-09-27 Tue 2005-09-25 Sun
2005-09-28 Wed 2005-09-25 Sun
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JulesM" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All
>
> Can anyone help me with a formula to calculate the Week Commencing date
> based on a date supplied?
>
> I found this:
> http://www.bygsoftware.com/Excel/fun.../iso_dates.htm
>
> and tried:
>
> =1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)
>
> Using a date in A1 of "28-Sept-05" (formatted) but this returns a
> completely wrong date "08/02/1900"
>
> Am I doing something wrong?
>
> Excel Version 2000
>
> Any help appreciated. Thanks
> Jules
>
>
> --
> JulesM
> ------------------------------------------------------------------------
> JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
> View this thread: http://www.excelforum.com/showthread...hreadid=471344
>

[Ron Rosenfeld]

On Wed, 28 Sep 2005 05:13:11 -0500, JulesM
<[email protected]> wrote:

>
>Hi All
>
>Can anyone help me with a formula to calculate the Week Commencing date
>based on a date supplied?
>
>I found this:
>http://www.bygsoftware.com/Excel/fun.../iso_dates.htm
>
>and tried:
>
>=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)
>
>Using a date in A1 of "28-Sept-05" (formatted) but this returns a
>completely wrong date "08/02/1900"
>
>Am I doing something wrong?
>
>Excel Version 2000
>
>Any help appreciated. Thanks
>Jules

And a more general formula would be:

=A1+1-WEEKDAY(A1+8-DOW)

Where DOW = Day of Week (Sun=1; Mon=2; etc)


--ron

[JulesM]

Many thanks to all

Now working perfectly. Thanks for your time.

Jules

[Roger Govier]

Hi Jules

I guess some of these mails are "crossing in the post".
I sent a further response to you saying I had misinterpreted your
requirement. The formula you posted gives week number. You want the date for
the beginning of the week containing your date in A1.

Ron Rosenfeld posted
>=A1-WEEKDAY(A1)+1
>will give the starting date of the week. Format as a date.

and has since posted
>>And a more general formula would be:

>>=A1+1-WEEKDAY(A1+8-DOW)

>>Where DOW = Day of Week (Sun=1; Mon=2; etc)


>>--ron

I hope you pick up this mail (or the others) and sort your problem.

Regards

Roger Govier


JulesM wrote:
> Thanks for ther reply Roger
>
> I'm still not getting it to work. Sorry! Obviously I'm doing something
> wrong.
>
> If I format A1 with just general format. How then should I enter the
> date? I tried 28/09/05 which still returned a value of 39.
>
> Would appreciate any assistance...thanks again.
> Jules
>
>

[JulesM]

Thanks again Roger

Yes...mails were indeed crossing! Thanks for you input!.

I do have another question however....apologies I am a bit of an Excel novice.

If I have 2 columns where A is the date I enter and B is the formula to calculate week commencing.

I want to make sure that column B is automatically calculated so I pasted the formula into the entire column. No problem for the cells in column A that actually have a value in but for those that have not yet any entry (i.e this is a spreadsheet that will have rows manually added to it) I get the ########### entry in column B simply because the formula cannot get a value because the cell in column A has no value.

2 questions
- Can I suppress the #########
- Can I prevent users changing anything in the "formula" column (B).

I'll happily post this again as a separate thread if required.

Many thanks all.

Jules

[Ron Rosenfeld]

On Wed, 28 Sep 2005 09:44:04 -0500, JulesM
<[email protected]> wrote:

>
>Thanks again Roger
>
>Yes...mails were indeed crossing! Thanks for you input!.
>
>I do have another question however....apologies I am a bit of an Excel
>novice.
>
>If I have 2 columns where A is the date I enter and B is the formula to
>calculate week commencing.
>
>I want to make sure that column B is automatically calculated so I
>pasted the formula into the entire column. No problem for the cells in
>column A that actually have a value in but for those that have not yet
>any entry (i.e this is a spreadsheet that will have rows manually added
>to it) I get the ########### entry in column B simply because the
>formula cannot get a value because the cell in column A has no value.
>
>2 questions
>- Can I suppress the #########

The ####'s are there because Excel is calculating a negative number, and, with
the 1900 date system, cannot display "negative" dates.

One simple way of suppressing it is with cell formatting. Merely add two
semicolons to your formatting string.

For example:

Select the range of cells with the formula.

Format/Cells/Number Custom Type: dddd, mmmm dd, yyyy;;


>- Can I prevent users changing anything in the "formula" column (B).

You would have to unlock the cells where you wish the users to be allowed to go
(the default is that they are locked) and then protect the worksheet. The
lock/unlock is under Format/Cells/Protection; and the Protect sheet options are
under Tools/Protection.

This can be password protected, although the protection is considered
low-grade; and clever users could likely break in.

>
--ron

[Roger Govier]

Hi Jules

Yes, you are right it is the absence of a date in A that is causing the problem.
Easy to cure
=IF(A1="","",A1+1-WEEKDAY(A1+8-DOW))
or
=IF(A1="","",A1-WEEKDAY(A1)+1) depending upon which of Ron's formulae you
went with.

With regard to protection, all cells are protected by default, but the
protection is only engaged when you protect the sheet with
Tools>Protection>Protect Sheet when you have the option to set a password or
not. Be sure to make a note of the password if you use one.

However, before you invoke the protection, you will need to remove
protection from the cells where you want the users to be able to enter data.
Mark the range of cells to be left unprotected, right click Format
Cells>Protection and remove the check mark from Locked. Now Protect the sheet.



Regards

Roger Govier


JulesM wrote:
> Thanks again Roger
>
> Yes...mails were indeed crossing! Thanks for you input!.
>
> I do have another question however....apologies I am a bit of an Excel
> novice.
>
> If I have 2 columns where A is the date I enter and B is the formula to
> calculate week commencing.
>
> I want to make sure that column B is automatically calculated so I
> pasted the formula into the entire column. No problem for the cells in
> column A that actually have a value in but for those that have not yet
> any entry (i.e this is a spreadsheet that will have rows manually added
> to it) I get the ########### entry in column B simply because the
> formula cannot get a value because the cell in column A has no value.
>
> 2 questions
> - Can I suppress the #########
> - Can I prevent users changing anything in the "formula" column (B).
>
> I'll happily post this again as a separate thread if required.
>
> Many thanks all.
>
> Jules
>
>

[JulesM]

Many many thanks again.

The answers are great and very much appreciated. This is by far one of the most useful and helpful forums I've been on.

Thanks for your help
Jules

[David McRitchie]

Hi Jules,
FWIW, It is the microsoft.public.excel.misc newsgroup that your question
was posted to and answered in.

For those who try to search for answers, having to weed through hundreds
of Google search pages of unrelated information from newsgroups included
on websites such as ExcelForum makes
web searches is very ineffective since sites such as ExcelForum put
newsgroup postings onto websites and to make matters even worse,
put a hundred unrelated newsgroup threads together so that you get
a false hit when search for several words.

I can weed them out of my searches but when you have to remove 60
websites from web searches it gets a little frustrating, and I know others
aren't going to be doing that. It becomes so frustrating that rather than
looking for answers all over the web, many simply reduce their searches
to a few sites, which is certainly not the best of solutions.

The advantage for you of web based newsgroups is to get around
a corporate firewall. If you are working on your own computer from
your own home, you would be better off using newsgroups directly.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JulesM" <[email protected]> wrote in message
news:[email protected]...
>
> Many many thanks again.
>
> The answers are great and very much appreciated. This is by far one of
> the most useful and helpful forums I've been on.
>
> Thanks for your help
> Jules
>
>
> --
> JulesM
> ------------------------------------------------------------------------
> JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
> View this thread: http://www.excelforum.com/showthread...hreadid=471344
>

[David McRitchie]

Hi Jules,
Thanks for indicating that the solutions worked for you, very helpful
when there are multiple and sometimes conflicting solutions.

Sorry for the long tirade it is certainly not directed at a thank you
note, but about our help in the Excel newsgroups as being anything
to do with ExcelForum.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm