Calculate The Time Between Two Points (on Different Days)
Mar 1, 2009
I am trying to calculate the duration between two times on different days.
For example - I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00.
I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.
View 9 Replies
ADVERTISEMENT
Jul 28, 2014
calculate the running total of time spent in the attached spreadsheet.
View 8 Replies
View Related
Oct 5, 2007
I have been trying to write a formula to calculate the differrence in days, hours or minutes between 2 cells in Excel.
Here is an example of the two cells
06/08/2011 00:0006/08/2011 12:19
View 5 Replies
View Related
Jan 21, 2009
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
View 9 Replies
View Related
Jun 18, 2008
Is there a way in Excel/VBA to calculate the width of a text string (in points or inches) with a specific font/font size/font attributes? Right now I have to use a static character count which assumes worst- case character widths for the font (Arial 8 point) but leaves unused whitespace to the right when the text string uses narrow characters.
View 6 Replies
View Related
Mar 29, 2006
I am updating a sports competition ladder. There are two aspects to compiling the table I would like to automate but have been unable to find out how via the excel documentation.
The aspects are.
1. When a team loses by a margin of seven points or less below the total points scored by the winning team then the team is awarded a single "1" bonus point that contributes to their overall competition points tally.
2. When a team scores four tries or more they are likewise awarded a bonus point.
I require formulas that will automatically calculate the bonus points into a cell.
Situation One
cell 1 = Points For (eg. 17)
Cell 2 = Points Against (eg. 24).
In this instance the team did not win. They scored 17 points and the winning team scored 24 points. Therefore the team lost but lost by seven points and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will calculate whether cell 2 is seven or less than cell 1, and if so automatically place a total of "1" in cell 3.
Situation 2
Cell 1 = Tries Scored (eg. 5)
In this instance the team has scored five tries. They have scored more than four tries or more and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will scan the number in cell 1. If this number is 4 or more, then a total of "1" should be automatically placed in Cell 2.
I have looked into the excel help but to no avail and the search terms are so broad it was a nightmare trying to search the forums.
View 11 Replies
View Related
Nov 22, 2013
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
View 7 Replies
View Related
Aug 2, 2009
I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
View 11 Replies
View Related
Feb 17, 2009
I have a file, and I want to put in the standart deviation, over 100 points a time. So =C2:C102, next will be C3:C103 etc, but only if there are values in the Easting column. I ID the columns, but something is wrong about by code, it put's like $ for the row and column.
View 3 Replies
View Related
Jun 17, 2014
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
View 1 Replies
View Related
Sep 21, 2011
I ran across a great article on performing Drive Time Calculations between two addresses. The only problem is that I can not seem to get it to work properly. Here is a link to the article cause I want full credit to be given to the person that created it.
[URL]
Here are the steps I have taken so far.
1. Downloaded Microsoft Internet Transfer Control reference (msinet.ocx) per [URL]
2. Registered the msinet.ocx
3. Selected Microsoft Internet Transfer Control as a Reference in Visual Basic.
4. Copied the custom function for DriveTime into a module.
5. Went to sheet that contained addresses (Column A contains PointA, Column B contains PointB)
6. In C2 I entered the formula =DriveTime(A2,B2)
7. After entering the formula I get a error (#VALUE)
View 9 Replies
View Related
Jan 10, 2013
I have a number of time data elements of different recordings that i would like to be able to use one formula in one cell and have it give me to total time. For example:
00:03
00:55
00:47
00:05
All in one cell (i.e., not having each data point in a cell), I would like to have the formula tell me that these four recording times equal 00:01:48 (i.e., 1min 48 sec).
View 1 Replies
View Related
Mar 15, 2013
I have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.
I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.
This is for excel 2007.
Seems I can't get picture up on the ipad, so the data points look like this
1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30
And so on for an entire year. Just need to remove the ones with the :30
View 12 Replies
View Related
Oct 7, 2011
I am trying to calculate how much time of a range falls between a another range of time.
An example
Swipe times = 7:00 to 16:00
How much of that time is between 00:00 to 7:59
How much of that time is between 08:00 to 15:59
How much of that time is between 16:00 to 23:59
This can be further complicated as the swipe time could be from 20:00 (day 1) to 8:00 (day 2)
View 2 Replies
View Related
Nov 12, 2013
I have a data set that looks like the following.
Description S M T W T F S
Item 1 X X
Item 2 X X
I have a few thousand rows of this. The X's could be placed in any column. I need to go through this data and determine the maximum days between X's for each row. I can't think of how to do something like this with either an equation or with VBA.
I can't get it to line up in the table above. But basically, there could be an X in Sunday and on Wednesday in one row. The next, could be every day of the week, the next could be any other combination. There could be 7 X's, 1 X, 3 X's, whatever.
View 1 Replies
View Related
Apr 22, 2014
I am currently using the following formula which has been working great for what we needed it to do:
=IF(ISERROR(NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1),"Not Completed",NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1)
The formula determine what was been completed on the previous work day. If it fell on a weekend or a holiday specified in another table, it would consider it the following weekday's work. However, the needs of management have changed and they would now like to see anything completed on a weekend or holiday push back to the previous weekday and I'm not exactly sure how to modify this formula to do that.
As an example, work was completed over Good Friday. Yesterday (Monday) when we ran the report, none of those showed up in our list of completed items until today (Tuesday). They would have liked those numbers to be included on Monday's report to get an accurate picture of work that was completed.
I have attached some sample data. Everything completed on the 18th, 19th is counted as 1 day behind but we would like 18th and 19th to be shown as 2 days behind, just like April 17. That way, if we ran the report today and set the filter to 1 (for column C), those items would not show up, but they would have shown up on yesterday's report.
Sample.xlsx
View 4 Replies
View Related
Jan 27, 2010
I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job
Please find attached a sample sheet of where I’m up to.
I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP.
A6:A735 have set dates for two years
D6:D735 have the sick days an employee has taken
B6:B735 Matches the sick date to the date in column A
G2 has how many days entitlement for CSP
H2 has how many waiting days (W D) before CSP can start
In Column C i need CSP, SSP, or W D next to the number 1
And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.
View 14 Replies
View Related
Apr 17, 2013
I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.
Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?
View 8 Replies
View Related
May 13, 2014
1. Find the working days between 15th Nov 09 to 28th
2. Add a column that shows the date after a number of months from today date. The number of months from todays date for each of the country is mentioned in column No. of Months after which revised.
3. Find the date our training will end using excel considering the fact that SAT and SUN are holidays. (Use the date when your training started)
View 2 Replies
View Related
Jul 29, 2014
I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.
View 2 Replies
View Related
Feb 18, 2014
I want to calculate the TAT between multiple date: For example: I have a request I received on 1/2/2014 and I sent to Abhi on 1/2/2014(Same day) and I got a reply on 1/3/2014 from Abhi. I forwarded the same request on 1/3/2014 to Prasad and got a reply on 1/5/2014. And I closed the request on 1/5/2014. How do I calculate TAT on this ? I want the Tat to be shown as 3 days because I just took three days on the request to complete that is 1/2/2014, 1/3/2014 and 1/5/2014..
View 4 Replies
View Related
Mar 26, 2014
I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.
Assuming that B1=0 and C1=1 (in the # of days row)
# of Days 0123456789101112131415Total Files
File Count011712113203000000049
View 8 Replies
View Related
Nov 21, 2008
I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.
View 4 Replies
View Related
Jan 8, 2009
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell.
For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
View 5 Replies
View Related
Apr 17, 2009
In the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number.
I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.
View 4 Replies
View Related
Apr 28, 2013
Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below:
1st 5 yrs - 1 month salary per year
5-10 yrs - 1.5 month salary per year
+10 yrs - 2 months salary per year
But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula.
Name
Hire date
Salary
Total service (days)
X co
Y Co
1st 5 yrs
5-10 yrs
+10 yrs
George
09-02-2005
1000
2883
2822
61
61
[Code] ........
View 4 Replies
View Related
Oct 23, 2013
My sheet looks like this - example:
ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03
I need to calculate the average days between the dates for each ID? How do I do this?
View 4 Replies
View Related
Aug 28, 2008
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
View 9 Replies
View Related
Sep 4, 2008
I have 2 dates a Start Date and an End Date.
I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.
Start End Yr1 Yr2 Yr3
11/01/99 11/01/00 11/01/01
10/31/00 10/31/01 10/31/02
Col A Col B Col C Col D Col E
10/31/00 07/05/01 365 117 0
I thought a series of IF statements but I am open to an easy solution...
View 9 Replies
View Related
Dec 27, 2008
to calculate no.s of workdays between two given dates,
1.We work 06 days a week (so only Sunday is OFF)
2.What if I have some holiday between the given dates + Sunday OFF
View 9 Replies
View Related
