Tracker
HOME    TRACKER    Excel

# Formula To Convert Months Into Weeks

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

Related Forum Messages:
Formula: Use Numbers For Weeks & Months
It's a formula I'm looking for. The best way to describe it is an example.

I have some numerical data on a sheet from cells A2 to A80. This data represents the amount of days each person has spent on holiday. The data will be shown on a graph, but instead of the graph having "1", "2", "3", "4", along the bottom, I'd like it to have "1 week", "2 weeks", "3 weeks", "1 months", "2 months", "3 months".

I -suspect- the formula will involve the " countif" function (or an equivalent) (eg. if the number is less than or equal to 7, add 1 to the "1 week" box, etc.)

Adding Weeks Or Months To Dates
a sample of my sheet first....

Date Differences In Days, Weeks Or Months
I've been racking my brain on this one for awhile, and I feel like I'm going the right direction but...

I have a list of dates like:
11/30/2007
10/31/2007
9/30/2007
8/31/2007
7/31/2007
6/30/2007
5/31/2007

or maybe

11/30/2007
11/23/2007
11/16/2007
11/9/2007
11/2/2007
10/26/2007
10/19/2007
10/12/2007
10/5/2007
9/28/2007
9/21/2007
9/14/2007
9/7/2007
8/28/2007

...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.

The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...

If Statements: Option Of Between 3 Weeks And 6 Weeks
i currently have a what if statement

=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")

I need it to also give an option of between 3 wks and 6 weeks

Therefore all options are

3-6 WKs
< 3 WKs
> 6 WKs

I have mastered 2 but now need a 3rd.

Formula: Calculate The Weeks Cover Of Current Inventory
Calculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.

I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.

Does anyone have a suggestion as This problem is becoming quite frustrating.

Here's an example of how the spreadsheet looks

Forecast103050205040Inventory 20019016011090400Weeks Cover6.05.04.03.02.01.0

Formula For 6 Months From A Certain Date
I came up with a formula that i thought should work but apparently not so could someone be kind enough to post a formula that will take the date in one cell and place it in another but showing 6 months later.

Formula For Months To Years Conversion
Here is my formula:

=E3*(1+E9/365)^(365*E5)

Cell E5 contains a place for you to put in the number of years you want

I want to modify this formula so that it calculates months instead of years, but still be based of a 365 day calendar year.

Elapsed Months (many Months Have Gone By Since Todays Date)
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.

Formula That Averages Totals From Different Months Out Of The Year
I need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.

I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.

Formula To Calculate Years Months And Days
I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"

C7 3/24/04
D7 1/4/08

What is returned is 3 years, 9 months, 136 days

How might if fix it show the correct days?

Formula To Count 12 Months Back For Finance Calculation
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row.

We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months.

For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets.

In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.

Here is the formula:
=(FVSCHEDULE(1,M79:M90/100)-1)*100

Formula To Calculate Percent Change, Varied By Amount Of Months
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.

Add Months & Return Decimal As Years & Months
I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.

Convert Formula To VB
the following formula to be converted to vb for my userform.

=ROUNDUP(+J16-50000,-3)*0.00364+352

this is part of the vb that i'm working with and i need to change the part after "Then Amount =" I just can't seem to work out where to put the roundup bit.

Convert Formula Into Value
How to convert the excel formula into its value? I know the easiest way is to copy and use paste special then "value" options but is there a way to make it automated in case the desired output value has resulted.

HTML example:
A1 B1 C1 D1
1 2 A1+B1 Yes/No

on the above example, if D1=Yes, the output C1 will become value equals to 3 and the formula will be remove, if D1=No, the formula will still remain.

Formula Convert Into VBA
=SUMIF(Log!\$B:\$B,\$A\$2&" "&\$A4,Log!\$C:\$C)

How would I convert this into VBA

Convert Formula Into Macro
convert the formula into macro

I hv formula as following :-

=Left(B2, Find(" ", B2, 1) - 1)
I likt to convert into macro,

ActiveCell.FormulaR1C1=_ "=Left(B2, Find(" ", B2, 1) - 1)"
But it cannot work

Convert Formula To RC Notation
I have got a list of formulae in column A e.g.

'=SUM(E14:E24)
'=SUM(E14:I14)
'=SUM(J14:N14)
'=SUM(Q14:Q24)
'=SUM(V14:V24)
(formulae is stored as text, i.e. preceeding with an ')

and i am looking for a VBA macro to cycle through the list and give the relative notation in column B i.e.

'=SUM(R[-11]C:R[-1]C)
'=SUM(RC[-12]:RC[-8])
'=SUM(RC[-8]:RC[-4])
'=SUM(R[-11]C:R[-1]C)
'=SUM(R[-11]C:R[-1]C)

How To Convert/speed Up This Formula
The formula: ={IF(AND(ISNUMBER(\$A3);(\$A3-DAY(\$A3)+1)=F\$2);\$D3;IF(AND(F\$2 > (\$B3-DAY(\$B3));F\$2 < DATE(YEAR(\$C3);MONTH(\$C3)+1;0));\$D3/DATEDIF(\$B3-DAY(\$B3);DATE(YEAR(\$C3);MONTH(\$C3)+1;1);"m");0))}

I need to use this formula for over more than 30.000 rows and more than 50 columns. Is it possible to speed up the formula? Or maybe to handle this by a macro?

Need Formula To Convert Minutes Into Hh:mm:ss
I have a spot on my spreadsheet where I am given an ammount of time in minutes.
I need to convert it to hours:minutes:seconds (ie: hh:mm:ss).

Example: 6214.11 must be converted to 103:43:12
In other words: 6214.11 minutes becomes 103hours, 43mins, 12 seconds.........

Averaging Of N Weeks
I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....

Convert IF OR COUNTIF Formula To Macro
Can anyone create a vba macro with the condition mentioned below

=If(Or(( COUNTIF(F2,"*Error*")>0),(LEN(F2)<4), (COUNTIF(F2,"*html*")>0)),"Fail","Pass")

I tried to do but of no use.

Convert Formula Workbook To Values Only
I would like to have a Macro, which could find Formulas on ALL sheets and replacing them with Values (Special Paste).

Example: I have multiple sheets and on every sheet there are functions (In my case, =MAX). I would like that my Macro would simply find that function and automaticly replace it with a Value (I do not have time to Copy -> Special Paste 300 Functions)

Convert Array Formula To Macro
The attached has a mega array formula that really slows down the recalc on this spreadsheet. I would like to use a macro to write the value of the formula to the worksheet "Database" column J.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False)
'This formula below needs a VBA equivalent
' Target.Offset(0, 4).FormulaArray = "=IF(MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8)))=RC[-2],MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8))),0)"
End If

If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)
End If
End Sub

Convert Cells Text To Formula
Is there a quick way, other than VBA, to put the contents of a cell that has the text of a formula into a second cell, but as a real formula?

What I mean is, if A1 has as text: abs(-1), I would like cell B1 to read the contents of A1 and evaluate it and put the results into B2.

Convert Concatenated Text To Formula
I have a formula that I create using concatenate and I get the right formula as below:

Cell B63
="=MATCH(B61,A"&N(1+B)&":A50,FALSE)"

The result comes like this:
=MATCH(B61,A9:A50,FALSE)

I would like to use this as a formula, however I cant figure this out
=Indirect(B63)
give me a #REF error.

Convert SUMIFS Formula In 2003
How would I convert the following formula into Excel 2003?

=SUMIFS(\$Q\$7:\$Q\$5000,\$O\$7:\$O\$5000,"Cat",\$P\$7:\$P\$5000,"Dog")

Convert CSE Formula To A Macro
I am using the following Control Shift Enter (CSE) formula to find a value.

Convert SUMIFS Formula For 2003
Need convert the following formula so that it would work in Excel 2003: =SUMIFS(\$BB\$4:\$BB\$500,\$E\$4:\$E\$500,"Abbeywood First",\$L\$4:\$L\$500,"Autumn"). I could play around with it for hours and get nowhere, so I'll just turn to the experts.

Convert Formula To Value Based On Condition
I'm trying to hard-set the value of the current cell (i.e. remove formula and keep value) if there is data entered into another cell. =IF(C14="",IF(\$M\$5="",G13*(\$I\$6+1),G13+(\$L\$6/ COUNTIF(A\$12:A\$200,"<"&\$M\$5))),Set the value of this cell and remove the formula.

Convert Sumproduct Formula To Macro
The attached has a sumproduct formula that is slow down the calculation of my workbook. (I know there is also an array formula-that's another thread). I'd like to use a macro to fire on the worksheet change so I don't have the heavy recalc burden. The formula is in worksheet "Database" in column H. So far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False)
'This formula below needs a VBA equivalent
' Target.Offset(0, 2).FormulaR1C1 = "=IF(RC[-1]>0,SUMPRODUCT(-(R7C7:R35000C7<0)*0.5,--(R7C3:R35000C3=RC[-5]))+SUMPRODUCT(--(R7C7:RC7>0),--(R7C3:RC3=RC[-5]),R7C7:RC7),0)"
End If
If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)
End If
End Sub

Convert Formula To Macro Code
I have

1) A worksheet ( named PC OD) with one 80X80 matrix (matrix1)

2) another worksheet (named worksheet2) where I have 2 80X80 matrices (matrix 2 and matrix 3) and the below mentioned formulae

Matrix 2 grabs data off Matrix 1 (cell 'PC OD'!B769 belongs to matrix 1, in sheet PC OD) to round off the values in each of the 80X80 cells : ...

Turning Dates Into Weeks
"Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).

I need to turn that date into the week that it falls into within a particular quarter of a year.
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).

And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).

Check Many Weeks In A Month
I need a formula that will tell me how many weeks there are in a month.
If cell A2 had the month and cell B2 had the year then I would like to return 4.....

Rolling Totals For Several Weeks
I have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.

I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).

Is it possible to do this?

I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.

Delete Workbook After Two Weeks
Is it possible for a workbook to delete itself after two weeks or by date since putting in VBA codes?

Or does it have to be active to be able to delete itself.

I'll be going away for a holiday in two weeks (yepeee) and the person who's taking over my place will only use it on first day of my absent.

I do not want others to use my file (of course I'll back it up before I'll go).

Count Weeks Delayed
how create a formula in D2 to D27 using the date A1 = (A2 to A27), and calculate how many weeks delayed comparing the value of Cum Actual (B1) if it less than, equal or greater than the value of Cum Planned (C1).

Sheet1

ABCDE1Week26-Jul-09Cum ActualCum PlannedDelayed2105-Apr-09 - 83.27
3212-Apr-09 - 237.00
4319-Apr-09 - 416.36
5426-Apr-09 - 570.09
6503-May-09 - 672.58
7610-May-09 - 826.31
8717-May-09 667.16 1,005.67
9824-May-09 667.16 1,244.25
10931-May-09 667.16 1,431.81
111007-Jun-09 667.16 1,646.79
121114-Jun-09 908.16 1,831.06
131221-Jun-09 1,345.16 2,046.04 ..............................

Macro: How Work With Weeks
I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)

A second thought was to copy paste them to a new sheet for each seperated week.

and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week

i thought maybe working with boxname?

Function - Stock Weeks
I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.

For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:

52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks

I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.

Converting Dates To Weeks
I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.

Automatically Count Weeks
Here´s the deal: based on weekly reports, I need to count and sum orders created in our CRM.

I´m exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:

1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD

I don´t need help with the last one, that´s the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.

So, my questions are...

1. I need to set different formulas for count and sum, that´s clear, but how can I make this autimatically without having to change the formula each week?

2. Do I need to consider the date my main file is modified, and count backwards?

As usual I am not sure I´m being clear, though I hope I´m getting better at this. I´m attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.

The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.

Summing By Month From Weeks
I have a weekly forecast for what will be sold for the upcoming year and want to sum the quantity by months. For example, 1/6/07 sell 351 units, 1/13/07 sell 315, 1/20/07 sell 1,165, and 1/27/07 sell 328 units so Jan would return 2,159. To avoid future user error, I'd like to sum by month without the basic sum( range) formula and have tried the following SUMIF statement:

=SUMIF(Weeks,"=Jan*",Widget_Qty)

That returned zero. I formated my Weeks to look like "6-Jan-07" so I thought that the month name could be used as a text but this was also wrong. I also tried changing the month value from text, i.e. Jan to a date value displayed as a "mmm" but failed again.

CONVERT Worksheet Formula Into Vba Code
I'm manually entering a 10 digit code into Col A; at this moment in time I have the following formula in Col B:

Convert SUMIF Formula Into A VBA Code
I've been trying to modified the formula below into the code you see below.

= SUMIF(G3:G21,">0",G3:G21)-SUMIF(A3:A21,"=",G3:G21)

So how can to modified the SUMIF formula into a code to the finalrow?

Range("J" & FinalRow + 1).Formula = "=SUM(J2:J" & FinalRow & ")"

How Do I Convert A Cell(s) From The Formula To It's Contents
Using Excell 2000 ...

Cell A1 of the Input Sheet, for example might contain the words "This is
a TEST"

Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!\$A\$1
.... thus displaying whatever the contents is of cell A1 of the Input Sheet.

I want to save the formula results in time ...

I want to convert the cell(s) of the Output Sheet to the results ...
getting rid of the equation(s).

I'm sure I have seen this before, I just cannot remember? ... getting
old!

Convert Text To Number In Lookup Formula
I have a sheet that uses all vlookup formulas to populate the cells, and then some basic math calculation are done in seperate columns with the results. The problem is, even though the rest of sheet works fine one column is being read as text. I've tried many things to get Excel to read the column as numbers but nothing is working. So far I've tried everything I've found in Ozgrid (Paste special using add and multiply, using a working column)and the simple formatting things built into Excel, but nothing is working. All I need is for the column to be read as a number.

Formula To Convert Inches To Square Foot
I need a formula to automatically convert inches to square feet. I have =IF(G5>12,G5/144). and G5 is the cell used to enter your inch value. The formula wrks great, but only if you enter over 12 inches. I'm pretty sure Im on the right track, just need to know how to add in the part about if its less than 12 inches it should be multiplied by 12.

Convert Nested Formula Into Custom Function
is there a way to turn the following formula into a function ?

=IF( COUNTA(A1:A7)=SUMPRODUCT((A1:A7<>" ")/COUNTIF(A1:A7,A1:A7&"")),"No Duplicates","Duplicates")

I just have no idea where to start ?

Convert If & Weekday Formula To VBA
Formula in Range("E4")
=IF(WEEKDAY(E2)=1,"CN","T" & WEEKDAY(E2))

what this in VBA?

VB Calendar, If Date Not Within 12 Weeks, Then Msgpop Up
I can get the calendar control to come up and the value selected on the calendar to show up in a certain cell.

If a user selects a date that is not within a certain 12 week range, I want a message to appear ("date not within 12 week program"), and the user can't select the date.

I am storing nutrition data from a certain date the user chooses and it is a 12 week program from the start date. I want to retrieve info from those 12 weeks but do not want them to be able to select data outside the 12 weeks.