HOW DO U MAKE 2 dates STick together to make a WEEK LIKE 21/

Sami · Mar 5th, 2002, 12:17 PM

i dont no how u make them stick together n excel recognises them as a week? how do u make weeks? n how do i group the sales data into weeks and put the weekly sales revenue in a workbook?

Mark W. · Mar 5th, 2002, 12:20 PM

Use WEEKNUM worksheet function which is available after the inclusion of the Analysis ToolPak add-in.

Also, see http://www.mrexcel.com/board/viewtop...c=1016&forum=2 for formulas to calculate week begin and end dates.

[ This Message was edited by: Mark W. on 2002-03-05 11:24 ]

Sami · Mar 5th, 2002, 12:24 PM

sorry but i got to say WTF!!??!?!?!!? what do i press lol?

Sami · Mar 6th, 2002, 09:39 AM

hey mark i no u can do this. but the formula in the other thing didnt work. plz tell me how to enter weeks into excel!!!! thanks

Mark W. · Mar 6th, 2002, 10:12 AM

Quote:

On 2002-03-06 08:39, Sami wrote:
hey mark i no u can do this. but the formula in the other thing didnt work. plz tell me how to enter weeks into excel!!!! thanks

Provide some sample data.

Scott in Phoenix · Mar 6th, 2002, 10:35 AM

How about this:

Assume cell A1 contains your first date and cell A2 contains your second date...

=(month(a1)&"/"&day(a1)&"/"&year(a1)&" - "(month(a2)&"/"&day(a2)&"/"&year(a2))

Mark W. · Mar 6th, 2002, 10:42 AM

Sami, if what Scott is suggesting satisfies your requirements you might want to use the following instead...

=TEXT(A1,"mm/dd/yy")&" - "&TEXT(A2,"mm/dd/yy")

...but, I wouldn't recommend that you group data on such a long text string when you can use the WEEKNUM worksheet function instead.

[ This Message was edited by: Mark W. on 2002-03-06 09:44 ]

Scott in Phoenix · Mar 6th, 2002, 12:24 PM

Quote:

On 2002-03-06 09:42, Mark W. wrote:
Sami, if what Scott is suggesting satisfies your requirements you might want to use the following instead...

=TEXT(A1,"mm/dd/yy")&" - "&TEXT(A2,"mm/dd/yy")

!Mucho mejor!

Aladin Akyurek · Mar 6th, 2002, 12:52 PM

Quote:

On 2002-03-05 11:17, Sami wrote:
i dont no how u make them stick together n excel recognises them as a week? how do u make weeks? n how do i group the sales data into weeks and put the weekly sales revenue in a workbook?

Which week is 21/02/02-28/02/02?

If you want to use labels that read (for users) as weeks, e.g., 04/01/02-04/07/02 or rather 04/01/02-04/05/02 (excludes Saturday and Sunday) standing for the kalendarweek 14, you'll have a computational burden to convert this label into a week number. What follows however computes that number:

=WEEKNUM(LEFT(E2,SEARCH("-",E2)-1))

The WEEKNUM worksheet function is available if the Analysis Toolpak is activated via Tools|Add-Ins.

Sami · Mar 7th, 2002, 11:40 AM

Hey guys u seem to no ur stuff but erm the problem is i got an accounts worksheet which has weeks and the total sales coloum. there is just 2 coloumns in this worksheet. The sales revenue and week date is taken from the sales worksheet where daily sales are entered. I need to no how excel will group the sales figures by week and paste the week and the sales revenue in the accounts file. Also if say another sale is done and £10 is entered the sales revenue from the week will change automatically.
Cheers. Thanks for the info. v grateful!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 12:17 PM	#1
Sami Board Regular Join Date: Mar 2002 Posts: 122	i dont no how u make them stick together n excel recognises them as a week? how do u make weeks? n how do i group the sales data into weeks and put the weekly sales revenue in a workbook?

Mar 5th, 2002, 12:20 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Use WEEKNUM worksheet function which is available after the inclusion of the Analysis ToolPak add-in. Also, see http://www.mrexcel.com/board/viewtop...c=1016&forum=2 for formulas to calculate week begin and end dates. [ This Message was edited by: Mark W. on 2002-03-05 11:24 ]

Mar 5th, 2002, 12:24 PM	#3
Sami Board Regular Join Date: Mar 2002 Posts: 122	sorry but i got to say WTF!!??!?!?!!? what do i press lol?

Mar 6th, 2002, 09:39 AM	#4
Sami Board Regular Join Date: Mar 2002 Posts: 122	hey mark i no u can do this. but the formula in the other thing didnt work. plz tell me how to enter weeks into excel!!!! thanks

Mar 6th, 2002, 10:35 AM	#6
Scott in Phoenix New Member Join Date: Mar 2002 Location: Arizona Posts: 12	How about this: Assume cell A1 contains your first date and cell A2 contains your second date... =(month(a1)&"/"&day(a1)&"/"&year(a1)&" - "(month(a2)&"/"&day(a2)&"/"&year(a2))

Mar 6th, 2002, 10:42 AM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Sami, if what Scott is suggesting satisfies your requirements you might want to use the following instead... =TEXT(A1,"mm/dd/yy")&" - "&TEXT(A2,"mm/dd/yy") ...but, I wouldn't recommend that you group data on such a long text string when you can use the WEEKNUM worksheet function instead. [ This Message was edited by: Mark W. on 2002-03-06 09:44 ]

Mar 7th, 2002, 11:40 AM	#10
Sami Board Regular Join Date: Mar 2002 Posts: 122	Hey guys u seem to no ur stuff but erm the problem is i got an accounts worksheet which has weeks and the total sales coloum. there is just 2 coloumns in this worksheet. The sales revenue and week date is taken from the sales worksheet where daily sales are entered. I need to no how excel will group the sales figures by week and paste the week and the sales revenue in the accounts file. Also if say another sale is done and £10 is entered the sales revenue from the week will change automatically. Cheers. Thanks for the info. v grateful!