Weeks

[vrus]

Hello everyone,

I am trying to work with date. I want to see in a cell the week numbe
with the stating date of that week and the ending date of thta week
Example:

Week #1 - 01 jan 2004 to 03 jan 2004
Week #2 - 04 jan 2004 to 10 jan 2004
Week #3 - 11 jan 2004 to 17 jan 2004


end so on.


Can anyone help me.

vru

 

[Frank Kabel]

Hi

With the assumption that you store the weeknumber in column A and use
the ISO definition for weeknumbers (Have a look at
http://www.cpearson.com/excel/weeknum.htm for more information on this
topic) use the following:
1. To calculate the starting date (Monday) of a week
=7*A1+DATE(B1,1,3)-WEEKDAY(DATE(B1,1,3))-5
A1: weeknumber
B1: Year

2. to calculate the ending date (Sunday)
=(7*(A1+1)+DATE(B1,1,3)-WEEKDAY(DATE(B1,1,3))-5)-1

HTH
Frank

 

[Norman Harker]

Hi vrus!

The following appears to meet your specification:

In A3:
="Week #"&ROW(A3)-2&" - "&TEXT(DATE(2004,1,1),"dd-mmm-yyyy")&" to
"&TEXT(DATE(2004,1,1+(1-(7>=WEEKDAY(DATE(2004,1,1))))*7)+7-WEEKDAY(DAT
E(2004,1,1)),"dd-mmm-yyyy")
In A4:
="Week #"&ROW(A4)-2&" -
"&TEXT(DATE(2004,1,1+(ROW(A4)-3-(1>=WEEKDAY(DATE(2004,1,1))))*7)+1-WEE
KDAY(DATE(2004,1,1)),"dd-mmm-yyyy")&" to
"&TEXT(DATE(2004,1,1+(ROW(A4)-2-(7>=WEEKDAY(DATE(2004,1,1))))*7)+7-WEE
KDAY(DATE(2004,1,1)),"dd-mmm-yyyy")
Copy down to A54
In A55:
="Week #"&ROW(A55)-2&" -
"&TEXT(DATE(2004,1,1+(ROW(A55)-3-(1>=WEEKDAY(DATE(2004,1,1))))*7)+1-WE
EKDAY(DATE(2004,1,1)),"dd-mmm-yyyy")&" to
"&TEXT(DATE(2004,12,31),"dd-mmm-yyyy")

I've used a Chip Pearson formula for determining the nth DoW in a year
as the basis:
Chip Pearson:
http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

You'll need to amend for where you place the formula but note that
those ROW functions use the row that the formula is in.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[vrus]

Thanks guys,

It works good now thanks to your help.

But I thought there was something in excel that would tell me the start
date and the end date of a giving week. Is there something like that?

 

[Norman Harker]

Hi vrus!

There isn't a function that does this. You could roll your own with
VBA.

Closest you get is WEEKNUM which is Analysis ToolPak and which gives
you the week number based upon alternative definitions.

One difficulty is establishing the basis of your week numbering
algorithm. See:

Chip Pearson:
http://www.cpearson.com/excel/weeknum.htm
General discussion plus formulas.

The base formula I used was from Chip Pearson and was:

=DATE(Yr,1,1+(Nth-(Dow>=WEEKDAY(DATE(Yr,1,1))))*7)+Dow-WEEKDAY(DATE(Yr
,1,1))

Yr is replaced by the four digit year number
Nth is replaced by a number represent the occurence of the desired day
(e.g.) 15 for 15th occurrence
Dow is replaced by 1=Sunday, 2 = Monday ...

But finding the start of a given week number with the WEEKNUM argument
1 assumption is complicated by the fact that unless 1-Jan is a Sunday
then week 1 starts 1-Jan and week 2 starts with the first Sunday of
the year.

With the ISO system, life is more difficult because for example week 1
of 2004 started on Monday 29-Dec-2003. Not as stupid as it seems
because at least with the ISO system all weeks for all time start on a
Monday and have 7 days in them. You'll see that Chip gives John
Green's UDF for determining Day 1 of ISO Week 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.