# Calculate Difference Between Dates In Two Columns?

Mar 4, 2014
I'm just trying to calculate the difference between dates in two columns and applying conditional formatting based on if it is a positive or negative number. But when there is a null value in either of the date columns I don't want anything returned.

So far I'm either getting a 0 or a FALSE.

Column A = Date 1 Column B = Date 2 Column C = Formula

=IF(NOT(ISBLANK(B2)), B2-A2, )

View 2 Replies
ADVERTISEMENT
Jul 27, 2009

I need to calculate the amount of time between two dates, and show it in the number of hours and minutes. My dates are formatted to show the time in Zulu time. So 1406 = 2:06PM.

Here is what I have:

A1 = 6/4/08 1406Z

B1 = 6/5/08 0402Z

For this example, the formula should return a result of 13 hours and 58 minutes. With the result, there is no need to desingate Zulu time with a 'Z'. But I need the result in a format that will allow me to calculate an average for all my data.

View 9 Replies
View Related
Mar 28, 2012

I have 2 columsn with data that looks like this:

Creation DateResolved DateDifference in days7/1/2011 4:221/9/2012 14:587/1/2011 8:567/1/2011 13:517/1/2011 11:438/3/2011 17:157/1/2011 11:597/14/2011 14:077/3/2011 22:477/5/2011 11:037/4/2011 1:399/15/2011 15:207/4/2011 8:367/5/2011 9:357/5/2011 7:047/6/2011 9:107/5/2011 9:177/6/2011 10:007/5/2011 10:471/9/2012 14:37

I am trying to figure out what formula to use to calculate the difference between the creation date and resolved date in hours and days. That is, In once column I would like to see the difference in 'hours' and in another column I would like to see the difference in 'days'.

View 3 Replies
View Related
Sep 11, 2008

This is more of a query on date dif

I was trying to calculate the difference between two dates on excel

I had a look through the search message board and searched the web generally where I came across the datedif function

I had found that in order to get the number of years it was

=datedif(startdate,enddate,"Y")

for months same formula"YM" and for days use "MD" in place of "Y" in the original formula

My question is when using datedif to calculate the number of days between two days it initialy gave me the right answer

Number of days between 01/08/2008 - 22/08/2008 - Answer 21 days

Using date dif gave me 21 days but when i did

01/08/2008 - 01/09/2008 - this gave me 0

Am I entering the formula wrong or does datedif simply not pick the difference if the days havent changed even though the month has ?

I then found i could simply minus one from the other , which then had me asking what is the point of date dif

View 9 Replies
View Related
Jul 31, 2008

is anyone aware of a formula/macro that can calculate (in days, hours and minutes) how much time has elapsed between two date and time stamps?

For example [30/07/2008 00:30] and [01/08/2008 01:45], so in days hours and minutes it would look somewhat like this: 02:01:15

View 7 Replies
View Related
Apr 4, 2014

Example.xlsx

how to calculate the difference in value of an item between two dates on a pivot table. I have one table that feeds the Pivot Table and it has three fields:

Data as of Date

Item

Value

So each day, the Data as of Date is updated to the current day and the items are the same but the values are different. I need to calculate the difference in value between the most recent date and x days ago. I cannot figure out how to do this via Pivot Table.

View 5 Replies
View Related
Jan 25, 2012

From the below example, I would need to know the difference between date & time of Received and Resolved in number of days. Meaning - time difference should also be calculated in days

CELL ACELL BCELL CCELL DCELL E - REQUIRED OUTPUTReceived

DateReceived

TimeResolved

DateResolved

TimeTurn Around Time - IN DAYS20-Jan-122:43:00 PM23-Jan-124:50:00 PMDifference between Resolved date & time and Received date & time

View 7 Replies
View Related
Aug 21, 2006

I have two Rows of data. Each row contains a unique Name column and separate columns for Date, Hour and Minute. I would like to calculate the Time difference in Days, Hours and Minutes between the two Dates. I’m not sure if the way I’ve set it up is the most practical. I’ll attach the spreadsheet to better explain.

View 4 Replies
View Related
May 14, 2014

I have a pivot table and I created a formula that says:

=jobCompletedDate-jobCreatedDate

and so for example, I have

3/31/2014 7:21AM - 3/31/2014 6:33AM

and that difference is giving me: .03367

I'm not sure what this value represents......the difference in dates, converted to ??

View 2 Replies
View Related
Feb 21, 2014

I need to calculate the date difference between two dates and get the result in the number of years and proportion of months ie;

20/09/99 to 01/02/02 is 2.33333333 years. I can use the DATEDIF function to get 2 years 4 months as the result but for the calc I'm doing I need it in the format of 2.33333333. Thinking I just need to tweak the DATEDIF a bit but just can't work it out!

View 5 Replies
View Related
Mar 5, 2012

I have a vacation data of my company. I want to calculate difference between two dates (relieving date & rejoining date) after excluding holiday(which I will decide as per my company policy) also note that now i m in gulf country hence here Friday is weekly off not Sunday.

View 9 Replies
View Related
Jun 8, 2008

I wanted to calculate the difference between dates, excluding the weekends. After much thought i used =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer.

This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.

View 9 Replies
View Related
Feb 2, 2008

I am attempting to get the formula in Column "E" (see attached excel file) to work based off the individual reps 'days off / holidays' (Columns K:P (or more as needed) that they might have. the formula I am using (which works fine) is:

=IF(C2=D2,0, SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&D2)),2)={2,3,4,5,6,7})*(COUNTIF($K$2:$S$2,ROW(INDIRECT(C2&":"&D2)))=0)))

My issue comes up when I am coping this formula for say 100+ rows. When I copy the formula to all of the rows, the (COUNTIF($K$2:$S$2) becomes static and does not realize that the row (ie. Row 26 Rep C) has different days off than Rep A. I can manually change the reference for the countif to specify the correct row to their own days but that becomes tedious very quickly. I was wondering if anyone has come across a way to make the countif work based off of that the name in Column B matches the name in Column G then looks 'to the right' and uses the 'days off' that are listed for the corresponding Rep. I have tried a few different ideas but nothing has worked so far.

View 2 Replies
View Related
Jun 19, 2008

I have a spreadsheet with Received Date/Time in C2 and Resolved Date/Tim in D2, Work Start Time in E2, Work End Time in F2. C2 and D2 are in dd/mm/yyyy hh:mm AM/PM format:04/03/2008 10:27 AM06/03/2008 1:06 PM9:00:00 AM5:00:00 PM

I need to be able to calculate the resolution time of each request in hours/mins excluding weekends and public holidays. I've tried using the NETWORKDAYS function but it doesn't seem to pick up multiple days between the Received and Resolved dates/times. I have put the Public Holidays in my spreadsheet ( attached) in AA2-AG2.

View 4 Replies
View Related
Jan 28, 2009

I'm trying to make a formula to calculate the difference in a date between two dates. For exaple, in my business we have a due day for something. So the due date could be Feb 10, we need to know if it was done on time, late, or early. So I've gotten it to display late by doing this: =IF(B2>A2, "Late", (A2-B2)) But then I can't figure out how to incorporate this: =IF(B2=A2, "On Time") or =IF(B2<A2, "Early").

I would like to displaly this all in one cell. So if the due date is Feb 10 and it's turned in Feb 8 it's early, turned in Feb 10 it says on time, turned in Feb 11 it's late. I also tried this but it didn't work: =OR(IF(B2>A2,"Late")*IF(B2=A2,"On Time")*IF(B2<A2,"Early"))

View 3 Replies
View Related
Mar 8, 2013

I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):

The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.

Ex1:

Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)

end date 06-03-2013 10:30

Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )

will be equal to:

Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )

end date 06-03-2013 10:30

Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )

Ex2:

Startdate 06-03-2013 12:01

End date 06-03-2013 14:28

Time Difference 01:29

View 3 Replies
View Related
May 4, 2009

I am trying to determine the average reduction amount of appraised property values. I have two columns in my spreadsheet. Column H has appraised value of property. Column I has the accepted value of the property. Sometimes the accepted value is the same as the appraised value, sometimes it is reduced, and sometimes it is rejected.

I want to be able to find the average reduction amount when the accepted value is less than the appraised value and is not rejected.

Column H always shows a numerical value (i.e. $250,000), but Column I may have a numerical value or show "rejected".

View 9 Replies
View Related
Feb 18, 2009

I want to calculate time difference from two columns,

00:00:18:4400:00:28:44

00:00:19:2400:00:29:24

00:00:34:7700:00:44:77

00:01:05:3200:01:15:32

00:01:05:3200:01:15:32

wanting the difference between col B and col a.

Sum doesn't work

View 9 Replies
View Related
Aug 14, 2014

So I am running in to a large brick wall! I have a single pivot tablet with a column for items received and then a column for items shipped. I would like to create a calculated field in the pivot table that will subtract the items received from the items shipped columns. I have attempted to add the calculated field using the tool bar but it keeps giving me "0" for every item on the pivot table when there are obvious differences.

View 1 Replies
View Related
Aug 26, 2009

Is a way to calculate the difference between two columns in a Pivot table .

I have the data something like this in a pivot table....

Sum of MetricValueColumn LabelsRow LabelsFebruaryMarchAprilMayJuneJulyNet New My SitesNet New Team Sites0Number of Provisioned Users322059319906322372350239332914328404Total Capacity In Use (GB)1585.381722.761824.731947.772124.282262.78Total No. of My Sites208212316924682307313313134317Total No. of Team Sites101751082311396683273227940

What i need to do is to calculate the "Net New My Sites" and "Net New Team Sites" values something like this....

Net New My Sites (March) = Total No. of My Sites(March) - Total No. of My Sites(February)

Net New My Sites (April) = Total No. of My Sites(April) - Total No. of My Sites(March)

View 9 Replies
View Related
Feb 14, 2007

See attachment. In this example, in Column D I want to calculate the percent difference between the numbers in the last 2 columns (Column B and Column C). BUT I want a formula that will automatically update if I were to insert a new column between Column C and Column D. So as a result, new numbers would go in Column D and the percent difference would now be in Column E.

View 2 Replies
View Related
May 2, 2008

I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.

ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.

View 9 Replies
View Related
May 7, 2013

I have 2 Columns of Holiday Dates (Column A is the "FROM" date; and Column B is the 'TO' date) and Column C is Peoples Names corresponding to the Holiday dates:

ie:

COLUMN 1 ; COLUMN 2 ; COLUMN 3

A1= 2 Jan 13 ; B1= 10 Jan 13 ; C1= Tom

A2= 4 May 13 ; B2= 10 Jun 13 ; C2= John

A3= 7 Jul 13 ; B3= 10 Jul 13 ; C3= Tom

A4= 3 Aug 13 ; B4= 25 Aug 13 ; C4= John

A5= 6 Dec 13 ; B5= 15 Dec 13 ; C5= John.

I am trying to figure out a formula (eg SUMIF) to calculate and summarize the number of Days Holiday based on a person's name (eg Tom's total Number of days Holiday days) based on the above column configuration of Dates and Names.

View 4 Replies
View Related
Jul 24, 2014

I'm trying to automate the attached schedule so that the formulas in H stop increasing once the amount in column J equals zero. So far everything I've tried either gives me a circular reference error or ends up giving me the same result as if I depreciated the asset an additional month.

View 3 Replies
View Related
Apr 20, 2009

I request a help:

A1 = 01-Apr-2009

B1 = 02-Apr-2009

C1 = 03-Apr-2009

D1 = Blank

E1 = 05-Apr-2009

F1 = 06-Apr-2009

What is the best suitable formula to find "First blank cell", then "date difference" between today and its previous cell's (i.e today minus "C1" in this case).

View 12 Replies
View Related
May 9, 2009

I'm looking for a macro or formula that can calculate the difference to the lowest of col C based on option in colB. See attached. I complete what it should look like in F. Basically, this can be done by sorting the data by colB and using this formual for every group. =C2-MIN($C$2:$C$4). This is not ideal because I have many of these to do and you have to adjust the min range for every group. Plus group sizes will vary.

There has to be a better way. Ideally, I would not like to have to sort the data, unless a macro did it for me....

View 9 Replies
View Related
May 22, 2014

I'm currently doing some research for the World Cup (Soccer) and I want to create a formula that finds the largest gap between two dates. Basically, I'm copy and pasting player data into an Excel template I've created and one of the columns in each player's data is a list of dates when he has played over the last 12 months. I want to create a formula that shows me the length (in days) of his longest break from playing competitive football AFTER Oct 1st 2013.

View 5 Replies
View Related
Feb 28, 2008

I'm trying to figure out a formula that tells me how many reports are overdue.

A report is due every six months. There may be times when more than one report got missed.

Right now, I have the Y6 recognizing that a report is late... period.

=IF(V6>(TODAY()),0,1)

So, what I need is:

If the Time Difference between V6 and T6 is greater than 6 months, divide the difference by 6 mos and return the answer to cell Y6 (rounded down with no decimals).

See attachment.

View 14 Replies
View Related
Sep 8, 2008

I want to take two dates, a start date and an end date and get the number of days elapsed.

I also want to enter the dates quickly, as in 070808 for 07/08/08 (not having to enter the dashes). I have tried 00/00/00 and ##"/"##"/"## in the cells format, number, custom.

Using that format, entering 070808 in A1, and 070809 for A2 and finally in A3 =DATEDIF(A4,B4,"d") to get the difference in days. What I get is 1 day instead of 365 days.

So it's thinking 70809 - 70808 = 1.

How do I get it to give me 365 days? What format can I use in the date cells?

View 9 Replies
View Related
Jul 13, 2009

I have the following dates in column A

22.01.09

23.01.09

30.01.09

And I have the following in column B

Closed 28.01.09

Closed 24.01.09

Closed 02.02.09

I need to calculate the difference of days between column A and column B.

Is there a formula that I could use?

View 9 Replies
View Related