Free Microsoft Excel 2013 Quick Reference

Graphing the difference between two lines as an area

I'm looking to automate the process of making a chart that highlights the difference between two lines on a chart. I have figured out how to denote the area between lines using an area chart, but I'd like to have the area between the two lines be a different color if the delta is positive or negative.

I've attached a simple example, which I made by doing a line chart and drawing the areas between them using the free form shapes tool. This is not too time consuming, but would have to be redone every time the data changes. I'm hoping somebody knows a better approach.

Thanks


Post your answer or comment

comments powered by Disqus
hi

I'm wondering if there is any way to work out the difference between two lines or series on a chart whatever the chart type used..

eg. two dollar series mapped.. how to tell the difference bwteen the two, this would be very useful information in some instances ..

I need a way to subtract the difference between two dates and exprss the
difference in a format simular to 10 years 6 months 7 days. Assume a start
date of 12/19/1943 and an end date of Today(). I have no trouble using
datedif to get the number of years as 61 years, months as 11. I have now
idea of how to convert the total number of days between the two dates to the
correct number days. sngNumDays = DateDiff("d", DateOne, Now) gives the
total number of days (22657).

My current Code:

Private Sub cmdAge_Click()
Dim DateOne As Date
Dim intYears As Integer
Dim intMonths As Integer
Dim sngNumDays As Single
Dim introw As Integer
For introw = 2 To 4 '3 different dates are
entered on rows 2 through 4
DateOne = Cells(introw, 13).Value 'get the start date from
the spreadsheet
sngNumDays = DateDiff("d", DateOne, Now) 'total number of years
in the period
intYears = Int(sngNumDays / 365)
intMonths = DateDiff("m", DateOne, Now) 'months is total months
in the time period
intMonths = intMonths - (intYears * 12) 'convert to months
difference in the current year
sngNumDays = DateDiff("d", DateOne, Now) 'total number of days in
the period
Me.Unprotect
'print the results to the row and column on the spread sheet
Cells(introw, 14).Value = " " & intYears & " Years " & intMonths &
" Months " & sngNumDays & " Days"
Me.Protect
Next
End Sub

I need a way to subtract the difference between two dates and exprss the
difference in a format simular to 10 years 6 months 7 days. Assume a start
date of 12/19/1943 and an end date of Today(). I have no trouble using
datedif to get the number of years as 61 years, months as 11. I have now
idea of how to convert the total number of days between the two dates to the
correct number days. sngNumDays = DateDiff("d", DateOne, Now) gives the
total number of days (22657).

My current Code:

Private Sub cmdAge_Click()
Dim DateOne As Date
Dim intYears As Integer
Dim intMonths As Integer
Dim sngNumDays As Single
Dim introw As Integer
For introw = 2 To 4 '3 different dates are
entered on rows 2 through 4
DateOne = Cells(introw, 13).Value 'get the start date from
the spreadsheet
sngNumDays = DateDiff("d", DateOne, Now) 'total number of years
in the period
intYears = Int(sngNumDays / 365)
intMonths = DateDiff("m", DateOne, Now) 'months is total months
in the time period
intMonths = intMonths - (intYears * 12) 'convert to months
difference in the current year
sngNumDays = DateDiff("d", DateOne, Now) 'total number of days in
the period
Me.Unprotect
'print the results to the row and column on the spread sheet
Cells(introw, 14).Value = " " & intYears & " Years " & intMonths &
" Months " & sngNumDays & " Days"
Me.Protect
Next
End Sub

Dear Experts,

While using the datedif function to find the difference between two dates in months and days i am getting error value comparing to the manual calculation login. Is there any other function to find out the result as per the desired requirement. Pls find the example below.

Manual Calculation Using Datedif Formula
Start Date End Date Month Days Month Days Difference
3/12/2007 8/1/2008 16 21 16 20 -1
1/10/2006 8/1/2008 30 23 30 22 -1
6/2/2006 8/1/2008 25 29 25 30 1
4/2/2006 8/1/2008 27 29 27 30 1
2/3/2005 8/1/2008 41 26 41 29 3
2/2/2007 8/1/2008 17 27 17 30 3
11/1/2007 8/1/2008 9 0 9 0 Correct Value
11/1/2007 8/1/2008 9 0 9 0 Correct Value
11/1/2007 8/1/2008 9 0 9 0 Correct Value
11/1/2007 8/1/2008 9 0 9 0 Correct Value
12/25/2004 8/1/2008 43 7 43 7 Correct Value
10/25/2004 8/1/2008 45 7 45 7 Correct Value
10/25/2003 8/1/2008 57 7 57 7 Correct Value

formula used DATEDIF(A3,B3,"M")
DATEDIF(A3,B3,"MD")
Hope some one can solve this.

Hi - I have been carrying out an analysis of which method is the best one to use in order to work out the difference between two dates in years. I have a formula that I think works better than yearfrac and better than deducting the 2 dates and dividing by 365.25.

I would be most grateful if some of you could test this formulas accuracy and confirm that it is indeed the best way.

Here goes (it rounds to 5 dps)

=ROUND(((YEAR(B4))-(YEAR(B3))-1)+((B4-(DATE((YEAR(B4))-1,12,31)))+((DATE((YEAR(B3))+1,1,1))-B3))/365.25,5)

Where B3 is the start date and B4 is the end date... Let me have your thoughts please - any input would be much appreciated.

Date360 gives an inacurate answer that over the course of 20 years can be
over a month. Is there an accurate way of computing the difference between
two dates?

I need to show the difference between two dates. Someone signed in on Jan 4,
2005 and today's date is the current date. How do i show that and then put it
into a Word document for a mail merge to show each person's enlist date?

Hi, I'm trying to create a tracking spreadsheet to keep track of important employee information. I'm attempting to calculate the difference between two dates (their hire date and the current date), while keeping the cell blank until their hire date is input.
Currently I have:

=YEAR(S2)-YEAR(P4)-IF(OR(MONTH(S2)

I need to show the difference between two dates. Someone signed in on Jan 4,
2005 and today's date is the current date. How do i show that and then put it
into a Word document for a mail merge to show each person's enlist date?

Help me please!
How would i calculate the difference between two times if hours exceed 24.
For example, start time is 10:55, finish time is 01:20. What formula i have
to use?
Thank you

Morning,

Having a few problems, I'm trying to work out the difference between two columns, so usually I would use:

=A2-B2

However the values in these columns range from:
N
B
3
4
5
6
7
8
EP

The value for N needs to be 4 and the value for B needs to be 2, and EP needs to be 9

Any ideas how I can do this?

Thanks for any help you can give

Hello,

I need to calculate the difference between two cells for an entire column. We have a total sales amount and deposit amount. I need the balance calculated in the next column for each sale.

On the back of that, I need the totals to do the same at the bottom of the spreadsheet.

I have attached an image for clarification.

Many thanks in advance for your help. I know I have been hammering the forums with newbie questions....but I really do appreciate your efforts to help!

Kind regards,

Dave

This is an age old query, I received help some months back on calculating the difference between two dates. There remains one problem:

What I have at the moment:
I have a cell called CURRENT and one called TARGET.

Current has todays date and target has 29/07/05.

I have a cell called TO GO which has the formula = Target - Current

The cell is formated in the following: yy mm dd hh:mm:ss

There is VBA which updates NOW so that I have a countdown.
(Dim SchedRecalc As Date


	VB:
	
 SetTime() 
    SchedRecalc = Now + TimeValue("00:00:01") 
    Application.OnTime SchedRecalc, "Recalc" 
End Sub 
 
Sub Recalc() 
    Range("Current").Value = Now 
    Call SetTime 
End Sub 
 
Sub Disable() 
    On Error Resume Next 
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False 
End Sub) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All this works fine.

The Problem:

The result in the cell TO GO is not accurate. It does not show the exact number of days to go. For example if Current = 29/07/04 and Target = 29/07/05 I want to see TO GO showing that there are 12 Months and 0 days to go.

Any ideas why the DAY part is not giving the answer I am expecting?

MZP

I have a pivot table set up in that it displays data with values before and
after an event. I would to be able to calculate the difference between the
two values in a separate column within the pivot table. Does anyone know how
to do this or if this is possible? It is using Excel 97 I have tried using a
calculated field but it does not return a sensible answer

Help I am been driven mad!
Does anyone know how to find the difference between two time stamps giving
the result as a time decimal.
I.E.
16:45 - 12:00 = 4.75 hrs

Hi
I'm trying to calculate the difference between two dates and times and returns the answer in days hours mins for workdays only. I've seen similar threads but nothing seems to be quite right or I can't get to work

E2 contains date/time call raised in custom format dd/mm/yyyy hh:mm
M2 contains date/time action taken in custom format dd/mm/yyy hh:mm
on another worksheet I have a list called "holidays" that will have all the public holidays

I basically want AA2 to show M2-E2 in days & hh:mm only or even just hh:mm for working days only assuming a standard 5 day week with standard working hours (I have not specified working days or working hours anywhere). Also what format should cell AA2 be set up as?

Oh and if M2 isn't filled in yet I need the cell AA2 to remain blank.

Help would be gratefully appreciated
Tammy

I have looked for this but im not sure what to call the method. I need a formula that calculates the difference between two times and give the result as follows.

Example 1

2:00 AM - 5:00 AM = 3.0

Example 2

2:30 AM - 3:00 = 0.50

Eample 3

2:15 AM - 3:00 AM = 0.75

I need it to show a period style answer and the range from 0.0 to 24.0 depending on the times of course any help would be great.

Fellow Forum Members,
Calculating the difference between two times is easy to do in Excel when only two columns are involved such as:

Column A Column B
StartTime EndTime
---------- -----------
1/3/11 4:00 PM 1/6/11 2:00 PM

A formula like: (B1-A1) calculates the difference.

However, how does the formula look like when instead of two columns you have four columns? My START time has two columns. One for the Start DATE (i.e. 1/3/11) and a second for the Start TIME (i.e. 4:00 PM). Then I also have two columns for the end time. One column for the END date and a second column for the END time. I would appreciate it a lot if some one can help me develop a formula that will calculate the difference between two times when four columns of time related data are at play. Thanks in advance.

I'm having difficulty getting Excel 2003 to subtract times. It seems to me that this should be easy, so probably I'm just making a mistake.

In Col A I have my arrival time (eg. 08:30)
- formula bar shows 08:30 AM

In Col B, I have departure time (which *might* be after midnight, but let's keep it simple for this example), eg. (23:00)
- formula bar shows 11:00 PM

I want to see the difference between the two times, which would be fifteen-and-a-half hours.

So, in Col C, I want a formula that will show Total Time On Site in Hours.Fraction-of-hour:

=B1-A1

Excel returns "0.3"

=Text(B1-A1,"hh")

Excel returns only "15" (not 15.5)

=Text(B1-A1,"hh.mm")
Excel returns 15.30 (not 15.5)

Q1. Advice on this problem?
Q2. Any thoughts about how to handle time differences that go beyond midnight?

Hi

I have an excel worksheet that has two date columns.

I want to calculte the difference between two dates and express this in Years and Months (without days shown).

I can already calculate the total of days between the two dates but thats no good for me.

So, 31 March 2007 minus 31 January 2006 would need to equal "1 year 2 months".

Look forward to hearing from you.

My pivot table is summarized by group, then by Actual Headcount and Budget
Headcount. So for example, HR is the first category broken down into 10
heads of budget and 9 heads of actual. The Total line sums the Budget and
Actual total to show 19. I would like the Total line to show the difference
between Budget and Actuals. So instead of the sum of 19, I would like the
pivot table to calculate the delta (Delta would equal 1: Budget of 10 -
Actual of 9). I would like it to do this for each group (HR, Sales, etc.)
Any thoughts?

Need to calculate the difference between two cells in a column. Exemple

A B C
Start 1 date/time
open date/time
: date/time
: date/time
: :
Start2 date/time =date2/time2 -date1/time1 (HH:MM)

I can select two consecutive "Start cells" with "IF"s, but the max of "IF"
supported by Excel is 7. I have no solution if there are more than 7 lines
between tho consecutive "start"s (or any other like "open" etc..)

I'd be glad to get some help.

68magnolia71 (in france)

I have tried many different formats and formulas and the worksheet will not
calculate the difference between two times formatted as follows.

6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE!
B8 C8

the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!!

I have read the Excel help and it did not help.

I have a simple form I am working on, but I am having issue showing the difference between two times.

Say the start time is 13:58 and the end time is 15:27 for example I need a formula to show how much time was between those two times.

Attached is an example of what I have started, I thought a simple X2-X1 woud work but no luck.

Thanks for any help.


No luck finding an answer? You could always try Google.