Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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.


View Complete Thread with Replies

Sponsored Links:

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

View Replies!   View Related
Adding Weeks Or Months To Dates
a sample of my sheet first....

View Replies!   View Related
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...

View Replies!   View Related
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.

View Replies!   View Related
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


View Replies!   View Related
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.


View Replies!   View Related
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.


View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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

View Replies!   View Related
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.

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

How would I convert this into VBA


View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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


View Replies!   View Related
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)



View Replies!   View Related
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?

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

View Replies!   View Related
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.........

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

what this in VBA?

View Replies!   View Related
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....

View Replies!   View Related
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.

View Replies!   View Related
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:

View Replies!   View Related
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 & ")"

View Replies!   View Related
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)


View Replies!   View Related
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

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

I have data being retrieved from another sheet

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!

View Replies!   View Related
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.


View Replies!   View Related
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.

View Replies!   View Related
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")

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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

View Replies!   View Related
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 : ...

View Replies!   View Related
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).

View Replies!   View Related
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.....

View Replies!   View Related
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.

View Replies!   View Related
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).

View Replies!   View Related
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 ..............................

View Replies!   View Related
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?



View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Automatically Count Weeks
Heres the deal: based on weekly reports, I need to count and sum orders created in our CRM.

Im 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 dont need help with the last one, thats 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, thats 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 Im being clear, though I hope Im getting better at this. Im 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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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 ?

View Replies!   View Related
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.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved