# 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
ADVERTISEMENT
Sep 25, 2006

i need to get a formula that will calucate hours and min. its for how many hours the employee has not worked. some of them would be strait hours some would be just min there is no way to tell.

example

lates 2 hours

anp(absent no pay) 12 hours

sicks 55.5 hours

no calls

early outs 21 min

(this is just an example if it were real this person would be fired)

i know this adds up to 69.85 hours but i can't fuiger out a way to get it to calucate in excel. i know i could have it all changed to min and then devied by 60 to get the hours but how do i get it to read what is mins and whats hours?

View 7 Replies
View Related
Mar 2, 2010

The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :

Difference between

02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes

=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))

The following displays it in the Hrs and Mins format

=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))

View 9 Replies
View Related
Jul 17, 2009

I have to work out the time difference between 2 times over a 24 hour period.

There is a fixed booked departure time and an actual time of departure, the actual time of departure can be either before or after the booked time.

I need the time difference between the times shown as either +/- minutes at present it returns the time in [HH:MM].

I have a simple IF formula to work out - time but when it calculate + time it returns the reverse difference.

A= booked time

B= actual time

View 8 Replies
View Related
Sep 14, 2007

if someone called me at 4:55pm and ended the call at 5:10pm, the whole call lasted 15 minutes. So, I want to show on excel that the call lasted 5 minutes in the 16th hour of the day (4:55pm) and 10 minutes in the 17th hour of the day (5:10pm), for a total of 15 minutes.

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
Oct 18, 2008

Im trying to calculate hours and minutes between two dates/times over more than 24hrs. I have:-

A1 is 18/10/08 13:14, B1 is 20/10/2008 12:20, C1 is 1:23:05

Using the formula C1 =INT(B1-A1)&TEXT(B1-A1,":hh:mm")

But what I really need is just Hours and Minutes so the above should read 47:05.

View 5 Replies
View Related
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
Apr 24, 2006

I need the difference between two dates/times field in hours or minutes.

Eg:

A1 B1

1/4/05 10:00 2/4/05 14:30

The result should be 13.5 hours, considering only 8 hrs per day, only business days and 8 to 5 workday.

View 10 Replies
View Related
Dec 7, 2008

I am trying to figure out a difference in hours between date & times.

Cell & Info:

A1 = 07/12/2008

A2 = 02:00:00

A3 = 07/12/2008

A4 = 04:00:00

So in cell A5 i want the difference which is: "02:00:00"

but i dont know how to include the data in the formula....

etc if:

A1 = 07/12/2008

A2 = 01:00:00

A3 = 08/12/2008

A4 = 01:00:00

A5 should be = "24:00:00"..

View 2 Replies
View Related
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
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
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
May 25, 2011

I have a spread sheet with a colum showing average time to complete a task. This is currently shown as Days:Hours:Minutes:Seconds (4:19:33:19). I meed it to be shown purely as minutes, or at least as hours and minutes.

View 4 Replies
View Related
Dec 5, 2006

I have a formula which will calculate the number of hours and minutes between two military times. I would like it to calculate the total number of minutes instead of hours and minutes. I have uploaded a small example of what i have so far.

View 3 Replies
View Related
Jul 4, 2006

What formula will convert 4.50 to 530 minutes ( "Decimal Time" )

another example 16.50 to 1250 minutes.

View 13 Replies
View Related
Jan 21, 2009

I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute

View 9 Replies
View Related
Aug 22, 2006

creating a formula for converting time data that has been created in an excel spreadsheet in minutes i.e. 516 minutes which I need to turn into Hours and Minutes i.e. 08:36 I am not experienced using Formulas, apologies if this question has been posted before, I did use the search facility to look for threads, but could not find anything related

View 5 Replies
View Related
Jan 29, 2010

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View 9 Replies
View Related
Aug 30, 2007

1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes ) - excel cannot average and work with this number format

2) resolution - =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm - works perfectly.

Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management?

example attached.

View 9 Replies
View Related
Jul 4, 2007

I have a worksheet which I am trying to format as a template which includes inputting start times and end times of work and calculating how many minutes are taken to do the job. I just can seem to find the correct formula.

View 9 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
Aug 11, 2010

I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.

View 3 Replies
View Related
Jun 11, 2008

A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.

example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.

If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.

View 9 Replies
View Related
Oct 10, 2009

When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?

View 3 Replies
View Related
May 27, 2006

Spreadsheet tracks race times for cross country races and needs to be able to

calculate improvement time in mm:ss (or zero if less than previous race).

Also,it would be nice to be able to enter times without using colons or have

to reference the field as h:mm:ss. The race result will always be mm:ss and

the last two digits will always be seconds, the result will never be more

than 60 minutes so hours are a real bother.

A1 (8:52, m:ss) - A2 (8:40, mm:ss) should = 0.12 (12 seconds)

Presently I am using the following: =TEXT(A2-A1,"h:mm:ss"), I only want to

display m:ss, hours are meanlingless

View 12 Replies
View Related
Dec 20, 2009

i am trying to make a employee work hour sheet so i can add the time and it add up all the hours and minutes he/she been working . now what i am trying to do is to enter 810 in the cell it automatically change it to 8:10 format but the problem it change it to 12:00:00.

even when i enter 083612 it again change it to 12:00:00.

now i have used the format cell > time and no luck. i already removed and installed my office but i still have the same problem.

View 12 Replies
View Related
Oct 17, 2008

I know similar questions have been asked in the past, but I can't seem to get this to work for my specific case. I need to convert hours into minutes, and these times do not conform to a 24 hour clock. For example, I need to convert 1000:15 into 1000.25

View 3 Replies
View Related
Jan 14, 2009

I have a database which shows a time some one came into work and a time they left. Formatted as HH:MM. I can minus the time they left from the time they came in...to work out how many hours they worked. However, whenever i want to work out how many mintes it is (time left - time in * 60). It does not seem to work.

Example

A1 - Time In 09:00

A2 - Time Out 17:00

A3 - Hours = 8 (A2-A1)

A4 - Mintes should be 480. (A3*60)

View 3 Replies
View Related
Sep 18, 2009

Unsurprisingly I have a spreadsheet:

Column D contains the total hours that a component has been installed for

Column C contains the additional hours the component acquired

I need the total of these 2 sets of time added together to give me the new hours that the component has acquired. All cells have been formatted [h]:mm:ss

I attach an extract of the spreadsheet. At the moment the values in column D are all calculated manually. When I add the values together Excel returns #Value!

The annoying thing is that in cells B2 and A3 I tested this and can do this with no problem...the value in B3 is the sum of B2 and A3.

View 2 Replies
View Related