# Calculate Difference Between Two Date/Time

Nov 13, 2009
I am currently usins Excel 2007 and would like to calculate the diferrence in hours and minutes (ideally in decimal e.g 4:30 should be reflected as 4.5) between two date and time groups, excluding the non-working time between 17:00 and 09:00, weekends and holidays. An 8 hour working day is to be used. I have attached a spreadsheet were I tried to achieved the above with little success.

View 2 Replies
ADVERTISEMENT
Apr 27, 2014

Formula to calculate time allotted minus time used and show the difference in hour and minute.

View 1 Replies
View Related
May 3, 2008

This may be a bit vague but here goes.

I have to calculate the difference between the start time and end time of a job. The only catch is, how can I avoid calculating "out of hours" time. So, if a job goes from 9am to 9am the next day, I want it to avoid calculating between the hours of 23:30 and 03:30.

Another example is if a job goes from 02:00 to 04:00, I want it to avoid the tim between 02:00 and 03:00.

If there is a difference in days, so the job goes overnight, how do I take that into consideration also.

View 9 Replies
View Related
Aug 16, 2008

I've got a time difference from 8:00AM - 12:30PM as 4.30 I'm trying to get the minutes, .30, converted into a 6 minute increment, .5. Is it possible to do this and if so how would it be done? Below is a chart of how the time is converted from 6 minutes increments into decimal form.

6 = 0.1 36 = 0.6

12 = 0.2 42 = 0.7

18 = 0.3 48 = 0.8

24 = 0.4 54 = 0.9

30 = 0.5 60 = 1.0

View 5 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 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
Sep 30, 2007

i've two time constraints with 22:00~6:00 and 6:00~22:00. i'll apply time span to two constraints,calculate time covering on two constraints. it will start at anywhere of 00:00~24:00, time span will be 00:00 ~24:00. i add some formula, a3=start time, b3=end time. time constraints with 22:00~06:00:

=IF(AND(A3<=22/24,B3>=6/24,A3>B3),8/24,IF(AND(A3<6/24,OR(B3>22/24,AND(B3>0,B3<6/24,A3>B3))),IF(B3>=22/24,(B3-22/24)+A3-6/24,(B3+1-22/24)+MOD(6/24-A3,1)),IF(AND(OR(A3>=22/24,A3<B3,A3=0),B3<=6/24),IF(OR(A3=0,A3=1),B3,IF(A3>B3,B3+1-A3,B3-A3)),IF(AND(A3<=22/24,B3<=6/24),MOD(B3-22/24,1),IF(AND(A3>=6/24,B3<=22/24,A3<B3),0,IF(AND(A3>=22/24,B3>=6/24),(1-A3)+6/24,IF(AND(A3<=22/24,B3>22/24),B3-22/24,6/24-A3)))))))

time constraints with 06:00~22:00. :C$3=start time,$B4=end time...............................

View 5 Replies
View Related
Oct 16, 2007

I am setting up a time and attendance system.

What I want to do is calculate the overtime that someone has worked but in multiples of 15 minutes.

Example, if someone worked 20 minutes over they would be paid for 15 minutes overtime.

If someone worked 31 minutes over they would be paid 30 minutes overtime.

The possible overtime someone could work in one day is 6 hours.

I want it to return the overtime in decimal numbers (e.g 0.25 for 15 minutes overtime).

I have attached a sample spreadsheet.

I would prefer this to be done in VBA if possible?

View 3 Replies
View Related
May 27, 2008

I'm trying to do some calculations involving times. I'm using the format [=A2+(A1>A2)-A1] in order to calculate times from one day to the next which avoids negative numbers. This is working well. My problem is now that I'm trying to develop my spreadsheet and am trying to embed this inside an IF statement as the [value_If_True]and I get an error because it doesnt like the leading equals sign inside the IF statement.

View 6 Replies
View Related
Apr 10, 2014

Time arithmetic, I have two cells representing a time range.

The first one (say: X1) is formatted using the custom format [h]:mm and contains a certain number of hours and minutes. It gets its value by summing up other cells in the same format. A typical entry could be 98:35 to represent a duration 98 hours and 35 minutes.

The second cell (say: X2) is formatted as a number with 4 decimal places after the comma, and similarily gets its value by summing up other cells in the same format. It also represents a time duration as a number of hours. A typical entry could be 202.7500 to represent a duration of 202 hours and 45 minutes (because 0.75 of an hour is 45 minutes).

I would like to calculate the hour difference between these cells, and display it as hours and minutes. In the example given, the result should be negative, i.e. -104:10.

My first approach was to use the formula X1-X2 and format the result as [h]:mm, but this gives me a #VALUE! error.

View 2 Replies
View Related
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
Aug 7, 2008

I have an excel spreadsheet where you enter the start time and end time for job function. Since some of the times cross midnight, I use the formula J3=IF(I3>H3,I3-H3,1+I3-H3) where I is the end time and H is the start time (format hh:mm). This part works fine, however when I sum column J and change my format to Time 37:38:00 (since it is over 24 hrs), it returns a large number of 2234:48:39 which should be closer to 223:00:00.

View 2 Replies
View Related
Jan 24, 2008

I have two sets of data, one is recorded every 5 minutes and the other is every 15 minutes. I am trying to add every 3 cells in the 5 minute column so I can compare it side by side with the 15 minute column. I have tried one of the responses in this forum with placing 0s in 2 cells and then the formula in the third however this does not allow me to compare the 2 sets side by side.

View 4 Replies
View Related
Sep 22, 2007

i am trying to create a forumla in a column (D in my expamle below) to count the number of days between two dates. Knowing that col D will change everytime the @now date changes - but thats ok..

COL A COL B COL D

ROW 1 Open Date Closed Date Count of days open

ROW 2 4/1/07 BLANK CELL Formula result here

ROW 3 4/1/07 9/5/07 Formula result here

if col b has a date then subrtract col a from b and display the # result, if col b is blank then subtract col a from location I store the @ now date - say Col ZZ Row 99? so no matter there is a count of number of days in every row in column D

View 3 Replies
View Related
May 13, 2008

I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"

View 7 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
Sep 21, 2007

On some Military computers the Add-ins were not installed, therefore the WEEKNUM function cannot be used.

The task is to calculate the time interval (in whole weeks) between two dates.

(Every start date is to be calculated as a whole week – same for the End date).

I tried but the most but came up with the incorrect formula in the attached workbook.

View 9 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
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
Aug 22, 2008

I have call data in date/time 06/07/08 2:00 PM custom format for a 2 month period. I have my regular opening times eg Monday 9-3, Tuesday 10-4. For each call I want to know if it was made during opening times or not.

View 7 Replies
View Related
Aug 22, 2006

I have 2 cells with dd/mm/yy h:mm format. I have been using =TEXT(BQ3-BP3,"[h]:mm") to work out the hours and minutes difference between the 2 cells. However I would like the formula to work so that the hours and minutes difference will only come into effect after midnight on the first day. For example

01/01/06 23:30 - 01/01/06 23:45 would read 00:00

but

01/01/06 23:30 - 01/02/06 00:45 would read 00:45

View 6 Replies
View Related
Jun 14, 2013

I have tried several ways to calculate the difference between 2 dates/Time fields. Here is what i have tried and the issues i am encountering.

Cell F3 Has the Create date and Cell G3 has the resolution date and i need to calculate the difference between the 2 in months, Days, Hours, Mins and seconds:

1st option - =G3-F3 and set the format of the cell to mm "m "dd"d" hh"h "mm"m "ss"s "

The problem is, for some reason the months isn't calculating correctly and appears to add 1 month

2nd option - =DATEDIF(F156,G156,"y")&"y "&DATEDIF(F156,G156,"ym")&"m

"&DATEDIF(F156,G156,"md")&"d "&TEXT(MOD(G156-F156,1),"hh""h ""mm""m ""ss""s""")

This appeared to work however because it looks at the date and time separately, when there are 22 hours difference which spans over 2 dates ( Created 13/06/2013 10:30:00 Resolved 14/06/2013 08:34:00) its shows as 1 day and 22 hours which isn't correct.

View 3 Replies
View Related
Dec 28, 2013

How to find the difference between 18/04/2013, 13.40 hrs and 20/04/2013, 11.40 hrs to get the result as 1 day and 22 hrs

View 9 Replies
View Related
Feb 24, 2014

1.The attached file shows an example extract of a data extract that has thousands of lines. See the Data Tab.

2.What I need is some time that has passed between two dates in a DDHHMM format

3.What I then need is the top 50 of each of the times (or the longest time past)

4.The column headers will be: WO Number | Contractor Name | Time Calculation â€“ see the various Report tabs

5.The calculations in the Data tab are between the following columns.

a.Difference between Column Z and Column AC

b.Difference between Column AC and Column AD

c.Difference between Column AD and Column AE

d.Difference between Column AE and Column AF

6.Is it possible to show the top 50 only in time?

This report is forming a part of a larger report and all other formulas are already present so I am hoping to keep the file size quite small. The aim is to then hand this over to someone else to just print on a monthly basis depending on the data that gets added with minimum input.

View 2 Replies
View Related
Oct 22, 2008

I need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields:

A1 2008/10/10 16:30

B1 2008/10/11 09:30

C1 8:00

D1 17:00

A1 = report received

B1 = reported recorded in db

C1 = work day start time

D1 work day end time

Where the answer should = 2 hours.

I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1-A1, "d:hh:mm")

And how do I account for weekends?

View 9 Replies
View Related
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
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
Aug 22, 2012

I have

Code:

Start date 21/08/2012 23:21:30

End Date 24/08/2012 22:21:45

is there a formula to get the time difference in Hours for example in this case its 72:01:15 72 Hours, 1 minute & 15 seconds

View 2 Replies
View Related
Aug 11, 2006

I'm trying to devise a formula to produce "days in inventory" based on the following data:

Date In

Date Out (which may be blank if cargo still here)

Todays Date

Days in Whse (which is the formula I can't figure out!)

It needs to work like this, date out - date in, unless date out is null. If date out is null, the result should be calculated based off of todays date - date in.

View 3 Replies
View Related
Sep 3, 2009

I want to be able to calculate a due date two days from an entered date/time. If I input 9/3/2009 3:00 PM into cell F6 I would like cell G6 to display 9/5/2009 3:00 PM. The formula that I am currently using includes a holiday list so if the due date lands on a day within that list it gets pushed out.

My formula is =if(F6="","",Workday(F6,2,Z1:Z144)).

Basically when F6 gets populated G6 is supposed to populate with a due date of two days later (same time of the day). Currently if my input date/time is 9/3/2009 3:00 PM the formula is displaying a due date of 9/5/2009 12:00 AM.

View 9 Replies
View Related