# Calculate Date/time Format Over 24 Hours

Nov 22, 2006
I need to calculate the difference between two cells that are formated like this: 11/10/2006 13:00. Simple subtract only works when the date hasn't changed (the subtract formula seems to focus on the time and ignore the date).

I found a formula that will work, but the results are in regular numbers, I need the hh:mm format and if I change the format of the result column, the time displayed is wrong.

The formula is=((B2-A2)*1440)/60)

If my result is 122.6 in regular numbers, when I convert to hh:mm I get 14:00.

Is there a better way to do this? Also, is there a way to eliminate weekend & holiday hours from the calculation? This is not as important as getting the results to calculate correctly in the hh:mm format.

View 9 Replies
ADVERTISEMENT
Apr 4, 2013

find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?

08/03/2013 13:32:00

02/04/2013 09:32:50

View 4 Replies
View Related
Jan 21, 2010

I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about.

For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.

View 2 Replies
View Related
Feb 28, 2013

I can calculate total hours when a user enters a start date/time and a finish date/time. The kick is I only want to include hours from 2:00 PM to 12:00 AM (10 hour period). So assuming all the start and end times will be in this range, how can i calculate work hours over multiple days? For example: Start date/time = 2/26/13 2:30 PM and end date/time = 2/28/13 10:30 PM. I want my calculated hours to show 28 hours.

View 1 Replies
View Related
Jan 29, 2010

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

View 9 Replies
View Related
Oct 15, 2008

Having trouble adding a column of minutes and converting the total into hours and minutes. Say Cell A1 through Cell A18 each have 12 minutes in each cell. I want cell A19 to tell me how many hours and minutes of total time that have elapsed. I have tried hh:mm, [hh]:mm, but nothing works.

View 9 Replies
View Related
Jul 20, 2012

I want to calulate time by decimal the problem is anything after 1 am wont work

example start at 12:25 finsh at 1:45 time should show up as 1.33

using excel 2007

View 3 Replies
View Related
Feb 27, 2010

Is it possible to calculate hours if both the start and end time are in the same cell. Unfortunatly the developers of our scheduling program developed it so when you export the schedule to excel it shows Greg in A1 and 3:00pm-9:00pm in A2. I would like to have excel calculate the 6 hours so I can use it to auto generate another spread sheet I am making.

View 10 Replies
View Related
Feb 6, 2008

i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.

As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.

if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total

if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs

if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs

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
May 7, 2014

I needs a formula (not VBA) to calculate the required start date.

I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00

My working week is from monday u/i friday and every day I work from 08:00-16:00

At what time do I have to start the job to get it done in time.

The formula should give this result: 01-may-2014 15:00

View 3 Replies
View Related
Aug 4, 2013

I am trying to determine the total hours of downtime accumulated when there are 2 or more machines down during the same time period. To do this, I need to be able to determine if at any point there are 2 or more pieces of equipment down at the same time, and if this is true, how many hours were overlapped. The attached spreadsheet shows how the data is presented.

View 7 Replies
View Related
Jun 22, 2009

I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.

View 3 Replies
View Related
Sep 17, 2012

Any way to calculate the total hours staff work based on the mininum time of the first transaction to the maximum time of the transactions. I used a DMIN and DMAX function to get those times per employee. The issue is then the time goes over from one day to the next, such as from 11 PM to 4 AM the next day. As you can see in the data below,the fourth record shows the minimum time as 12 AM and the max as 11 PM with total time worked as 23 hours. In this example, the total hours worked should be five hours.

min time

max time

total hours

7:00 AM

16:00

9:00

[Code] .......

View 4 Replies
View Related
Oct 5, 2007

I have been trying to write a formula to calculate the differrence in days, hours or minutes between 2 cells in Excel.

Here is an example of the two cells

06/08/2011 00:0006/08/2011 12:19

View 5 Replies
View Related
Dec 8, 2007

I've being trying t calculate an excel formula to work out a order turnaround time based on opening hours.

Example: The store is open from 8 am to 6pm monday to friday, I need to get a formula to work out how long it took from the time the order was logged until it was completed, during the course of the working day - this works fine but if the order was received on 06/12/07 at 5:20 pm then was finally completed by 7/12/07 at 11:55 the next day I need to figure out how to take into consideration of non working hours during that time. (time from 6pm to 8 am next day

example 2 - if order was sent on the 06/12/07 at 01:30pm and completed at 06/12/07 4:30 then I know its taken 3 hours to complete

example 3 - if the order was sent on the 06/12/07 at 5pm and completed next day at 07/12/07 at 10am - then I know it took 3 hours to complete

View 7 Replies
View Related
Jun 6, 2012

I have a requirement of calculating time spent on ticket, basically a difference between the time a ticket was logged and when it was resolved.

First, working days are Saturday through Wednesday, i.e weekends are Thursday & Friday.

Second, there are different resolver groups or teams which work on a ticket. These teams have different working hours, some work from 07:30 to 15:30 on weekdays, and others work 24/7. Each ticket will be assigned only to one resolver group.I also need to consider holidays.

So, my requirement is to check the resolver group for each ticket, and decide the working hours based on it. After this check has to be done for holidays, and then difference between logged time and resolved time needs to caluculated in minutes.

Tickets can be logged at any time of the day / week, it may not always be logged during working hours only. in such cases calculate should consider next business day / hour as the start time.

Raw data for tickets is available in =Sheet1!$A$1:$E$21

Ticket NoResolver GroupLogged DateResolved DateTime Spent123456

Team A5/21/2012 1:56:28 PM5/29/2012 2:10:53 PM343543Team B5/21/2012 9:31:02 PM5/27/2012 3:41:22 PM853732

Team C5/22/2012 2:28:06 PM5/26/2012 2:34:31 PM

[Code].....

View 4 Replies
View Related
Mar 26, 2013

When I enter L into B3 I want B3 to show a message.

i.e. B3 is blank, I enter L into B3, it the changes L to 0645 - 1515.

And it needs to be in time format so at the end of the roster i can add how many hours in total.

View 3 Replies
View Related
May 30, 2012

I want to add hours to a date-time cell to get result in date-time.

Format of cell A1 is d/m/yy h:mm AM/PM

Format of cell A2 is General

Format of cell A3 is d/m/yy h:mm AM/PM

I want to add A2 (number of hours) to A1 to give A3.

The formula I used is A3=A1+Time(A2,0,0)

The formula works perfectly fine when A2 is less than 24, but when A2 is more than 24, the date doesn't get changed.

View 6 Replies
View Related
Oct 28, 2007

I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?

View 9 Replies
View Related
Jan 7, 2012

For the past month now, I'm trying to calculate some work hours (night hours actually), based on a reference.

Let me show you the table:

- Column A has all of the schedules of the employees

- Column B has the numbers of hours for each schedule (all have 8.5 hours/day)

- Column C has the reference for the night hours.

Now what I'm trying to do, is to find a formula that will calculate the numbers of the night hours using the reference in Column C

Row 23 in that table has an example of what I'm looking for.

View 9 Replies
View Related
Nov 16, 2012

How do I convert all this to times (Hours worked)

I know if I change the format to TIME to total hours does not add up correctly

i.e.

41.53361078

56.47083288

73.12722164

45.45944408

58.28749953

73.75777719

70.7266661

57.02361065

49.52916627

62.88527727

Total 588.8011064

View 4 Replies
View Related
Jul 6, 2007

is there a way in VB to make it fast, and tidy, clear the contents of all unlocked cells on one sheet ?

View 2 Replies
View Related
May 11, 2014

Is there a way to calculate how many days and hours there are in a date range IE 5/11/2014 to 10/31/2014 = days and hours

View 2 Replies
View Related
Sep 24, 2012

i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.

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
Jan 2, 2013

I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).

View 4 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
Aug 23, 2008

I have a problem concatenating time in excel if it is of Date + Time format..

What formula do I use to just add the time by, say, 3 hours? I'll need to use, say cell B2, which will add 3 hours to it.. What formula do I use?

View 3 Replies
View Related
Feb 22, 2008

Column 1: Date Call is received by helpdesk

Column 2: Time Call is received by helpdesk

Column 3: Drop-down list indicating Priority of call (High,Medium,Low)

In Column 4 I want to enter a formula that adds working hours only to the date and time entered in Columns 1 & 2. The time added will vary depending on what is entered in Column 3 (e.g.: if priority is High add 1 hour, Medium add 2 hours, Low add 3 hours). The working hours i need to adhere to are 09:00-17:00, Monday-Friday.

View 6 Replies
View Related