# Excel 2010 :: Calculate The Difference Between 2 Dates?

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
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
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
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. Im not sure if the way Ive set it up is the most practical. Ill attach the spreadsheet to better explain.

View 4 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
Apr 15, 2014

As you can see in attached file (hopefuly you can see it) pivot table Summed sales in 2014 and 2013 in column GRAND TOTAL. Insted of summing this two years, I would like to show difference between them, but keep this layout!

PROBLEM.jpg

View 4 Replies
View Related
May 11, 2014

Excel 2010 - Solver will NOT work with whole numbers and that is what I need (only whole numbers, i.e whole vials being used). So this is a drug/inventory problem. The drug is dosed as 25, 35,or 50 units/kg. Also a Max dose is reached with 100 kg. (this I can do). The trick is we want to use whole vial sizes and the vials come with varying amount of drug per vial (480-620 units/vial). We can enter our existing Inventory to get the exact units/vial and number of vials on hand. Now I need to figure out the best combination of vials in Inventory to use to get the closest to the dose needed. If Max dose is reached then the MOST we can go over the max dose is 5%. I'd like to see the percentage difference from the calculated or Max dose.

View 7 Replies
View Related
Nov 8, 2012

I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.

If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.

I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.

View 1 Replies
View Related
May 1, 2014

keep getting "The formula you typed contains an error".

To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.

3. If we want his age in years and months, then we can use a formula like this one:

=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”

The output of this formula will be as follows:

40 years and 2 months.

I change A1 to G3 because that is where I the birth date.

View 2 Replies
View Related
Dec 24, 2013

Using Excel 2010

How can I use 2 filters in DAX Calculate?

I tried

MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field] && Tbl2,Tbl2[Field2])) It works with 1 Filter : MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field])) Just falls down when I add the second filter.

View 7 Replies
View Related
Apr 8, 2014

I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:

E3 provides the start time of 4:00

H3 provides an end time of 15:30

If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.

View 7 Replies
View Related
Nov 27, 2013

I am trying co Calculate number of nights between to dates for example 10/26/2012 -- 11/25/2013 in a period that can be greater than the month Period. for example. some One checked in on October 24, 2013 and they will checkout on december 17, 2013. in the monthly Period of 10/26/2012 -- 11/25/2013, I am trying to calculate, the monthly nights of that period itself, and the number of days he was checked in on that period.

View 1 Replies
View Related
Apr 3, 2014

I have been trying to get excel to calculate elapsed time between two dates using mostly what I've read from other elapsed time posts. But I am clearly missing a step. I'm trying to streamline my process for taxes.

I want to take a time in the format of " yy/mm/dd hhmm " example: 1

3/04/24 2245 ( note its a 24hr clock) 13/04/24 2245 would represent 2013 April the 24th at 10:45PM.

------- A --------------- B -------------------- C

13/04/24 2245 ------13/05/24 0900 ---------[display hrs elapsed] <-- currently get #name?

I have custom formatted A and B to yy/mm/dd hhmm and formatted c to [h]:mm

When you enter b-a in column c you get #name?

If its easier to have the date and time into separate columns I can give that a shot, I was just trying to make it as streamlined as possible for copy past.

View 11 Replies
View Related
Jul 5, 2014

I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.

A

B

C

D

E

1

0

0

20

5%

[Code].....

So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C

The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.

View 1 Replies
View Related
Apr 21, 2014

I am using Excel 2010 and I am trying to calculate 4 different factors' (columns) values which are numbers. Based on my analysis I will assign the value from 1-5 for each factor and at the end I have to decide the priority (a new column) adding all 4 columns' numbers. My Priority column has three criteria (High, Medium, and Low) but I don't know what formula/function to use and how. I have tried IF function but did not work.

View 9 Replies
View Related
Nov 22, 2011

I'm using Excel 2003 & Windows XP Professional.

I have two sets of data tables. One contains Number, Name & Date. The other contains amounts which those people have paid in 2007, 2008, 2009 & 2010. So in the below Jones has paid a total of $580 over those years, Smith has paid a total of $650 over those dates & so on.

The amounts in 2007, 2008 etc are full year amounts. I'm after a formula that can calculate the amounts for part years based on the dates from C1 to C11 by number (or name).

I've got what I think the results should be (I think with some rounding problems), but my real data has much more data.

View 3 Replies
View Related
Dec 11, 2013

I have 2 columns directly next to each other each containing dates.

Example 1:

F2 = 5/23/13

G2 = 5/23/14

Example 2:

F3 = 6/6/13

G3 = 4/11/14

I wanted to do a conditional formatting that would highlight the cell in

column G if the Month/Day doesn't match the Month/Day in column F

Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??

View 3 Replies
View Related
May 29, 2014

Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

[code].....

View 4 Replies
View Related
Apr 7, 2012

I am using 2010 to write some code for a spreadsheet that will be used in 2007, so I know that RepeatLabels is not available.

My question is if you Group a date field by Months and Years and the data spans across 2 or more different years, is there a way to hide months in a specific year? I ask because the Date field has only 12 months in it and if I hide Jan (for example) it hides Jan in both years. (attached pic of filter for Date field below.)

Is there some other way to format it so it can discern between years or do I need to add yet another column to my original data?

View 2 Replies
View Related