in cell d20 i have a contract date
in cell d21 i have a lead time
this will always be in the format eg(2-3 weeks)
in cell d22 i would like it to tell me d20 plus the first number of weeks ( before the hyphen)
and in d23 the second number of the weeksafter the hyphen
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
I'm creating a workbook with the ultimate aim of graphing some data between any two dates. For this I plan to use the ComboBoxes on the Control Toolbox. Each ComboBox takes its range of data from a list of dates, with the cells set as Date format (custom as dd-mmm-yy), however when I select a date in the dropdown of the ComboBox, it is shown in number format, i.e. 38723 etc.
When I ask the ComboBox to put the output in another cell, it is input in text format as that number. Changing the output cell type does work, but once you select a new date in the dropdown, it reverts to text once again. how to set the ComboBox to display dd-mmm-yy format and it's output to be the same?
I'm trying to concatenate quite a few cells together. One of the items being included in the concatenation is a date, however when I do this the output is the numerical version of the date that Excel uses to mathematically calculate with. My question is how do I do a cell concatenation with the desired output showing the date, not the numerical equivalent used by Excel? Book1.xls attached is what I'm talking about.
View 2 Replies View RelatedI'm having where basically I've got the below scenario on a spreadsheet:
Start Date - 14/03/2013 (Cell A2)
I want to look up this date against a 52 week structure to see where it is in our "working calendar year". I've got a table which has the following fields:
Week Start (Cell E2) - E.G 11/03/2013
Week End - (Cell F2) - E.G 18/03/2013
Week Title - (Cell G2) - Week 1
This table follows the same format for all 52 weeks.
Now in this example "Start Date" falls between the "Week Start" and "Week End" date so I want to output Week 1 in cell B2. However I could have a "Start Date" of 29/03/2013 that falls in week 3 of the working calendar.
I have created a standard input box for the date (UK)
datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived
I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),
I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.
As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.
So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
I seem cannot get the code right, see below.
Cell: A3= Week 5
=VLOOKUP(RIGHT($A$3,LEN($A$3)-5),Lane!$A$9:$AD$20,3)
It won't work....
However, if I tried as follows,
=VLOOKUP(5,Lane!$A$9:$AD$20,3) OR
=RIGHT($A$3,LEN($A$3)-5)
Both works fine!!!
But I do rather something like as it is shown on the above in RED TEXT
Also I am worried if I have Week 10 the output will look as 0 instead of 10 so.
I am using the following function quite frequently: (INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1). Because of the way the data is organized the function is commonly undefined (output="#N/A"). I would like the program to print a constant (718) when the function is undefined. Below is the function that seemed intuitive to me, but doesn't do what I want.
=IF(INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1)="#N/A", 718, INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1))
I am using one file, in that out put is coming but some times it is showing out put as -1.
Please find the attachment.
In the Sheet 1 Column X some out puts are coming as -1, what is the error in my formula.
Platform Open tickets on 29.01.2014.xlsx‎
I'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.
I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.
If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.
So =IF(C21=B8),IF(C22=B11), ????
IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.
Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.
Here's what I'm trying to do hopefully you can help me.
1) Given value in Sheet 1: Column A, search for that value in Sheet 2: Columns A:C. Then output the value in Sheet 2: Column D of the same row, to Sheet 1: Column B
2) Same setup as above, but the same given value is found multiple times, then have say 1st value output to column B, second to C, third to D, and so on.
I am running a macro in Excel that when runs opens a message box that asks you to enter a start date, then and end date - this works perfectly.
However, what I now need to look at is adding a message box BEFORE these two fields that asks for a Sales Account Refrence number.
Once this number has been entered it then runs the date range boxes against that number entered.
Below is my code that currently works for the date boxes.
It is running a SQL string and the Sales Account Rerefence number is a field called org.sales_acc_ref
Sub GetData()
Dim create_timestamp As Date
Dim response As String[code].....
I have a function which either looks up a value on a spreadsheet or runs a sub. If it runs the sub, it should generate a list of figures which are output to a spreadsheet.
My function keeps crashing but I have found by stepping through it that is correctly decides whether to lookup a value or run the sub. It starts the sub correctly but when it comes to outputting information to the spreadsheet it crashes. I have lx_calc set as an array and I am using the following to output to a spreadsheet (lx_calc(20) is defined as 10000 and ImpFac and qx have already been calculated):
For i = 21 To 120
lx_calc(i) = lx_calc(i - 1) * (1 - (1 - ImpFac(i - 1)) * qx(i - 21))
Workbooks("Macro Ann Addin.xls").Worksheets("Mbr").Cells(i + 1, 1) = lx_calc(i)
Next
I can't see why this doesn't work unless it is just that it doesn't like the fact it is looking at an array.
I am making a report that puts out a CSV file my trouble is that some of the numbers are converted to date when they are opened in excel. This report is sent out company wide and I need a way to keep the numbers from changing to date. I have added a tic(') mark to the front of the number but it comes over as '12308. What can I do.
View 9 Replies View Relatedattached spreadsheet. Notice cells A11 through B23.
Is there any third-party extensions, Macros or other functions that can output the dates and corresponding text (in column A) to a nice looking calendar, or even better yet to Outlook, or both?
I tried creating a pie chart or graph function, but non contain a calendar scheme.
Notice cell B7 is an input cell. This would determine the number of days the calendar would show in full. The begin date of the calendar would be in B11, and the last date on the calendar would be in B23. It could be a 30 day calendar, or more or less depending on the input.
For those "visual" people, a calendar format would be amazing, but I can't write a single peice of VB code, so I wouldn't know how to write a Macro.
I'm wondering if it's possible to use a button in excel where I can output a cvs file (from another sheet in the file) with just a click on that button.
View 3 Replies View RelatedI have a userform that employees use to enter production data every day. Originally I had this storing the data in excel, but now I would like it to put the data in tables in access.
Here is some code I am trying, but it's giving me an error: "Run-time Error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."
VB:
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cnt As New ADODB.Connection
[Code].....
i have the formula below,
currently the output is comming as a number but i need the output to come has a percentage what adjustments do i need to make to do this?
=CHAR(149)&" "&"The overall YTD"&" "&"performance is"&('Red Activity & Performance'!R45)*100&IF(INDEX('Red Activity & Performance'!R45:R55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0))<INDEX('Red Activity & Performance'!$C$45:$C$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0)),"Is below the target (75%) by: "&TEXT(INDEX('Red Activity & Performance'!$A$45:$A$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0))-INDEX('Red Activity & Performance'!$R$44,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0)),"0%"),"has achieved the national target (75%)")
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
how to consolidate file01 to file02 merging them, resulting the expected output sheet. Currently im doing this manually and it really take up a lot of my time this is just a sample scenario. My real world use of this is composed of over 2000+ accounts with different product purchases and billing numbers.
View 14 Replies View RelatedI want to compare and output the differences in text in 2 cells.
ie. Cell A1 has a long string of text as does Cell A2 but slightly different, I want to see exactly what the differences are.
Is there a formula I can use?
I need the output in B1 to be in multiples of the value in A1, rounded up.
example:
Value in A1 is 6
random formula in B1: =IF(AND(N2>$AA$1,S2>0),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2>$AA$1,S2=""),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2<$AD$1,S2=""),ROUND((30/$AD$1)*AD2-Z2,0),IF(AND(N2<$AC$1,S2=""),ROUND((30/$AC$1)*AC2-Z2,0),IF(AND(N2<$AB$1,S2=""),ROUND((30/$AB$1)*AB2-Z2,0),IF(AND(N2=M2,N2<$AA$1,S2>0),ROUND(30*(AA2/N2)-Z2,0),IF(AND(N2<>M2,N2<$AA$1,S2>0),ROUND(AA2*(30/$AA$1)-Z2,0))))))))
So if the resulting value of my B1 formula is 2.33, I'd like to have the output be 6. If the resulting value is 14.55, I'd like the output to be 18.
I have a formula in sheet code to get a total of the following columns. The formula in these columns is not working. I'm not getting any error. The code is working fine without a require output.
Attached File : Total.xlsm‎
I want to superscript the text with red color. But now only a active cell is change to superscript with red color after running the macro.
View 2 Replies View RelatedHow do you write a code that will calculate a formula and output the result in a cell?
For example:
A1 = 1 (Named "Cell1")
A2 = 2 (Named "Cell2")
A3 = Results of macro for Cell1+Cell2 = 3
Is there a way to write it if I also want B3 = B1 + B2 and C3 = C1 + C2, etc.
I have below vba code which output result in wordpad file. I want to send output in clipboard instead of wordpad file.
[Code] .....
I'm trying to have it output certain symbols such as "Ó" and I can't quite figure out how to do it, i want it to basically write in the selected cell:
"Ó of " & a & "'s = " & b
where a and b are strings... my problem is i cant figure out how to have it output the sigma but keep the letters from going into symbol or windings...
I have two columns of data one which #N/A appears due to formulas often and the other a set range of numbers 1-60. Column Q contains numbers between 1-60 and #N/A when the lookup formula doesn't find what it is looking for. Column R contains the numbers 1-60. What I need is to place into another column are the numbers in column R that don't appear in Column Q.
View 2 Replies View Related