Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Formula To Convert Months Into Weeks

  1. #1
    Join Date
    13th August 2008
    Posts
    2

    Formula To Convert Months Into Weeks

    Hi,

    I'm having an issue converting months into weeks. My example is I have people who must provide leaving notice (could be months or weeks). For instance column B details months, and column C details weeks. I'd like to convert months (column B) into weeks in column D, but am not sure of the components of such a formula so I won't list or guess as I'm just not sure.

    Your help is greatly appreciated.

    Crusader

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Formula To Convert Months Into Weeks

    Your question is not clear at all. Can you post an example workbook and explain in context?
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,728

    Re: Formula To Convert Months Into Weeks

    The Month January etc would "convert" to Week?????

    If you mean Week Number see the WEEKNUM Function.

  4. #4
    Join Date
    11th December 2007
    Posts
    36

    Re: Formula To Convert Months Into Weeks

    Converting months to weeks really depends on the definition of months according to your co. I know accountants who live 48-week years. (Which I would say is just living too fast).
    So, if a month is four weeks:
    week value = month value * 4
    4 months = 16 weeks

    If a month is 30 days:
    week value = month value * (30/7)
    4 months = 17.14 weeks

    If there are 52 weeks in a year:
    week value = month value * (52/12)
    4 months = 17.33 weeks

    If there are 365.25 days in a year:
    week value = month value * (365.25 / 12 ) / 7
    4 months = 17.39 weeks

    Hope this helps

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    13th August 2008
    Posts
    2

    Re: Formula To Convert Months Into Weeks

    apologies for not being clear. I attach an excel example of what I'm trying to achieve.

    In column A, I present a number of people; column B, the notice period those people must provide when resigning their post (in months); column C, the notice period those people must provide when resigning their post (in weeks); in column D, I'd like to automate this function by writing a formula that give me the following:

    =if(b2>0,[?X?],C2)

    where [?X?] converts the number of months leave into weeks i.e. 3 months notice = 12 weeks leave (roughly speaking). Is there a (combination of) function/formula that will calculate this for me?
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    11th December 2007
    Posts
    36

    Re: Formula To Convert Months Into Weeks

    Also, logically, wouldn't you prefer to "if test" for the week value, which will definitely be accurate in weeks and only return the converted month value in the absence of a week value?

    I mean =if(c2<>0,c2,[x?x])

    If both are entered presumably you'd want to display the week value as entered...

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th October 2011
    Posts
    1

    Re: Formula To Convert Months Into Weeks

    I was just working on this exact problem and decided to check the posts to see if anyone had a creative solution. Let me first restate the problem:
    It is like solving a quadratic equation. One formula is to split the quantity into weeks with the proper allocation. The next formula is to ensure that over a three month period the total quantity should equal the sum of 13 weeks.

    Business conditions may drive the solution is different directions. If the forecast is driving the production schedule you can not afford to build the inventory late. If cash flow is tight you might want to be more conservative and how fast to make or buy.

    As a result of these considerations I have developed at least three procedures for "spreading" the monthly forecasts into weeks. Then the user is given the choice as to which method they would prefer. My preference is to do some monthly averaging. This has the affect of pulling peak demand forward in time, which gives you more time to build, make or buy. This would be synonymous with carrying a safety stock and replenishing it early.

    Here is the bottom line. Think in terms of a 4, 4, 5 set of weeks where every third month has a five week month. Divide each monthly average by 4.29 and apply it to each week. I cant speak for your business situation but this gave me a satisfactory result. Arrays work very well for handling the intermediate values and results. If you have specific questions about this post leave a message here.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th December 2013
    Location
    Sydney, Australia
    Posts
    136

    Re: Formula To Convert Months Into Weeks

    you try the below formula where E1 could be todays date and B2 is months

    =round(IF(B2="",C2,((DATE(YEAR(E1),MONTH(E1)+B2,DAY(E1)))-E1)/7),0)

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    3rd June 2013
    Location
    Indonesia
    Posts
    173

    Re: Formula To Convert Months Into Weeks

    Not sure, but try this....

    cheers
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,876

    Re: Formula To Convert Months Into Weeks

    mirskymj

    Please open a thread for your own question.

    For all contributors, please do not reply to the thread that is hi-jacked.

    Thread closed

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Date Differences In Days, Weeks Or Months
    By cochese in forum EXCEL HELP
    Replies: 5
    Last Post: January 3rd, 2008, 22:58
  2. Calculating Months And Remaining Weeks
    By Jerid421 in forum EXCEL HELP
    Replies: 1
    Last Post: February 15th, 2007, 02:02
  3. Formula: Use Numbers For Weeks & Months
    By rayjt9 in forum EXCEL HELP
    Replies: 5
    Last Post: November 20th, 2006, 20:02
  4. Formula for 6 months from a certain date
    By Alan_Slater in forum EXCEL HELP
    Replies: 9
    Last Post: May 22nd, 2006, 19:28
  5. Replies: 2
    Last Post: February 1st, 2006, 03:59

Bookmarks

Posting Permissions

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