Calculating The Difference Between Cells, But Using The Value Of Another Cell If The

Jun 2, 2009

I have the following on a spreadsheet that keeps track of when student work is handed in and calculates how many days late it is so a penalty mark can be given.

A1= User entered value : a fixed submission date that a piece of work is due to be handed in on.
A2 = User entered value : The date entered if work is handed in on time or early
A3 = User entered value : The date the work was handed in if late
A4 = User entered value : The date the piece of work is due to be handed in if an extension has been granted
A5 = Calculated value : How many days late a piece of work is

Essentially I want the calculation in A5 to reflect how many days late a piece of work is. SUM(A3-A1) would do this of course. If the work is early or on time, A2 is ignored.

Sometimes however students have permission to hand work in late, in which case another calculation is needed to work out how many (if any) days over their extension date (A4) their work is. How do I combine these two calculations into the same cell, so that if A3 is empty it checks A4 to see if the student has an extension date and then returns a 'days late' value if applicable?

View 9 Replies


ADVERTISEMENT

Calculating Percentage Difference Between Cells

Dec 5, 2013

Formula "(Cell-Cell)/ABS(Cell)"

When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me

I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.

Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number

PercDiffExample.xlsx

View 4 Replies View Related

Calculating Difference Pivot Row

Nov 24, 2008

I created a Pivot Table whose columns are different years (2007, 2008). I would like to show the growth (or not) of the percentages from the years. For example

Dept. 2007 2008 Growth

Bio 97% 56% -41%

View 4 Replies View Related

Calculating The Difference In Time?

Aug 17, 2013

I'm calculating the difference in time:

9:54-9:45 = 0:09

Now to convert this in minutes (in number format), I multiply this number with 1440:

0:09*1440 = 9

When I click the cell this number is actually 9.0000005, rather than exact 9. I need it to be exact 9 since I have to match this number from other excel sheet.

To sum up:

This is what I expect:

31.99999 should be changed to 32

10.000001 should be changed to 10.

View 3 Replies View Related

Calculating Difference Between Two Letters?

Nov 14, 2011

Is there a formula to calculate the difference between two letters?

For example, I have levels A-Z. Column A contains level at the start of the year (A-Z). Column B contains level at the end of the year (A-Z). I would like to find the difference (e.g. level A to level D would be a change of 3).

View 3 Replies View Related

Calculating Difference Between Two Times

Mar 29, 2007

I am trying to calculate the difference between a scheduled start time and an actual start time. If the actual start time is greater than the scheduled start time a negative time (hh:mm:ss) should be returned. instead the cell is populated with #############....

I have tried to reformat using the custom formats, but the only options in excel 97 are for either standard numerics or £ (these return a minus figure).

I've had a look at the time functions but could not see a suitable one.

I'm sure there is a simple format solution to this.

View 3 Replies View Related

Calculating The Difference Between Time Stamps?

Dec 12, 2013

I've got a spreadsheet of samples taken at certain time intervals for a period of 2.5 hours, they were collected in hh:mm:ss format. Most of them are approximately 5 minutes apart. What I'd like to do is convert the first time to 00:00:00 and then recalculate each sample from there. Is there an easy way to do this?

So, what looks like this right now:
8:45:00
8:45:36
8:50:36
8:55:36
9:00:36

Would look like this:
00:00:00
00:00:36
00:05:36
00:10:36
00:15:36
etc...

View 8 Replies View Related

Calculating Difference In Time After Midnight

Dec 14, 2013

I'm trying to calculate the available man hours I have for my shift/team. I have got the basic formula to deduct the time, and lost time for breaks ok, but when the shift end time crosses midnight the formula returns a "value" error message. i.e. start time = 18:00. End time = 24:00 breaks = 15 mins gives me a formula like this =TEXT(O17-N17-P17,"h:mm") and a result of 5:45

How I can make this formula work so it can calculate the variance even though the shift ends at 02:00am the following morning?

View 5 Replies View Related

Calculating Difference In Time Using Excel

Mar 7, 2013

I manage a team in a call centre and deal with lots of reports on excel.

I am dealing with a report on a daily basis that i need to use in order to calculate the total idle time that each of agents have. There are roughly 170 agents.

After I have formatted the cells to hh:mm:ss, the obvious thing to do would be : =(B1-A1)

Is there a way to write a macro (or anything) in order to reapply this to the report on a daily basis? Bearing in mind that this has to be done daily for 170 individual reports. They work a 8am - 5pm shift. So there is an entry every time they make a call.

Agent Name
Extension
Initiateddate
TerminatedDate

MSGR_nmasango
9363

28/02/2013 8:06:47 AM

28/02/2013 8:08:54 AM

[Code] ........

View 1 Replies View Related

Calculating Difference In Times As Hours And Minutes

Aug 10, 2009

I need to calculate the difference between a start time and end time in hours and minutes.

Start
01/07/2008 11:40

End
01/08/2008 19:28

Start and End columns are formatted as 'Custom' m/d/yyyy h:mm.

I'm not sure what formula to write to calculate the hours and minutes between the two times. Everything I've tried doesn't count over 24 hours. Also what do I format the result cell as?

View 3 Replies View Related

Calculating Difference Between Two Times Through A Column Of Data

Aug 21, 2013

It will be easier to explain in an example:

A B
Time (hh:mm:ss) Digital
1 10:03:00 0
2 10:03:01 0
3 10:03:02 1
4 10:03:03 1
5 10:03:04 1
6 10:03:05 0

[code]....

From the data above I am searching for the duration of when the digital column says '1', i.e my function = A5-A3 which would output 2 secs or 00:00:03 & likewise A9-A8.

The problem I have is that the digital signal is staggered and does not always have the same frequency. I have over 6848 lines of time to check so to do this manually would take me all day.

View 8 Replies View Related

Calculating Time Difference Based On Row Criteria?

Jul 30, 2012

I am conducting some analysis on alarm data of certain devices and am looking for creating a VBA algorithm that will do the following (refer to the table below for example data)

I want identify an asset in alarm (Column F) and based on its asset ID (Column C) and Fault Description (Column D) then find the row when the asset with the same asset ID and fault description returns to an 'ok' Asset State. After the row where the same asset ID and Fault description is found where the asset state returns to 'ok', I want to calculate the total alarm time based on the difference between when the asset ID and Fault description goes from 'Alarm' State to 'OK' state from DateandTime Column.

I want the result to be populated in a new worksheet that displays data with the following column headings as a result of the above calculations: Date, Asset ID, Asset Description, Fault Description, Alarm Time

I have added an additional column to show the type of output I am after.

There are up to 20,000 rows of data I want to perform the above calculation on.

A
B
C
D

[Code]....

View 2 Replies View Related

Calculating Time Difference Between Two Days Combined With IF Formula?

Jan 30, 2014

I am using the following formula in Column E to calculate the difference between an employees start time, and their previous shift end time in order to work out how many hours rest they have had:

=IF(A4=0,"",IF(I4=0,"",MOD(I4-B4,1)))

My Current Formula works fine for same day calculations, but if an employee finishes work at 18:00 on Friday and starts work at 22:00 Saturday night, Excel calculates the Total Rest hours as 04:00, when in fact they have had 28:00 hours rest...

Is there a way of calculating the hours difference between two dates?

I have attached a sample of my spreadsheet to illustrate

View 5 Replies View Related

Calculating Date & Time Difference: Result In Hours Only

Sep 29, 2006

Instead of calculating a time difference manually I want to have a formula do it for me. What I want to achieve goes as followed:

08/06/2006 04:33
12/06/2006 01:05

Time difference is 92:32 [hh:mm]

another example:

09/06/2006 12:42
12/06/2006 11:35

Time difference is 70:53 [hh:mm]

So instead of getting 92:32 and 70:53 by calculating it myself I would like to have a formula do it for me. Otherwise I'll have to invest a lot of time to get the information I need.

View 9 Replies View Related

Calculating Difference In Months Between Dates / Ignoring Days And Years?

Mar 13, 2012

Cell A1 contains 02/29/2012

Cell B1 contains 12/01/2006

I am looking for a formula that will return the number of months (periods) between two dates, ignoring days and years. Using the above dates, which cannot be changed, the result needs to be 63. The formulas I have tried keep returning 64 because my later date is at the end of a period, and my earlier date is at the beginning.

View 7 Replies View Related

Delete Cells With Difference Greater X With Cell Below

Jan 15, 2008

I would like to have a complete column compared with its immediate next values and delete the values whose difference is more dhan 5.Im new to VB programming.

D

12.90
13.34
19.90
26.90
21.55

If dis is my set of data how should i use range values?

View 2 Replies View Related

How To Make A Cell Display Text Difference Between Two Cells

Aug 30, 2013

Is there a way to make a cell display the text difference between two cells?

For example of cell E2 reads "Thank Goodness it is Friday" and cell F2 reads "Thank Goodness" can I put a formula in H2 that will show the text that is in E2 but not F2 ( "it is Friday")?

View 1 Replies View Related

Find Time Difference Between Two Cells And Present The Date In Third Cell?

Jan 6, 2014

i am trying to find the time difference between two cells and present the date in a third cell. The data in the cells are in a non standard date/time and i need to create a special format i think. The cells look like this.

fldcollected fldaccepted Type Time between being received by database and eccepted

2013-11-06 15:59:29.1002013-11-07 08:41:12.000PSTN

View 3 Replies View Related

Color Cells Based On Difference Between Two Cells

Jun 3, 2008

I have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g.

COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0.

So, I enter the following;

A1 B1

21.0 13.0 <----------------No color Blue

I enter new values

21.0 9.5 <----------------B1 is blue.........................

View 2 Replies View Related

Find Difference Two Cells Within A Row To Another Row?

Dec 3, 2013

I'm trying to find the difference two cells within a row to another row.

I'm using time values i.e 17:07 and 14:53 and in the third cell I'd like to get a result that shows me a plus/minus of the differences.

I know by looking what math to apply to that particular cell. Is there a way to do a formula to get the results no matter if they are plus or minus. without having to change the formula back and for on if i know it'll be increasing or decreasing?

View 8 Replies View Related

Output Difference Between 2 Cells

Mar 25, 2008

I want to compare and output the differences in text in 2 cells.

ie. Cell A1 has a long string of text as does Cell A2 but slightly different, I want to see exactly what the differences are.

Is there a formula I can use?

View 10 Replies View Related

Conditional Difference Between Two Cells

Feb 10, 2010

I have to calculate the difference of two cells in an excel sheet if the first cell value is greater than the second one. If the first cell value is smaller than the second one then the difference should be shown as zero.

View 9 Replies View Related

Finding Difference Between Time Two Cells?

Feb 24, 2014

I'm looking for a formula in relation to the calculation of time.What I'm looking for is a way of finding the difference between the time two cells and it displaying it in the number of hours e.g.:

Cell 1__Cell 2__Cell 3

12:00 + 18:30 = 6.5 (it is 6 hours 30 mins but I want it displayed as a number.

View 5 Replies View Related

How To Compare And Get Difference In Character Between 2 Cells

Jul 19, 2014

Suppose we have 2 cells

Cell 1
Cell 2
O/P needed

abc123
abd123
d123

xyz112
xyz113
3

abc911
axz111
xz111

abc119
abc130
30

abc121
abc122
2

The formula would basically need to check each character sequentially in both cells[starting from the left most character] till there is a mismatch and then display the characters that follow in the second cell (including the character with a mismatch) in the output cell.

it basically looks like this:
ABC1234
ABD1239

I want to get D1239 as the result after using a formula.

Is there a way this logic could be extended to 12 characters?

View 11 Replies View Related

Conditional Formatting - Difference Between Cells

Feb 18, 2014

I have an excel sheet for recording the results from measurements made on round material.

What I want is to have the conditional formatting so that if they have more than the allowed difference in a column they are highlighted red.

Example:

Allowed difference is 0.06 so the cells with a larger difference would be highlighted red.

580.09

View 1 Replies View Related

Summing Cells To Get Difference (subtracting) Only If Both Are Not Blank?

Oct 19, 2012

I have a spreadsheet of clients' body weights each week which goes in a column and to the right of it I have a "diff" which I want to show weight gain/loss.

So for a client with weigh-ins the week of 10/1, 10/8, and 10/15 the columns will be:

10/1(V1)--Diff(W1)--10/8(X1)--Diff(Y1)--10/15(Z1)...and so on.

If I have weigh-ins for each week, no problem. I would just have to: =SUM(X1-V1) to get the change in weight from the week of 10/1 to 10/8. Say the client weighed 200 on 10/1 (V2) and 195 on 10/8 (X2), the difference (or "diff" (Y2)) would be -5.

But let's say the client didn't weigh-in on 10/1. The difference in weight is going to be 195. Or if they weighed-in on 10/1 but didn't on 10/8 it's going to read -200.

So I have two cells (say V2 & X2) that if one of them is blank, I want the "diff" to read "N/A" or at least leave the cell blank.

View 2 Replies View Related

Show Exact Time Difference In 2 Cells

Jul 4, 2012

I have 2 cells in which time is given in format hh:mm:ss , I have differentiated both whatever difference is their between them it get showed in different cell , but my cell is not showing exact time difference its showing up as ######.

View 6 Replies View Related

Compare Two Sheets - Cells Highlight If Difference Is More Or Less Than 5%?

Mar 19, 2014

I have the following code, which works great, but I need the cells to highlight only when the difference is more or less than 5%

how can I adjust for this?

Code:
Sub RunCompare()Dim sheet1 As String
Dim sheet2 As String
sheet1 = InputBox("What is the First Sheet Name?")
sheet2 = InputBox("What is the Second Sheet Name?")
Call compareSheets(sheet1, sheet2)

[code].....

View 3 Replies View Related

Show Rank And Difference In Adjacent Cells

Aug 24, 2007

I"m looking for a formula that searches for sales from a sheet (that already has the sales ranked) and populates that into another sheet. But the 3 cells above/below would have the difference in sales.

Rank
1.
2.
3. Rank3-MySales
4. Rank4-MySales
5. Rank5-MySales
6. MySales
7. Rank7-MySales
8. Rank8-MySales
9. Rank9-MySales
10.

So something like this:

Rank
1.
2.
3. 3500
4. 2700
5. 950
6. $5000
7. -1200
8. -1550
9. -2400
10.

View 9 Replies View Related

Formula To Calculate The Difference Of Time Between Cells

Feb 17, 2008

I'm working in excel2007:

I want to write a generic formula to calculate the difference of time between cells, the first being a real data point, such as

6/22/2007 8:53

minus a generic constant term using the same date and a given time, 8:30.

So, what I need is something like this:

6/22/2007 8:53 – (same mm/dd/yy @ 8:30)
6/22/2007 12:29 – (same mm/dd/yy @ 8:30)
6/25/2007 11:19 – (same mm/dd/yy @ 8:30)

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved