I have a sheet where I need to return the delta of dates as follows:
IF C has a date then I need the delta between C and A UNLESS there is a date in B in which case I'd need the delta between C and B.
If C is blank then I need then I need the delta between "today" and A UNLESS there is a date in B in which case I'd need the delta between "today" and B.
I have two factors that both change, resulting in two unique values. I'm trying to determine which portion of the resulting delta is attributable to each factor. Example:
Hourly Cost Run Rate Piece Cost (Hourly Cost / Run Rate)
$105.00 4,500 $0.0233
$126.10 3,000 $0.0420
Two unique results titled Piece Cost = Delta
$0.0420 - $0.0233 = $0.0187
How much of the $0.0187 is attributable to the change in Hourly Cost and how much of the $0.0187 is attributable to the change in Run Rate?
I'm trying to figure out how to insert the delta symbol into a header using VBA. The symbol code is 0394. My line of code is the following but it is giving me a weird "D"-like symbol. Any thoughts on how to correct this?
Code:
ActiveWorkbook.Sheets("The Flux").Range("G2") = ChrW(394) & " Prev. Year"
I have a Pivot Table. I have two Columns Currently Current Week and Previous Week. The Values are displayed as a count for the Status Field. I would like to know if it is possible to add a calculated formula that will take the count difference between Current Week and Previous Week?
View 1 Replies View RelatedToday I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
I have a list of Dates (01-OCT-2011, 03-Dec-2011, 12-Jan-12, 10-FEB-2012, 03-APR-2012, etc). I would like to figure a formula that would see the the date and return the QUARTER. For example:
IF dates are between 01-OCT-2011 thru 31-DEC-2012 THEN return QTR1, IF dates are between 01-JAN-2012 thru 31-MAR-2012 THEN return QTR2, IF dates are between 01-APR-2012 thru 30-JUN-2012 then return QTR3, etc
I'm really just now sure how to do the "betweens".
I have 2 cells F7 and G7 each with a date in. What I hope to do is in cell I7 return the value Yes if the date in G7 is earlier than F7 and No if it is not.
View 2 Replies View RelatedI am trying to generate a formula that looks at dates in one column, finds all the same days, then looks at another column and adds the values for those dates.
I formatted it below for simplification but on the actual sheet the columns would be A, E, K respectively
DATE
DURATION
9/2/2011
1
9/2/2011
1.2
9/2/2011
3.2
[code]....
how to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies View RelatedFormula that will return a string of dates (in values so that I use the cell for additional formula).
For example:
A1 B1 C1:to whatever cell needed, to be autofilled with day, date, month, year, weekdays only.
Start Date 2013/07/04 Monday, July 1, 2013
It would be really nice if it highlights the local holidays too.
I receive a stock on hand report daily which containers 7,000 + lines each day.
I want, in another sheet to show me what new product codes have appeared when compared against the previous day, for example
Date Product #Qty
1/10/201312341
1/10/201312351
1/10/201312361
1/10/201312371
2/10/201312341
2/10/201312351
2/10/201312361
2/10/201312371
2/10/201312381
So in a new sheet I want to show Product # "1238" as this did not appear on the previous days list
What I want to do is return the oldest date to another sheet, but I only want to return the oldest date if the backlog value is higher than 0. So example below has oldest date with 0 as backlog value, I don't want to return that date, but go to the oldest one with an actual backlog value.
So I have the following raw data in Sheet1
[Code] .....
Now I want to put this raw data in Sheet 2
I use this formula to pull the data =SMALL(Sheet1!C3:C5,1)) but this returns the B4 value to me, which I don't want as A4 is 0.
I've been thinking about somehow using VLOOKUP or IF to sort this out but I am stuck.
if A1 is a start date and B1 is the end date, I want C1 to return the number of months there are between the start and end dates?
View 9 Replies View RelatedI am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet.
This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.
Sheet1
BCDEFGHIJ2Product10/08/200917/08/200924/08/200931/08/200907/09/200914/09/200921/09/200928/09/20093A228157989393994B1784371107922385C4483398261701298
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
And I am after a formula that will return the total sum of values between two specific dates.
So if my results table looks like the one below, the values the formula would return are shown in Cells E9:E11.
Sheet1
BCDE8ProductStart DateEnd DateTotal9A24/08/200914/09/200934110B10/08/200907/09/200918511C31/08/200921/09/2009225
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I have been trying to search for either a function or VBA that will perform the following:
If I enter I want all months >=1/1/07 and <=3/31/07 it would tell me that the months in between are January, February and March and so on with all dates.
I have a Training Matrix with "Main", "Leavers" and "Reports" sheets. (can email it but can't upload it here; it's 534kb and I can't make it smaller)
In the "Reports" sheet I need to fill "Site Procedural Training" - "Number of people trained in period".
The periods are :
25/12/11 - 31/03/12
01/04/12 - 30/06/12
01/07/12 - 29/09/12
30/09/12 - 29/12/12
The source of data will be the "Main" and "Leavers". The data are dates in these sheets; each date = 1
The formula needs to look in to "Main" and "Leavers" and if it finds a date which fits in the required period in "Reports" then return it as 1. If it finds 300 dates then returns 300.
I have a workbook with two sheets. The first has a list of job positions open, columns designated to stages in the employment process and in these columns, my staff enter the dates that they completed a particular stage.
"Position | Date Opened | Stage 1 | Stage 2 | Stage 3 | Stage 4"
Pos 1 | 01/01/2014 |02/01/14|03/01/14|07/01/14|09/01/14
This has a great number of entries and they are increasing and decreasing every day depening on the amount of jobs available.
On the second sheet, I am trying to set up a table which shows the average working days it is taking to complete each stage, divided into the months in which the job position was opened (i.e. for positions opened in january, the average completion working days for stage 1 was X amount of days etc...)
I have tried using =IF(AND(Logical, Logical),TRUE,FALSE) but this doesn't return any info as the logicals will always be false. I tried the OR function, but that requires only one of the criteria to be true to return a value. What I need is for the formula to return a number of days between two dates, ONLY if the opened date falls in one month.
I got a good start on what I need to do from this thread here: [URL] ......
A user will use a userform to enter in results from a room inspection into one sheet and then on another sheet selects the maid and it pull up the matching room inspections. I wish to then limit it to a date range which can be found in two cells.
Currently cells D2:H2 contain the array
[Code] ......
and cells D3:H3 contain
[Code] .........
I would like to further limit those searches by restricting the date range, Cells D4 and E4 contain the first of the month and last of the month respectively.
I would like to avoid the easy answer, start a new workbook each month, but I won't be the person entering the data or using the separate sheet to conduct performance reviews so it needs to be one workbook that lasts from month to month.
I need a macro that based on multiple cell dates determines if the person is qualified or not, and puts yes or no in column A... all of the dates are yearly.
View 5 Replies View RelatedI want to use a COUNTIF to return the sum of all the dates that fall within a given month/year. For example: E1 Contains the date July-2009
Column A has date entries such as July 3, 2009, July 18, 2009, August 4 2009. In F1 I want to return the sum of all dates that fall within the month of E1.
I need a formula that will calculate the percentage of the total duration between two dates (in dd.mm.yyyy hh:mm format) that a third, intervening date represents.
As an example:
a) 10th Jan 2014 09:00
b) 20th Jan 2014 09:00
c) 30th Jan 2014 09:00
In this case the answer sould be 50% because 'b' represents exactly half of the time elapsed between 'a' and 'c'.
Note: the formula needs to work effectively across the boundary between two consecutive years.
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2.
Column B -- Column D
07/06/09 -- 54000
08/06/09
08/06/09 -- 62000
08/06/09
09/06/09 -- 61000
I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned.
As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.