Calculating Dates Depending On Information
May 18, 2006
I would like to calculate the date but depends on how the information is scattered. In the first coloumn I have a set of dates, in the second coloumn I have a list of letters. I would like a macro that reads the the letters, and as they disappear calculate the time interval. For instance,
01/01/2002 B,C
02/01/2002 A,B,C
03/01/2002 C
So it firsts starts off and sees that the letters are B and C are there to begin with, then it moves on to the second row and then sees that the letter A has been added. Then to the third row and sees that there is only C so A lasted for 1 day (was there on 02/01/2002 and disappeared on 03/02/2002) and B lasted for 2 days (was there on 01/01/2002 and disappeared on 03/01/2002).
View 6 Replies
ADVERTISEMENT
Jun 12, 2009
I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week.
What would be the code to....
In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell.
Does anyone have any ideas how to do that?
View 10 Replies
View Related
Sep 11, 2006
i have 20 different materials which have codes assigned to them
wh = white
ivory = ivory
db = duck egg blue
etc etc
when i create an order some of the codes will be called up
i want to be able to put them codes plus the description into the worksheet Ideally into the header. if not call a1
View 9 Replies
View Related
Mar 9, 2012
I have a graph which displays information from a vlookup.. The vlookup can bring either numbers which are in percentages format or ordinary number format.
The question I have is is it possible for the graph to automatically change format depending upon the information who is looked up..
If so how can this be done..
View 1 Replies
View Related
Mar 9, 2009
I have been having alot of trouble with a spreadsheet and have managed to overcome some huge hurdles until now, and I'm really stuck in a rut. If you look at my spreadsheet the main focus is on the Final Results page. I currently have the data in one sheet which I then autofilter to find out the percentage rejection :- For example select Grower (Grower9), Product (Lillies Stores), Variety (Orientlstd). This the shortens the list to about 14 items which I then work out by hand.
What I want to achieve is on the Grower Summary page, to work out the overall percentage of rejection I.E. = (Sum of data matching critera in Row F / Filtered data of Sum of Row E) * 100. The next problem I have then is I want to be able to do it quickly / automatically based on the content (It doesn't have to autofilter on this sheet, it's just like that to make my life easier at the moment), so it will work out all of the growers / products / varities and put them onto a seperate summary page for each grower. See the attached sheet!
View 4 Replies
View Related
Mar 22, 2007
I'm trying to put in dates into my spreadsheet depending on the year I enter in. in cell A1, I put in the year (2007). In cell S2, I want to have the first day of the first month to show up, so my formula is =DATE($A$1,COLUMN()-18,1). That works great, it shows up as 1/1/07, which I want. Now my question is, is there a way to copy the formula over to the right, leaving three blank cells and having 2/1/07 show up in cell W2, 3/1/07 in cell AA2, and so on. Right now when I copy it over, what shows up is: 5/1/07 in cell W2, 9/1/07 in cell AA2, and so on.
View 9 Replies
View Related
Dec 17, 2006
I'm trying to create a sickness record book for colleagues at work, so we can monitor their absense accoridingley. I am trying to create a spreadsheet that will do the following:
Look at the dates that are over 12 months and not count those in the total occurances countCheck the dates that someones works and compare this to the absense date and automatically calculate the total time off
Are these points possible or am I wishful thinking? Just I have 60 people in a team and its hard doing them all in one go, but would be good if there is a way of it automatically doing this.
View 4 Replies
View Related
Oct 31, 2013
I'm trying to get dates within a column on a table highlighted depending on their value.
I know how to do this using today's date, but I am having issues in using a separate set date as the parameter.
For example;
I want all values in a table in =K7:K72 that are more than 30 days older than a value set in A3 to be highlighted red.
This should be quite easy, but every time I enter anything the whole column changes colour when this is incorrect.
View 1 Replies
View Related
Aug 24, 2009
I have column "C", starting from "C5" with dates till C200, but not all cells have dates some of them are also blank.
I want the cell to change color depending on the date.
If the written due date is in a month from today, I want it to turn yellow, and if the due date is in the past from today, I wanted to turn red. I want it to get updated every month.
View 9 Replies
View Related
May 15, 2012
I need to calculate an SLA.
I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:
dd/mm/yy hh:00
I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.
I need a macro that compares the two and works out the following:
< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"
View 4 Replies
View Related
Mar 3, 2009
to know the number of hours between A5 and J5 of my spreadsheet - working day is 8.30am to 5.30pm also need excluding weekends and bank holidays in the uk. I need the result to appeer in K5. the date and time format i have is: 2009-02-04 - 11:56:39 in column a5 and 2009-02-16 - 14:23:45 in column J5
I have over 100 of these calculations to do per month and it takes me ages manually.
View 9 Replies
View Related
Mar 31, 2009
I have two columns. The first column contains cells with multiple date values in text format (YYYY/MM/DD) in each cell. The other I was planning to be Calculated cells that I thought would compare the string of text (possibly converted to numbers) and determine which is the next in sequence after today's date. As there may be numbers in the past I cannot just use the first item.
The cell of text looks like this:2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00
The calculated cell (based on today's date) should output:2009-04-10 00:00:00
Problem: I've been unable to figure out a calculation that will accomplish this. The idea I had was to examine the text in pieces and compare them numerically to "today" (as a number instead of a date).
I'm not very adept with Excel functions yet. I've started with this: "=(MID(A2,2,10))" but all it does is return the first item. I also tried using a Replace function to get rid of the dashes and also one that would return the dates as a decimal. But while I can get the numbers to change format, I don't know how to do the compare...
View 9 Replies
View Related
Sep 27, 2006
I need to calcuate how many times a date( any date for the month) aprears in the list. it needs to be calucated by each indivual month form a list that will span over a 12month period.
the problem i'm having is how do i get it to only read the month that i want?
View 9 Replies
View Related
Apr 3, 2007
I am starting a project that will involve finding how old people are using the difference of two years. Cell A1 will contain the function that automatically obtains the current year from the PC clock (or a manual input). Cells B1...B6 will contain text (names). Cells C1...C6 will contain the month of their birth. Cell D1...D6 will contain the day of their birth. And cells E1...E6 will contain the year of their birth. Cells F1...F6 will have the formula to calculate the difference between A1 and its respective cell column E.
The only functions I know are =(TODAY) which does not give me just the year. Also, I do not know how I would be able to do the calculation [e.i., =(TODAY) - 1988].
View 6 Replies
View Related
Jul 21, 2009
I have 2 Tabs.
1) Furnaces --> INPUT
2) Report --> OUTPUT
1) I have this speadsheet and every day i entry new information:
Date Furnace# Burner
6/1/2009F-11
6/1/2009F-513
6/1/2009F-512
6/2/2009F-16
6/3/2009F-15
6/4/2009F-14
6/4/2009F-514
6/5/2009F-511
6/6/2009F-12
6/7/2009F-11
2) I want in this tab to get information from the other tab (Furnaces) in order to have an every day control.
Furnace #Burner 6/1/2009 6/2/2009 6/3/2009 6/4/2009.. etc
F-1 1
2
3
4.............................
View 2 Replies
View Related
Nov 22, 2011
I am working on a project at work. I need to track our VIP guests. I only have their arrival and departure date.
Is there a way to graph and track the days between so that I can have a total of all arriving and In-house VIP guests on all dates in the range?
Here's a sample of the data.
10/18/201110/19/2011HOERMAN/SCOTT10/17/201110/21/2011SMITH/JOHN M
10/10/201110/13/2011WARNER/AMY10/18/201110/19/2011GERONDALE/RON
10/11/201110/12/2011BUCHANAN/JEFFREY10/17/201110/18/2011HUND/LARRY
10/20/201110/23/2011HADDOCK/CHRISTOPHER10/9/201110/12/2011
BLOOMER/ROBERT10/20/201110/23/2011COLE/ELIZABETH10/7/201110/8/2011IWASA/KENNETH
View 6 Replies
View Related
Apr 30, 2014
calculating project completion % based on Project start date and end dates .
View 5 Replies
View Related
Jun 9, 2014
I need to make a table for an injury category per shift per week. (Falls per shift per week)
I have attached an example of the spreadsheet. I have a formula in the table now that was calculating just the injury type per week but just need to add the function to read per shift but can't seem to get it to read correctly.
View 2 Replies
View Related
Jan 8, 2014
I have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
View 1 Replies
View Related
Mar 18, 2014
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
View 5 Replies
View Related
May 2, 2008
I need to calculate time taken to fix a piece of equipment.
A1 B1
BREAK TIME FIX TIME
4/22/08 23:00 4/23/08 04:00
Should be 5 hours, but i can't find the formula to make it work.
View 14 Replies
View Related
Feb 4, 2010
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
View 5 Replies
View Related
Aug 17, 2009
I have put together the following code (with help from this and another forum):
View 5 Replies
View Related
Apr 19, 2006
I have employees that have different number of business days they work. I
need to be able to calculate when the employee has utilized a specific number
of business days specific to the days of the week they work and the number of
days per week work. For example, if I have an employee that works 3 business
days per week (Specifically M, W, and F), and I need to know the date this
employee worked a total of 30 business days, is there a way to calcuate this
date (which should be 6/9/06 if we use a start date of 4/3/06.
View 9 Replies
View Related
Aug 5, 2008
I need a formula that will allow me to put a date in cell a2, and in cell a3 put the the number of days between 2 dates. Example.. For example (A2) shows 08/06/08, a3 to show the number of days from 08/06/08 to 08/14/2008--(a3 )14 and (a4) to shows the number of days from 08/06/08-10/05/2008 ---(a4) 60 days.
View 5 Replies
View Related
Mar 9, 2009
I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
View 2 Replies
View Related
Nov 4, 2009
So Column 1 I've got dates, need to sort through that and calculate Year-to-date and Month-to-Date values. These are both Sums of the cells....
YTD = Sum of all cells with most recent yr, in this case 2007
MTD = Sum of all cells in Column B for most recent month, Feb2007 here.
I've listed the desired solution for YTD and MTD on the sheet as well. (I'm guessing the solution will have something to do with SUMPRODUCT?)
View 5 Replies
View Related
Oct 26, 2012
I have a column CF which has to calculate the due date based on start date in column X and end date in column Y.
This is achievable however; I have dropdown columns in CE which has below option.
Below is the requirement.
If user select "Should Pay" from drop down in column CE, then in column CF the due date has to be Start date + 7 days
If user selects "Not to Pay" from drop down in column CE, then in column CF the due date has to be N/A or Blank
If user selects "Has Time to Pay" from drop down in column CE, then in column CF the due date has to be Start date + 9 days
If user selects "Misc" from drop down in column CE, then in column CF the due date has to be (End date - Start Date)/8+7
if this can be attainable either via formula or VBA.
View 4 Replies
View Related
Jul 25, 2002
How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.
View 9 Replies
View Related
Oct 23, 2007
I have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?
View 9 Replies
View Related
