WEEK in MONTH

hy again me and again asking questions...:whistle::(

i opened a new post, becouse this is a simple question, but i thought, when someone else was having the same trouble, maybe he would find this post on the internet, and maybe also a solution :cloud9:...

so the problem is this ... i need to calculate which week in month it is...

i have made some functions by myself but they didn't run like they should... so i googled it and found this function which was similar to myn and i thought it works... but today i realised it doesn't... so the function look like this :

Code:

---

=INT(DAY(B4)/7+0.99)

---

and like it says: for the today it should bring me that it is the 4th week in month and 3th day in week!! but it results this : 3th week & 3nd day...


so why is this so ... how can i get this week number in a month ...

i need to know this, becouse i have some if statements which then link to specific workbooks when such week number is today...


so if anyone knows... please help...

i know maybe stupid question... but that's why i posted it... maybe someone else needs this also, maybe not today but in the future ... :dunno

thanks again ...

Are you counting weeks from 1 + multiples of 7 or weekdays. ie in what week is 5/Nov/07?
If the former, try =ROUNDUP(DAY(B4)/7,0)

I think this will get you your weeknumber as you define it

Code:

---

=ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7,0)

---

Quote:

---

Originally Posted by xld

I think this will get you your weeknumber as you define it

=ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7,0)

---

hmmm i got an error, which says: You have entered to few arguments ...

hmm :think: at cell B4 a have the function =NOW() ... so i think this is correct

if i tray to calculate this by hand i can't get it right ... 21 + ( 4-21+1+5 )/7 = 10 / 7 = ...

Could it be that you need Continental separators

Code:

---

=ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7;0)

---

BTWE, you only need =TODAY() at B4

Maybe...

Code:

---

=INT((DAY(B4)-1)/7)+1

---

Hope it helps!

Quote:

---

Originally Posted by xld

Could it be that you need Continental separators

Code:

---

=ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7;0)

---

---

or even use

Code:

---

=INT((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7)

---

Quote:

---

Originally Posted by xld

Could it be that you need Continental separators

Code:

---

=ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7;0)

---

---

hey guys.. sorry for the late answer... but yesterday i went to sleep...

it works ... this formula works, i wwas just falling to sleep, that i didn't saw that (;) mistake ...

and now it works ... thanks again xld :cloud9: ... and also others who replayed to my post, but i didn't trayed other formulas, becouse this was doing fine, so can't tell if other work... i can just say : case SOLVED :cloud9:...

helo guys... i have a problem again... i said and marked this thread as SOLVED, because i thought that formula for calculating the week in month works fine... and actually it does... till today as i saw that it shows that the week is the 2nd in month ???? but it should be the 1st ...


why is this happening now?? i thought that the reason is because it counts new week from the sunday and not from monday...is it this??? if not why is it?? and can i make it to work normally...??

the formula i used was...

Code:

---

=ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1)+5)/7;0)

---

it just that i realised this now... maybe is the problem because today is sunday??

Even if it counts from Monday it would still say 2 as there are days in the month before the first Monday.

I think you need to CLERALY explain the rules for determing when the first week starts.

Quote:

---

Originally Posted by xld

Even if it counts from Monday it would still say 2 as there are days in the month before the first Monday.

I think you need to CLERALY explain the rules for determing when the first week starts.

---

hey xld thanks for the replay...

how do you mean this?? don't understant what you mean... month befor first monday...

is there then any why to calculate which week it is in month?? this formula works great, but till now... now i don't now what to do???

Yes there are 50,000,00 ways, but each one needs to now its rule so as to know how to determine the week number.

I thought I understood that rule, but now you say it doesn't work, so it seems I don't. Therefore, tell us what the rule is.

Quote:

---

Originally Posted by xld

Yes there are 50,000,00 ways, but each one needs to now its rule so as to know how to determine the week number.

I thought I understood that rule, but now you say it doesn't work, so it seems I don't. Therefore, tell us what the rule is.

---

hmmm i also thought that formula works...becouse it was working fine last month... but now when it is decemer the 2nd and it should show the 1st week but that formula shows the 2nd week...

OK what i actually whant is this! if you look the calendar today you see
TODAY IS: Sunday, 2nd, Dezember, and it is actually the first week in this month
TOMORROW: it will be Monday, the 3th Dezember and if you look calendar it shows that tomorrow should be the 2nd week in this perticular month...

so do you know what i mean?? actually this should work on base of the day Monday... if today is monday make week + 1... but it should also now on which day some month begin...

hope i explained what i mean :doh:

thanks again

By the rules you stated it should be week 2. You said that the week starts on Sunday, so the 1st Dec is week 1, week 2 starts on 2nd Dec.

Quote:

---

Originally Posted by xld

By the rules you stated it should be week 2. You said that the week starts on Sunday, so the 1st Dec is week 1, week 2 starts on 2nd Dec.

---

no that's the problem actually... becouse the week starts when monday starts, not sunday.

i think in excel in by default that the week starts when sunday starts... i also had to make changes in the formula for calculating the =WEEKDAY()
becouse it show me a day more... for today it would show me day 2...
so i put a -1 there for ex.=WEEKDAY(H7-1), so it shows me now the first day in week not the second.... becouse the should start when moday starts...

so i think there is the problem also for the formula of week in montth...becouse the formula thought the if sunday is today then week+1 ...
but it should be if monday then week +1

hehe hope i explained it so you understand me :giggle

hey xld...

now i know where the mistake was...

like i sad in excel is by default that WEEKDAY() starts counting from sunday...so sunday is for them the 1st day in the week...but it actually should be the last or the 7th day in the week

thats why in this formula your wrote =ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1)+5)/7;0) it made problems becouse it counted sunday for as it was new week so week+1 ... but thats WRONG...

so i had to make just a little change(delete) in that formula and now it wroks PERFECT i also checked it by myself changing manualy the dates...

so the formula should look like this if you what that the week starts also when monday starts:

Code:

---

=ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1)+5)/7;0)

---

delete the red printed +1 and it works great...

Code:

---

=ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7))+5)/7;0)

---

so thanks again ... actually without you i couldn't make this =) :cloud9: :hi:

I don't see a flaw with that formula, another way iks

Code:

---


=ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1,2)+5)/7;0)

---

i know there is no flaw actually...it's just about how it calculates the start of a week, sunday or monday...

but can you explain what you made now for changes with +1,2 ?? what does this actually mean?

thank again

It means that for the WEEKDAY function the week starts on a Monday.

oh :cloud9: ok... that's it then :) ...

i think now it works 100% :thumb ... thanks xld again...

solved again :giggle