Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Solved: WEEK in MONTH

  1. #1
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location

    Question Solved: WEEK in MONTH

    hy again me and again asking questions...

    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 ...

    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 :

    [VBA] =INT(DAY(B4)/7+0.99)[/VBA]

    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 ...

    thanks again ...
    I like to help others... but sometimes i also need help ...

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I think this will get you your weeknumber as you define it

    =ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7,0)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location

    Question

    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 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 = ...
    I like to help others... but sometimes i also need help ...

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Could it be that you need Continental separators

    =ROUNDDOWN((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7;0)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    BTWE, you only need =TODAY() at B4
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    Maybe...


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



    Hope it helps!
    SHAZAM!

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by xld
    Could it be that you need Continental separators

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

    =INT((DAY(B4)+WEEKDAY(B4-DAY(B4)+1)+5)/7)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location

    Exclamation Sloved

    Quote Originally Posted by xld
    Could it be that you need Continental separators

    =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 ... 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 ...
    I like to help others... but sometimes i also need help ...

  10. #10
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location
    helo guys... i have a problem again... i said and marked this thread as SOLVED, becouse 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 becouse 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... =ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1)+5)/7;0)

    it just that irealised this now... maybe is the problem becouse today is sunday??
    I like to help others... but sometimes i also need help ...

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location
    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???
    I like to help others... but sometimes i also need help ...

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location
    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

    thanks again
    I like to help others... but sometimes i also need help ...

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location
    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
    I like to help others... but sometimes i also need help ...

  17. #17
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location
    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:

    =ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1)+5)/7;0) delete the red printed +1 and it works great...

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

    so thanks again ... actually without you i couldn't make this =)
    I like to help others... but sometimes i also need help ...

  18. #18
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I don't see a flaw with that formula, another way iks

    [vba]

    =ROUNDDOWN((DAY(H7)+WEEKDAY(H7-DAY(H7)+1,2)+5)/7;0)
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  19. #19
    VBAX Contributor PaSha's Avatar
    Joined
    Nov 2007
    Location
    Slovenia
    Posts
    104
    Location
    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
    Last edited by PaSha; 12-03-2007 at 06:16 AM.
    I like to help others... but sometimes i also need help ...

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It means that for the WEEKDAY function the week starts on a Monday.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •