First one needs to look through a single row and find where the first cell with a value (text) in it, then finds the date that that cell is under.
Second one needs to look through the same row to find the last cell that has a value (text), then finds the date again that that cell is under.
Example workbook is attached
I am making a form with drop down boxes and auto fill to make things easier. I have one Box for the Company selection another box for the occupation selection and I need a formula to find a $ value based on the selections made in these two cells. If I could establish the actual cell reference of the selected data in the second drop down list. The data will be much larger than this example and will live on a separate sheet.
View 2 Replies View RelatedI am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:
A blood sample takes 1 day, a urine sample takes 3 days.
I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date
I found the following formula in one of the other posts;
=IF(E5="BLOOD",G5+1,IF(E5="URINE",G5+3,0))
The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.
I added a new cell called Track Back on the employee search sheet.
What I want to do is only show variances for the amount of days back selected in the Track Back cell.
For example if I select the last 30 days, only the last 30 days would show up below in the sheet.
I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI need a formula thet will look at the last date of the absence and calculate what would be the last working date in the given financial yaer.
Example
Abcence Start Date 21/08/13
Abcence Due End Date 04/11/14
Financial Year Start Date 01/07/13
Financial Year End Date 30/06/14
[URL]
how to find the max date of a value in column A. From there I need to link up the price associated with that date. VBA is preferred. The information I'm working with is structured like this in this image:
MaxDate.jpg
What I want to do using VBA, is to look at the values in column A and for each of them, find the max date or most recent date. Once that is determined, on a new sheet, I want to write the value found in column A in sheet2/ColumnA and the date in column B, and the price in column C. In a nutshell, I'm looking for an effective date for pricing. The example on the bottom is basically what I'd like to extract with code onto another sheet.
I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :
1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number
I've seen a solution using the Median function but I'm not here it would work in my case. link here
I've attached a workbook : Date_Sample.xlsx‎
I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
View 7 Replies View RelatedWhat I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.
Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.
The formula I have got to work is
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)
but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER
when I change the formula to
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)
The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?
Ok so let me set this up. I have 2 columns: .....
View 14 Replies View RelatedI have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.
follow the link to view the excel file.
http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL
[code].....
I need a formula to find the delivery date for an order. If the order is placed by Friday, it will be delivered on the following Thursday as long as it isn't a holiday when it would be the following Friday. I have the holidays listed in a range of cells I2:I5.
OrderedDelivery4/3/20094/9/20094/6/20094/16/20094/7/20094/16/20094/8/20094/16/20094/9/20094/16/20094/10/20094/16/200911/20/200911/27/2009
Is it possible? If I can calculate the next Thursday part without taking into account the holidays,
Can you share the formula to find out the date and day difference. Foe example ( from :Thu 06-Jul-06 11:59 AM TO Mon 10-Jul-06 11:56 AM)
I have the dates is the same formate as shown in example.
I am about to write a macro and I want it to identify, from the C: drive, any .csv files that were created / saved today. I have managed a pure list before and I have managed to distinguish by filename, but selection by date would be exctremely useful.
View 2 Replies View RelatedI have the following info in a table:
name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555
Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.
Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
I cells B5:B15 I have a list of dates from last year. I need a formula that I can put in cell D5 that gives me the date that is closest to the same day last year.
Example:
If today is 4/9/14
and
From B5:B15 there were the following dates:
3/12/13
3/19/13
3/29/13
4/5/13
4/8/13
4/12/13
5/6/13
5/29/13
Cell D5 would contain
4/8/13 since it is the date closet to same day last year.
in which the OP was trying to find the maximum date (Col B) in which the value in Col A occurred, DonkeyOte used this formula as a possible solution
View 4 Replies View RelatedWhat I have is an output of about 30 worksheets of individual stock data in a workbook, all with the same column headings, laid out like the following (so that the Column Headings are stock data as recorded on the Date in Column A):
<Stock Name>
<Date> <Price> <Price to Book> <etc.>
with the stock titles also as the names of the worksheet.
What I want to do is create a macro that outputs to a single worksheet where I can input the stock name and a certain date for the stock, and then have it insert the appropriate row from the stock's worksheet underneath.
So, if I say I want the data for Stock A on March 24, 2007, I can input those two and have it output the appropriate rows from the worksheet.
Or, more generally, I can create a list of stock names and dates, and have the macro output that data underneath.
1 column in my sheet is a list of dates (affectively a calendar). I am trying to create a macro that will only display rows 1-5 and then current the current date and the next 30 days and hide all other rows.
The approach I have tried is to use “ADDRESS(MATCH(BS40,$B$1:$B$462,0),2)” equation to find the cell with todays date in it (Cell BS40 contains todays date). This was repeated for todays date +30. The problem I have is that although I now have the cell address’s located I cannot reference their values in a range.select
What I want to do is somwthing like this:
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
View 3 Replies View RelatedI have a spreadsheet with 2 worksheets within it. These are simply called sheet 1 and sheet 2.
On sheet 2 I have cell A1 containing =(TODAY())
Also on Sheet 2 I have cells A28 to A39 containing Month names and cells B28 to B39 containing values. Like this
January 1000.00
February 1000.00
March 2500.00
April 1250.00
May 1750.00
June 2200.00
etc
How can I return the sum of these values into Sheet 1 B18 based on the date. That is to say that I want to add up the sum of Jan, Feb, Mar, Apr, May up to and including callcualtaions made on 31st May but as soon as the date goes past 1st Jun I'd want to include June as well and so on.
I have made the macro that does as ,soon as i open the file, select today's date but only if date is in sheet5... sheet 5 is may so it works. Doing the same job for every sheet in the file.
View 10 Replies View RelatedI have a list of Names that has numbers accumulated over months. I am looking for a formula to use in a search tool that uses a drop down list to identify the name I am looking for and then the formula will show me the increase that happened in certain months.
See the attachment the result is coming from Denominators tab, I want the answer displayed in the Display tab, you will see a drop down list at C3 in Display, I am trying to show the answer in cell D9-I9.
how many the denominator has increased in that month. so for D9 it will come from denominator tab column c minus b, E9 will be d minus c
I have a table which, across the top, has different cell titles, say "Category" in A1, "Budget" in B1, and C1 through F1 are date values. I need a formula which will return "C1", because it's the left-most cell in the first row which contains a date.
I know I can find a date with ISNUMBER or the TYPE functions. But I cannot get TYPE to work on an array. Honestly, I can't even understand the behavior of TYPE. Let's say B1 contains a string, and B2:B8 contain numbers.
If I select one cell and do TYPE(B1:B8) and press enter, I get "2", because B1 contains text. If I change B1 to a number, the TYPE cell returns "16", which means error. Wha....?
If I instead press CTRL+SHIFT+ENTER on the TYPE cell, I get "64", which is the array type. So obviously this is not doing what I want it to do (which is to run the function TYPE on each of the cells B1 through B8). With ISNUMBER, I can get the array functionality, but I want to find the *first* numerical cell, not if each cell is a number.
what formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.
View 3 Replies View RelatedI have following numbers in Column A. I need formulas to get results shown in Column B and C.
ColumnA ColumnB ColumnC
Numbers Formula?? Formula??
200001 January Jan 00
200101 January Jan 01
200102 February Feb 01
200710 October Oct 07
200912 December Dec 09