Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Formula To Convert Months Into Weeks

1. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

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?

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.

4. Member
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. I agreed to these rules
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?

Excel Video Tutorials / Excel Dashboards Reports

6. Member
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. I agreed to these rules
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. ## 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. Senior Member
Join Date
3rd June 2013
Location
Indonesia
Posts
220

## Re: Formula To Convert Months Into Weeks

Not sure, but try this....

cheers

Excel Video Tutorials / Excel Dashboards Reports

10. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Formula To Convert Months Into Weeks

mirskymj

Excel Video Tutorials / Excel Dashboards Reports

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

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