Announcement

Collapse
No announcement yet.

Formula To Convert Months Into Weeks

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      Re: Formula To Convert Months Into Weeks

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

      If you mean Week Number see the WEEKNUM Function.

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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...

            Comment


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

              Comment


              • #8
                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)

                Comment


                • #9
                  Re: Formula To Convert Months Into Weeks

                  Not sure, but try this....

                  cheers
                  Attached Files

                  Comment


                  • #10


                    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

                    Comment

                    Working...
                    X