Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate Week commencing
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
On Wed, 28 Sep 2005 05:13:11 -0500, 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 =A1-WEEKDAY(A1)+1 will give the starting date of the week. Format as a date. --ron |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 ------------------------------------------------------------------------ JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609 View this thread: http://www.excelforum.com/showthread...hreadid=471344 |
#7
|
|||
|
|||
On Wed, 28 Sep 2005 05:13:11 -0500, 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 And a more general formula would be: =A1+1-WEEKDAY(A1+8-DOW) Where DOW = Day of Week (Sun=1; Mon=2; etc) --ron |
#8
|
|||
|
|||
Many thanks to all Now working perfectly. Thanks for your time. Jules -- JulesM ------------------------------------------------------------------------ JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609 View this thread: http://www.excelforum.com/showthread...hreadid=471344 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 ------------------------------------------------------------------------ JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609 View this thread: http://www.excelforum.com/showthread...hreadid=471344 |
#11
|
|||
|
|||
On Wed, 28 Sep 2005 09:44:04 -0500, 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 ######### 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 |
#12
|
|||
|
|||
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 ToolsProtectionProtect 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 CellsProtection 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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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" wrote in message ... 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 |
#15
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I calculate the week number from a date, eg 21/06/05 = 26 | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
Line or bar graphs for tracking stocks profit and loss. | Charts and Charting in Excel | |||
Excel: Is there a way to calculate the date as week of month? | Excel Discussion (Misc queries) | |||
calculate month from week number | Excel Worksheet Functions |