8 Replies Latest reply: Oct 6, 2016 5:50 PM by Marco Wedel

# how to get week

HI All,

i am trying to put days under particular weeks,which is not working .

if(Day='1,2,3,4,5,6,7','Week1',if(Day='8,9,10,11,12,13,14','Week2',if(Day='15,16,17,18,19,20,21','Week3',if(Day='22,23,24,25,26,27,28,29,30,31','Week4')))) as Week

can any one suggestion where it went wrong

qt1:

Dual(Date(Date,'MMMM') ,Month(Date(Date,'MMM'))) as Month,

day(Date) as Day,

//'Week' & Week(Date) as Week;

quantity

FROM

[C:\Users\naveen.kumar.kunuru.DIR\Desktop\date.xlsx]

(ooxml, embedded labels, table is Sheet1);

if(Day='1,2,3,4,5,6,7','Week1',if(Day='8,9,10,11,12,13,14','Week2',if(Day='15,16,17,18,19,20,21','Week3',if(Day='22,23,24,25,26,27,28,29,30,31','Week4')))) as Week

Resident qt1;

• ###### Re: how to get week

hi

here is another version that should work

qt1:

Dual(Date(Date,'MMMM') ,Month(Date(Date,'MMM'))) as Month,

day(Date) as Day,

//'Week' & Week(Date) as Week;

quantity

FROM

[C:\Users\naveen.kumar.kunuru.DIR\Desktop\date.xlsx]

(ooxml, embedded labels, table is Sheet1);

ceil(Day/7) as Week ///this will create your week number in much more efficient way

Resident qt1;

• ###### Re: how to get week

HI

if i use ceil function it coming out 5 weeks in a month,but i need only 4 weeks for all the months

• ###### Re: how to get week

hi

in this case , change the function to :

if(ceil(Day/7)>4,4,ceil(Day/7)) as Week ///this will create your week number in much more efficient way

Resident qt1;

• ###### Re: how to get week

perfect man,thanks very munch...it worked

• ###### Re: how to get week

can u plz explain me wat exactly this is doing ..plz

if(ceil(Day/7)>4,4,ceil(Day/7)) as Week

• ###### Re: how to get week

Even this is not working ,i am getting   ')' missing  error but everything looks ok

if(Day <=7,'Week1',  if(Day >7<=14 ,'Week2', if(Day >14<=21,'Week3', if(Day >21<=31,'Week4')))) as Weeks

• ###### Re: how to get week

You should read this post and it has links to other week manipulation posts embedded

Recipe for a 4-4-5 Calendar

• ###### Re: how to get week

Hi,

also possible:

```RangeMin(Ceil(Day/7),4) as Week
```

hope this helps

regards

Marco