I have a sheet for tracking my poker playing online.
In column A I enter the start time as an Excel Dates & Times and in column B I enter the end time in the same format.
It could look something like this:
Start time End time
2007-01-03 18:20 2007-01-03 18:50
2007-01-03 18:30 2007-01-03 18:45
(Swedish date formatting)
This means I have been playing two tables simultaneously. Now I could get total time in "table hours" using SUM Formula:
=SUM(B:B)-SUM(A:A)
which in this case would result in 45 minutes.
But I also would like to calculate effective playing time so if I play 10 minutes on two tables simultaneously it will only count as 10 minutes and not 20 minutes.
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 .
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?
Here is the effective rate calcs on a random normal amortization loan:
this is in B2, and answer is 7% =RATE(B4*B5,-((B3+B7)/B6),B7)*12
B3 = Total*Interest 279017.8 B4 = #*Years*in*Loan 30 B5 = #*Payments*/*Year 12 B6 = Total*Payments 360 B7 = Beginning*Principal 200000 B8 - Ending*Balance 0 problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan.
Here is the example I'm working on... A client's loan is the following: Loan amount - 131,538 interest rate - 6.15 30 year amortization 10 years interest only
normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
My problem is trying to identify the applicable rate in a range of dates that are not consistent in every case. I have a number of orders that span 4 years. The rate charged has changed over time and therefore I'm trying to find what the applicable rate would be for that time frame. For example one of my orders was created on 2/27/2012 with a specific item, then again the same item was ordered on 9/10/2013 and I need to find what the rate should have been for both of those orders during those rate periods. I've attached a sample sheet with the 2 tables I've been trying to assess. I've tried using mulitple IF and VLOOKUP formula's but it doesn't work they way I need it to due to the inconsistency in Table 2. H
I am wanting to calculate the the processing time for an order that takes place within normal business hours and workweek. A normal day is from 8:00 to 5:00 If a task is started at 2:00 PM Monday and finished at 10:00 AM Tuesday then the result should be 5 hours as I do not want to include any time outside of normal hours. I can figure out how to subtract dates and times but not how to bridge a day(s). My data is somewhat flexible as I have not started the project yet. I can use separate cells for the times and dates or have two cells that use both incorporate the date and time (8/27/09 2:00 PM) for start and end time or any other idea.
I have a userform that time stamps on my userform as soon as i open the form, is there a way that when I submit, that the amount of time that I was on the call to be put in my column on my worksheet as minutes?
I have an excel file with the ID, Name, workID and Creation date fields. Some people have several work ID's created in different times. How can I select row that contain work ID's that were created the latest. I may have several records per person, I need only the latest record. File contains about 3000 records, I can't go through all of them.
By the way ....D1 = start Date F1 = End Date E1 = start Time G1 = End Time
It's GREAT! But now I need include time from 6:00 a. m. to 10:00 p. m. (I already modified your formula:
(NETWORKDAYS(D1,F1)-1)*("22:00"-"6:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"6:00","22:00"),"22:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"6:00","22:00")) because are regular working hours in Colombia but:
1. All the days (Monday to Saturday) are working days 2. I just want to calculate the time in shifts, I don't specify the start date and end date. Example: 8:00 p.m. to 11:00 p.m. Result: 2 hours, because 1 hour is after 10:00 p.m.
This are two different options, so I need two separate formulas.
I have to calculate the difference between the start time and end time of a job. The only catch is, how can I avoid calculating "out of hours" time. So, if a job goes from 9am to 9am the next day, I want it to avoid calculating between the hours of 23:30 and 03:30.
Another example is if a job goes from 02:00 to 04:00, I want it to avoid the tim between 02:00 and 03:00.
If there is a difference in days, so the job goes overnight, how do I take that into consideration also.
I've got a time difference from 8:00AM - 12:30PM as 4.30 I'm trying to get the minutes, .30, converted into a 6 minute increment, .5. Is it possible to do this and if so how would it be done? Below is a chart of how the time is converted from 6 minutes increments into decimal form.
I'm trying to devise a formula to produce "days in inventory" based on the following data:
Date In Date Out (which may be blank if cargo still here) Todays Date Days in Whse (which is the formula I can't figure out!)
It needs to work like this, date out - date in, unless date out is null. If date out is null, the result should be calculated based off of todays date - date in.
the vendor has a 21 hr working window; start from 7am and goes until 4am; Mon to Fri.
Here is a scenario: - i request for a product information from a vendor on 3-Feb-14 8:00am (Monday) - he replies with all of the product info on 6-Feb-14 12:00pm (Thursday)
can you find the time in above scenario consideration the working window?
Here is another scenario: - i request for a product information from a vendor on 6-Feb-14 8:00am (Thursday) - he replies with all of the product info on 11-Feb-14 12:00pm (Tuesday) - Sat & Sun are days off but keep in mind that my Friday shift ends on sat at 4am so the networdays formula wont work.
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.
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D.
how to find the average times of two different sets of groups. There are the baseline times and intervention times. Both groups have three subtopics: Time Fell Asleep, Woke Up, and Duration (how many hours I slept). I am not sure how to find the average of each subtopic. Here are the times....
I have a problem with a time card i am producing. I want to count hours and minutes worked and carry forward any surplus or deficit into the next week. I can do it if the hours do not total more than 24. I have attached the file and it is formatted in 1904 format.
I am staring at this excel spreadsheet blankly ... I am at a loss as to how to accomplish the detailed analasys but simple math this spreadsheet reequires.
The data consists of three columns pasted from a report that is exported as a .csv:
B Code C Start Time D End Time
The following analyses must be made on each row:
1. determine if the start time falls within the scheduled shift
1. User enters a start and end time Start1, End1 (23:00, 01:00) 2. The time difference is calculated and displayed in Total1 (2:00) 3. User enters Start2, End2 (22:00, 22:50) 4. Time difference calculated - display in Total2 (00:50) 5. Grand total of Total1, Total2 is displayed in GrandTotal (2:50)
The user will input the times as 0000 but I need to convert the entry into a time format. (User enters 1235 - I need it to covert to 12:35)
I have a calendar. All months across the top, formatted as date mmm with content 1/2 2/1 3/1 4/1, etc. so the cells display Jan Feb Mar Apr, etc. All days down the side formatted as number displaying 1, 2, 3, 4, etc. All the days of the year are formatted as time h:mm.
I want to create a cell that shows me the total time for the last 14 days.
1) Current Speed 2) Current Acceleration 3) Acceleration Growth
Assuming: Current Speed=0 Current Acceleration = 0.2 (each 'turn' the current speed will increase by this much) Acceleration Growth = 0.2 (each 'turn', the current acceleration will grow by this much)
This gives a current speed over a series of 'turns' as 0.0 + 0.2 = 0.2 0.2 + 0.4 = 0.6 0.6 + 0.8 = 1.4 1.4 + 1.0 = 2.4 2.4 + 1.2 = 3.6 3.6 + 1.4 = 5.0 5.0 + 1.6 = 6.6 6.6 + 1.8 = 8.4 8.4 + 2.0 = 10.4 etc.
What I'd like to do is have a formula (or some way other than calculating each step) to tell me how many turns it would take for the Current Speed to =>X (example 100)
Basically, Turns to X speed = something clever * acceleration growth * something else very clever.
I want to be able to calculate a due date two days from an entered date/time. If I input 9/3/2009 3:00 PM into cell F6 I would like cell G6 to display 9/5/2009 3:00 PM. The formula that I am currently using includes a holiday list so if the due date lands on a day within that list it gets pushed out.
My formula is =if(F6="","",Workday(F6,2,Z1:Z144)).
Basically when F6 gets populated G6 is supposed to populate with a due date of two days later (same time of the day). Currently if my input date/time is 9/3/2009 3:00 PM the formula is displaying a due date of 9/5/2009 12:00 AM.
I need to find the average time it takes students to take exams . I use the following formula =text(end time - start time, "h:mm"). I am able to calculate the amount of time it takes a student to take the exam. Now I need a formula to calculate the average time students take to complete a test. I have over 80 times i need to average. Whenever i try a formula I keep getting 0.
It is quicker and easier for me to enter times as whole numbers.
I tried to Catinate the number and enter the ":" in the middle. Example I enter 815 and then =":" &RIGHT(a1,2) and get :15 but how do I add the 8 and convert the new number to time?
I have data changes regularly (Say like tank level and we wil asume that at time 2:00 AM is the base , no matter what is the value) and I want to recored the that change every two minutes along with the time as list. (i.e. if there is no change nothing will be recorded)
