What formula should I use if I have a list of maturity dates and current investments amounts as of 06/30/07:
06/30/08 10,000
08/31/14 18,000
05/31/08 11,000
07/31/09 25,000
06/30/10 14,000
12/31/25 80,000
and the amounts need to be distributed in columns titled:
Within 1 Yr (From 7/1/07 to 6/30/08)
After 1 to 5 Yrs (From 7/1/08 to 6/30/13)
After 5 yrs (After 7/1/13)
production01.png
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
Excel 2007
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
I'm looking to analyse is how long these articles take to reach maturity from their date of publication (so, when they achieve their optimum performance).
I'd then like to further analyse this across various metrics like article type/producer etc., which, I guess, would be in some kind of Pivot Table filter-type-thing.
Here's an example of some of my data:
ArticleName - PublishedDate - OctViews - NovViews - DecViews - ArticleType - Producer
Article1 - Oct-08 - 3701 - 4856 - 4561 - Food - ABCTV
Article2 - Nov-08 - 0 - 9824 - 1256 - Food - ABCTV
Article3 - Oct-08 - 1358 - 8953 - 9652 - Tech - XYZ.com
Article4 - Oct-08 - 1351 - 2354 - 3562 - Tech - ABCTV
The part I'm finding tricky is this; what we'd like to do is rather than look at a specific date (say, articles published on 09/04/08) it would be useful to combine all the dates so that the first month something is published is Month 1, regardless of the date it is actually published.
As the same article can be from the same producer but published at different times, if I could remove the "Jan-Mar = Month 1-Month 3" problem it would allow us to graph all the data into one snapshot of the publishing process and find trends. If this could then be cross examinable by the ArticleType etc. it'd be really useful.
My lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.
View 2 Replies View RelatedI import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd
I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
I got the following table.
http://img163.imageshack.us/img163/2972/oldnf.jpg
What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?
http://img687.imageshack.us/img687/5720/newcg.jpg
http://img168.imageshack.us/img168/413/new1zk.jpg
I'm using this formula (below) to add the hrs between 2 time periods (4:30 pm in cell I37 and 8:00 pm in I38, then the formula is in I39) and works almost every time even when the second time goes into the am hours, except in certain instances such as: 4:30 pm to 11pm show as 30.5? But sometimes it shows the correct number, 6.5
=(IF(I37>I38,I38+1-I37,I38-I37))*24
I need to produce a graph that provides the user with opportunity to define the time period, i.e. start and end dates for the data to be used in the graph.
View 2 Replies View RelatedI have a source table that contains time information about shifts people work:
E
F
G
H
37
Source Table
38
name
date
start-time
stop-time
39
John
1-1-2012
13:00
15:45
40
Frank
15-8-2012
8:45
10:30
Now I'm looking for way to 'enhance the resolution' of this information to an hourly basis.
This means I would like to know how many hours John worked on 1-1-2012 between 15:00 and 16:00 (0.75).
I probably need to add 24 calculated colums to the table corresponding with the hours of the day.
I have a spreadsheet that holds a list of customers and the contracts they have with my company - this sheet includes the Total Contract Value in £s - so how much each customer will pay us for the service - the period of the service - so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months.
Initially I had complicated formulas that simply divide the value by the number of months of the contract - however, my company has since changed the policy and requires it to be done to the day.
For example, if the value was £12,000 and the period was 12 months from 01/01/08 - to calculate January 08 it would be - 12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on...
Does anyone know how I can do this - bearing in mind that each contract won't be as easy as 12 months - it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month - I would be very very grateful - as always... [/img]
I am trying to plot two data series in one chart. The first series has monthly corn prices for last 10 years (~130 data points). The second series has bimonthly chicken prices over the last 10 years ( ~72 points). How can I plot them together?
The bi-monthly periods for the second series do not exactly coincide with calender months ( e.g. Aug 19 to Oct 13, 2007). As a result, I can not adjust the points to match with calender months.
There are 2 seperate spreadsheets
S1 - "End"
No. | ID | Name | Login | Total | Jan-07 | Feb-07 | March-07 | => | Dec-07
S2 - "Datasource"
ID | Irrelevant Name | Period | State | Irrelevant Data
I need something to go through the unique IDs in the "Datasource" & find a match in the "End" spreadsheet & then insert the number "1" in the corresponding time period (matching Period with Jan-07 or which ever it's suppose to represent). The Period is written in the following format:
122007 for December 2007
12008 for January 2008
One thing, the datasource has dates for both 2007 & 2008. The end spreadsheet has 2007 in one worksheet & 2008 in another.
I am currently using this formula to count dates between 2 date periods If specific condition exists.
=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"
how to Make a forecast for the demand for the time periods shown....
View 9 Replies View RelatedI am attempting to create a formula that will count the number of times, lets say letter A, occurs in column E. However, the tricky part that I have been confused on is that I only want to count how many times A occurs between each hour of the day. I need to count number of times "A" occurs between each hour of the day for the entire month.
My Worksheet looks as such: Column A contains the dates for the month of November, Column B contains times that random instances occur, Column C is not important, Column E contains a Letter A-N that pertains to what occurred at a time in column B. SO, Column B and column E coordinate with each other.
Here is an example. Column A - Cell A1 - 11/01/2013 , A2 - 11/02/2013, A3 - 11/03/2013.
Column B - Cell B1 - 12:01 AM , Cell B2 - 14:03 , Cell B3 - 15:23
Column C - Cell E1 - A , Cell E2 - A, Cell E3 - B
So I would like Column F to display that between 12:00 - 12:59 Am there was X number of times the letter A occurred throughout the entire month.
So I have two dates:
Opening Date: 29/07/2014 13:27
Closing Date: 29/07/2014 14:42
These are formatted in DD/MM/YYYY and HH/MM
I need to subtract the dates and find the time taken from the opening and closing time.
I have 4 distinct columns.
Adm Date Adm Time Trans Date Trans Time
1/16/2014 937 1/16/2014 1045
1/1/2014 121 1/1/2014 121
1/14/2014 800 1/11/2014 735
1/30/2014 100 1/30/2014 205
1/13/2014 800 1/12/2014 1202
I would like to calculate the difference (# hours spanned from the dates shown). Unfortunately when we transfer the data the COLON is dropped from MILITARY TIME so I am having problems in the calculation. This is also made more difficult as some of the calculates span over two dates.
time variation between 2 dates including time (Exclude weekends and holidays), but include the worked time of weekend, if the end date and time falls under weekend or holiday.
Eg., A issues reported on 01Jan2014 12:00:00 PM and Resolved on 11Jan2014 12:00:00 PM
There are 3 weekends in the above mentinoned date - 5th, 6th and 11th
I can eliminate the weekends using the networkdays formula, but however in this case I need to eliminate 04th and 5th Jan (weekends), but Consider 12 hours from 11Jan2014(which is also a weekend), as the issue got resolved on Saturday afternoon, assuming for critical cases team works irrespective of holidays.
I need to calculate the time spent replying to my inquiries: I log the time I receive my inquiry as: 06/02/2009 09:23:00 in column A. I log the time I send my reply as: 07/02/2009 07:23 in column B. In column C, I need to put the Formula that will return the following result: 0 days 22.0 hours.
View 5 Replies View Relatedmy problem is that I need to work out the time between two date/times. The first date is imported into the spreadsheet as a line of text, which looks like this :-
Sent Aug 18,2006 20:08
I then want to work out the difference in minutes from then until the current time.
I have tried using mid, left and right statements to get all the individual aspects of the date, and then using concatenate and date, but couldn't suss it.
Formula query
Column “A” list of dates correspondence in
Column “B” list of dates correspondence reply out
Need to calculate how many times the difference between date in and reply date is <= 5 in a specific time period.
The start date is entered in say, “K1”
The end date is entered in say, “L1”
Also calculate difference in dates between 14 days and 21 days for time period
Also with reply in column “B” not sent until over 28 days. I have tried SUMPRODUCT with (date_in=K1)*(date_out<=L1) for the dates but unsure how to define <=5
I work at a call center and I get the data as Date, Time of calling and Duration of the call. Now since this is an overseas call center, even though the calls spills over 12midnight, the corresponding date shows as the date when the agent logged in.
So I get the data in this format, Date: 06/11/2012, Time: 19:00:00 - 04:00:00.
How do I calculate the time difference at one go without first getting the time difference from the time of login till midnight and then add that to the time difference of midnight till time of logout. I have attached a file for your review.
I am trying to subtract 45min from the time 00:05, but the result i am getting is 0:40 which is wrong.
I am using below formula.
=TEXT(ABS(B3-$A$3),"-h:mm")
Actual timeTime minusResults00:0500:45-0:40
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"
I have table which looks like this:
Name of School Time1 Time2 LoggedIN
2436 DAVIS JH 11/10/08 8:20 AM 11/10/08 8:30 AM 1 2436 DAVIS JH 11/10/08 8:30 AM 11/10/08 8:40 AM 1 2436 SVM JH 12/11/08 7:00 AM 12/11/08 7:10 AM 1 2436 DAVE JH 12/11/08 7:10 AM 12/11/08 7:20 AM 2 2436 CRAW H 12/11/08 7:20 AM 12/11/08 7:30 AM 2
Total Number of Columns = 4
First Column = School NAme
Second Column = Time 1
Third Column =Time2
Fourth Colum = Logged In
I want a 5th column where i can get the following result:
If time in 3rd column is betweeb 8am and 9 am and time in the 4th column is between 8Am and 9 Amthe thee corresponding row in the 5th coulmn should be 8AM-9AM
If time in 3rd column is betweeb 9am and 10 am and time in the 4th column is between 9AM and 10 Am the thee corresponding row in the 5th coulmn should be 9AM-10AM
Likewise for each hourly time slot of the day
The time columns are in the "DATE" Format!
The Purpose is finally to get the number of students logged in each hour of the day,and finally draw a usage hour graph by school and time !
I thought this would be simple but I can't figure it out. I need to subtract the time between two cells. For example:
Cell A1: 6/6/2007 3:00:00 PM
Cell A2: 6/7/2007 2:00:00 PM
Result would be either 0:23 or 23 as in hours.
How to get the Hours of two different time
A1
27/06/2014 11:30
B1
30/06/2014 18:14
C1
Ans: Hours of these 2 dates with time.
i just need to get their Hours.
I would like to create an average function that will take an average of the Column labeled "Gap Time (Hours/Min/Sec)". I only want it to take the average for this for each new start date. These values will change daily so I was hoping that someone may help me write a function that will work when data changes instead of manually taking the average every time data is entered.
View 5 Replies View RelatedAttached a workbook of what I see. On column A and B there are dates with time on a "m/dd/yyyy hh:mm:ss" format.
Until a while ago excel 2010 would recognize the dates and I was able to find the time difference between the 2 just by typing =B1-A1 (column C)
I think due to a Microsoft update (?) I cannot do it anymore and excel will not recognize the info?