# Formula To Convert Months Into Weeks

Aug 13, 2008
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 5 Replies
ADVERTISEMENT
Nov 20, 2006

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 5 Replies
View Related
Dec 17, 2006

a sample of my sheet first....

View 9 Replies
View Related
Jan 28, 2012

Inmates can apply for pre-release when they have half their minimum sentence served. For example 2 years 6 months. They would have to serve 1 year 3 months.

I use this formula

=DATE(YEAR(A1)-A5,MONTH(A1)-B5,DAY(A1)-B6)

Where A1 is half the years, B5 is half the months, and B6 is half the days. The problem is when an inmate is sentenced to a minimum of 3 years and 3 months. The latter formula will not calculate 1.5 years or 1.5 months. It rounds up. What I would like to do is covert the 1.5 years to months and the 1.5 months to days.

View 5 Replies
View Related
Jan 3, 2008

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 5 Replies
View Related
Dec 17, 2011

Is it possible to do the following with a formula in Excel...

I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.

The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.

USER FIRST LOGGED IN LAST LOGGED IN

user 1 2 years 20 weeks 54 sec ago

user 2 44 min 7 sec 1 min 37 sec ago

user 3 49 weeks 2 days 17 min 3 sec ago

user 4 5 weeks 2 days 33 min 32 sec ago

user 5 38 min 9 sec 38 min 9 sec ago

user 6 5 weeks 3 days 1 hour 7 min ago

user 7 2 hours 17 min 2 hours 11 min ago

user 8 45 seconds ago 45 seconds ago

Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?

View 9 Replies
View Related
Nov 13, 2012

I want to compress my months into seasons. To make things easy I'm going to make winter, jan feb and march etc. i have a column with the months and next to it i have numbers. i need to add the numbers into one season. also i need the months in the left to be differentiated by year so fall 2006 is different from fall 2007

View 4 Replies
View Related
Sep 7, 2009

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 7 Replies
View Related
Jan 13, 2014

find the formula to calculate how many weeks between two dates for example. I how many weeks between

December 23, 2013

January 5, 2014 = 2w

January 6, 2014

January 19, 2014 =2w

January 20, 2014

February 2, 2014

View 2 Replies
View Related
Jan 13, 2014

I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.

For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.

If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.

If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.

View 2 Replies
View Related
Jun 10, 2008

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 9 Replies
View Related
Dec 27, 2013

I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.

View 3 Replies
View Related
Aug 3, 2012

I have the data for month wise in one tab. now i want to pick up the months from dropdown list... in A1 (April) B1(june)

when i m picking the months it is automatically take those 3 months data from the data tab...

View 3 Replies
View Related
Mar 6, 2006

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 9 Replies
View Related
Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

View 6 Replies
View Related
Aug 14, 2009

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 14 Replies
View Related
Jul 31, 2013

I have a excel file which deals with accounting systems. It has the Loan date in Cell B6 and Number of dues in cell B7. I need the column from B11 : B34 to be filled with due dates for every month. Suppose if the loan date is 12/05/2013 and number of dues is 10 then in B11 I should get 12/06/2013 , B12 it should be 12/07/2013 and B13 should be 12/08/2013 and so on based on the number of dues.

View 3 Replies
View Related
Apr 11, 2014

In Column A, I have the dates listed as such. Common point is that, they are all 1st day of the month.

In Column B, I have Quarters as such.... (Please see below)

Can I put a formula together, to round up how many months left in that particular quarter ? example

Date Q3 2014 Q3 2014 Q1 2015

9/1/2014 1 3 3

Column A

9/1/2014

12/1/2014

6/1/2015

9/1/2014

9/1/2014

6/1/2015

9/1/2014

9/1/2014

...

..

Column

Q2 2014

Q3 2014

Q4 2014

Q1 2015

Q2 2015

...

...

View 9 Replies
View Related
Jul 19, 2009

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 3 Replies
View Related
Jul 31, 2007

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 9 Replies
View Related
May 23, 2014

I have a sheet that has a start date and an end date for holidays.

The start date for the test holiday is 23/05/14

The end date for the test holiday is 15/7/14

I need a formula that will calculate how many of the holidays (work days only Mon-Fri) fall in each month from May through July.

View 14 Replies
View Related
Dec 3, 2013

See attached. (I added in some arrows for the first month so it is easier to follow the logic)

I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.

Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.

Essentially, I need the formula to return the correct amount that is being paid, even across different years.

I don't know if my formula can be modified or a new formula would be more efficient.

(This post originated from Commercial Services, which I just requested be removed from that forum)

View 3 Replies
View Related
Jan 1, 2007

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 9 Replies
View Related
Sep 13, 2009

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 9 Replies
View Related
Apr 1, 2014

I have come accross this calendar online and it suits my requirements, however, the month s currently static and the year is dynamically controlled by a spin button.

The formulas in the relevent days are as follows

Day 1 =IF(AND(YEAR(JanOffset+1)=calendarYear,MONTH(JanOffset+1)=1),JanOffset+1,"")

Day 2 =IF(AND(YEAR(JanOffset+2)=calendarYear,MONTH(JanOffset+2)=1),JanOffset+2,"")

and so on, I need the formula and the spin button the also change the month as well as the year.

Please see attached file for details.

View 7 Replies
View Related
Mar 24, 2009

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 2 Replies
View Related
Sep 30, 2011

Just wondering if there is a formula where I can change the date an employee started working into the number of months employed??

Example, John Smith started here in January 2011. I would like the formula to return 8 months.

View 9 Replies
View Related
May 20, 2013

Keep getting compile error.

Sub CreatingCT()

'

' CreatingCT Macro

Dim ConsolidatedTrend As String

'Path to File

ConsolidatedTrend = "(d:2013"&(IIF((MONTH(NOW())+1>9),"(1","(0")&(MONTH(NOW())+1)&") "&TEXT(DATESerial(YEAR(NOW()),(MONTH(NOW())+1),1),"MMM")&"Consolidated Trend - "&YEAR(NOW())&" "&MONTH(NOW())+1&" + "&(12-(MONTH(NOW())+1))).xlsb)"

ActiveWorkbook.SaveAs Filename:=ConsolidatedTrend, FileFormat:=xlExcel12, CreateBackup:=True

End Sub

View 5 Replies
View Related
Mar 15, 2014

I know I will earn $5,000 of monthly revenue from a client. $5,000 is represented in a monthly revenue cell. I have 12 columns showing the 12 months of the year. There is an additional cell showing the customers implementation date.

If a company's implementation date is on or after the 15th of the month (example: 3/28/2014) then the next month (April 2014) is skipped and the $5,000 is returned to columns May through December. All months prior to May return $0.00. If the implementation date is before the 15th day of the month (example: 3/13/2014) then the next month (April 2014) and all months after will return $5,000. All months including March and prior must return $0.00. If the implementation date is unknown then 12/31/2099 would be in the implementation date cell and $0.00 is returned for all 12 months.

Essentially, if the implementation date is prior to the 15th of the month the revenue will show as of the following month. If the implementation date is on or after the 15th of the month the revenue skips the following month and will show the month after.

View 6 Replies
View Related
Jul 24, 2009

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 2 Replies
View Related
Jan 20, 2014

I need a formula to automatically change the summary column according to the month we are in.

Ie:

Last Months Data

Nov

Dec

Jan

Feb

1

8

1

7

4

7

9

2

9

'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?

I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))

View 2 Replies
View Related
Mar 14, 2014

I need a formula that will calucalte the monthly total based on the following conditions:

Col A = Yearly Cost

Col B = The number of the month when the costs are starting (1=Jan etc)

Col C = The duration or the number of months for which the costs are to spread

Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.

Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx

View 3 Replies
View Related
Nov 12, 2008

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 2 Replies
View Related
Jun 16, 2009

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 2 Replies
View Related
Jan 5, 2013

The formula below is in cell H3. My requirement is to remove the formula and instead have a macro do the same thing that formula does.. in cell H3.

Formula:-

PHP Code:

=IF(COUNTA(F:F),MIN(IF(ROW(F:F)>MATCH(TRUE,F:F<>"",0),IF(F:F="",ROW(F:F))))+2-MATCH(TRUE,F:F<>"",0),"")

View 9 Replies
View Related
Jul 7, 2009

=SUMIF(Log!$B:$B,$A$2&" "&$A4,Log!$C:$C)

How would I convert this into VBA

View 9 Replies
View Related
Jul 5, 2012

I want to convert code below to excell formula

VB:

Sub Fonksyon171819()

Dim total As Double, i As Integer

total = 0

[Code]....

View 4 Replies
View Related
Feb 17, 2014

Below is my requirement and i have to convert formulas into vba code:

If dt = "A0" Or dt = "A1" Then

Sheet1.Range("K").Formula = "$J/$ I" ' Formula is k=J/I

Sheet1.Range("K:K" & last_row).FillDown

Sheet1.Range("L").Value = "20.00%"

Sheet1.Range("L:L" & last_row).FillDown //Formula is L=20% for now i have just hardcoded the value there.

Sheet1.Range("M").Value = "=($K-$L)* 100" 'Formula is M=(K-L)/*100

Sheet1.Range("M:M" & last_row).FillDown

End If

View 5 Replies
View Related
Feb 25, 2014

How can I write this formula in vba code:

RevName = "Randolph Wakabayashi"

Range("A1").Value =LOWER((Left(Right(RevName, Len(RevName) - Find(" ", RevName, 1)), 5)) & Left(RevName, 2))

View 3 Replies
View Related
Mar 7, 2014

Any way to convert a cell result into value if it meets the criteria or else the formula stays in place

I have same formulas from cell Q17 to Q1000 some of those cell will have different results based on manual inputs that occur daily.

I would like the formula result to convert to value only if greater than -1000 or else keep the formula in place to continue future calculations till it meets the criteria.

View 2 Replies
View Related