Calculate Week commencing

J

JulesM

Hi All

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

I found this:
http://www.bygsoftware.com/Excel/functions/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
completely wrong date "08/02/1900"

Am I doing something wrong?

Excel Version 2000

Any help appreciated. Thanks
Jule
 
R

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
 
R

Ron Rosenfeld

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/functions/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
Click to expand...


=A1-WEEKDAY(A1)+1

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


--ron
 
D

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
 
R

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
 
J

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
 
R

Ron Rosenfeld

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/functions/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
Click to expand...

And a more general formula would be:

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

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


--ron
 
R

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.
Click to expand...

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

Regards

Roger Govier
 
J

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 Exce
novice.

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

I want to make sure that column B is automatically calculated so
pasted the formula into the entire column. No problem for the cells i
column A that actually have a value in but for those that have not ye
any entry (i.e this is a spreadsheet that will have rows manually adde
to it) I get the ########### entry in column B simply because th
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.

Jule
 
R

Ron Rosenfeld

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 #########
Click to expand...

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).
Click to expand...

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
 
R

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
 
J

JulesM

Many many thanks again.

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

Thanks for your help
Jules :
 
D

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.
 
D

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.
 
Top