Calculate hours between to separate dates and times

I need to work out the amount of hours between a finish time on one day and 
the start time on the next day. In some cases the start time may not be until 
2 days time. 
0
Dave08 (15)
10/12/2008 11:17:00 AM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
527 Views

Similar Articles

[PageSpeed] 29

Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you 
want the mins as well)
-- 
__________________________________
HTH

Bob

"Dave08" <Dave08@discussions.microsoft.com> wrote in message 
news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
>I need to work out the amount of hours between a finish time on one day and
> the start time on the next day. In some cases the start time may not be 
> until
> 2 days time. 


0
BobNGs (423)
10/12/2008 11:47:48 AM
Thanks Bob, But the problem is the times are on 2 separate dates e.g. finish 
2/10/2008 0200 and start 3/10/2008 12.00 

"Bob Phillips" wrote:

> Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you 
> want the mins as well)
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Dave08" <Dave08@discussions.microsoft.com> wrote in message 
> news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
> >I need to work out the amount of hours between a finish time on one day and
> > the start time on the next day. In some cases the start time may not be 
> > until
> > 2 days time. 
> 
> 
> 
0
Dave08 (15)
10/12/2008 12:06:00 PM
Since Excel stored dates as serial numbers, try =(A2-a1)*24 where 24 
converts fractions of a day to hours
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dave08" <Dave08@discussions.microsoft.com> wrote in message 
news:C9BC6027-0B3B-47C4-9F68-76E2EA012702@microsoft.com...
> Thanks Bob, But the problem is the times are on 2 separate dates e.g. 
> finish
> 2/10/2008 0200 and start 3/10/2008 12.00
>
> "Bob Phillips" wrote:
>
>> Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you
>> want the mins as well)
>> -- 
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Dave08" <Dave08@discussions.microsoft.com> wrote in message
>> news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
>> >I need to work out the amount of hours between a finish time on one day 
>> >and
>> > the start time on the next day. In some cases the start time may not be
>> > until
>> > 2 days time.
>>
>>
>> 


0
bliengme5824 (3040)
10/12/2008 2:13:27 PM
Still with differente dates the given formula applies :
Format the 2 dates cells as : 3/14/01 1:30PM
and the 3er date cell(result) as : [h]:mm:ss.
If you mean different worksheets, then just
right the worksheet name in form of the cell as
ex: =A1-SHEET3!A2

"Dave08" wrote:

> Thanks Bob, But the problem is the times are on 2 separate dates e.g. finish 
> 2/10/2008 0200 and start 3/10/2008 12.00 
> 
> "Bob Phillips" wrote:
> 
> > Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you 
> > want the mins as well)
> > -- 
> > __________________________________
> > HTH
> > 
> > Bob
> > 
> > "Dave08" <Dave08@discussions.microsoft.com> wrote in message 
> > news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
> > >I need to work out the amount of hours between a finish time on one day and
> > > the start time on the next day. In some cases the start time may not be 
> > > until
> > > 2 days time. 
> > 
> > 
> > 
0
FC1 (15)
10/12/2008 4:04:00 PM
That doesn't impact my response one iota. Try it!

-- 
__________________________________
HTH

Bob

"Dave08" <Dave08@discussions.microsoft.com> wrote in message 
news:C9BC6027-0B3B-47C4-9F68-76E2EA012702@microsoft.com...
> Thanks Bob, But the problem is the times are on 2 separate dates e.g. 
> finish
> 2/10/2008 0200 and start 3/10/2008 12.00
>
> "Bob Phillips" wrote:
>
>> Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you
>> want the mins as well)
>> -- 
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Dave08" <Dave08@discussions.microsoft.com> wrote in message
>> news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
>> >I need to work out the amount of hours between a finish time on one day 
>> >and
>> > the start time on the next day. In some cases the start time may not be
>> > until
>> > 2 days time.
>>
>>
>> 


0
BobNGs (423)
10/12/2008 8:38:00 PM
I found this option worked the best in resolving my delima. because I had the 
finish and start dates and times in seperate coloums I had to first calculate 
them then subtract them. I then formatted them [hh]:mm
Date in Column A and the times in Columns B 
 =(A2+B2)-(A1+B1)


"Bob Phillips" wrote:

> That doesn't impact my response one iota. Try it!
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Dave08" <Dave08@discussions.microsoft.com> wrote in message 
> news:C9BC6027-0B3B-47C4-9F68-76E2EA012702@microsoft.com...
> > Thanks Bob, But the problem is the times are on 2 separate dates e.g. 
> > finish
> > 2/10/2008 0200 and start 3/10/2008 12.00
> >
> > "Bob Phillips" wrote:
> >
> >> Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you
> >> want the mins as well)
> >> -- 
> >> __________________________________
> >> HTH
> >>
> >> Bob
> >>
> >> "Dave08" <Dave08@discussions.microsoft.com> wrote in message
> >> news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
> >> >I need to work out the amount of hours between a finish time on one day 
> >> >and
> >> > the start time on the next day. In some cases the start time may not be
> >> > until
> >> > 2 days time.
> >>
> >>
> >> 
> 
> 
> 
0
Dave08 (15)
10/13/2008 1:54:01 AM
It does help if you give all the information at the start. You never 
mentioned the separate cells.

-- 
__________________________________
HTH

Bob

"Dave08" <Dave08@discussions.microsoft.com> wrote in message 
news:428E3860-B295-43B0-A16D-F0F4D3EA20CB@microsoft.com...
>I found this option worked the best in resolving my delima. because I had 
>the
> finish and start dates and times in seperate coloums I had to first 
> calculate
> them then subtract them. I then formatted them [hh]:mm
> Date in Column A and the times in Columns B
> =(A2+B2)-(A1+B1)
>
>
> "Bob Phillips" wrote:
>
>> That doesn't impact my response one iota. Try it!
>>
>> -- 
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Dave08" <Dave08@discussions.microsoft.com> wrote in message
>> news:C9BC6027-0B3B-47C4-9F68-76E2EA012702@microsoft.com...
>> > Thanks Bob, But the problem is the times are on 2 separate dates e.g.
>> > finish
>> > 2/10/2008 0200 and start 3/10/2008 12.00
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if 
>> >> you
>> >> want the mins as well)
>> >> -- 
>> >> __________________________________
>> >> HTH
>> >>
>> >> Bob
>> >>
>> >> "Dave08" <Dave08@discussions.microsoft.com> wrote in message
>> >> news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com...
>> >> >I need to work out the amount of hours between a finish time on one 
>> >> >day
>> >> >and
>> >> > the start time on the next day. In some cases the start time may not 
>> >> > be
>> >> > until
>> >> > 2 days time.
>> >>
>> >>
>> >>
>>
>>
>> 


0
BobNGs (423)
10/13/2008 9:36:57 AM
Reply:

Similar Artilces:

Sum of Hours Per Month
I have been given a data set which contains work hours and dates in the form of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker Using a pivot table grouped by months and years would be the most straightforward. "Zachary Baker" <Zachary Baker@discussions.microsoft.com> wrote in message ...

Excel Simple Calculations help
Hi Guys, thanks for looking. Im creating an inventory stock list for m fresh produce company and have a list of products with a column o 'stock on hand'. I also have two other columns, one which takes awa sold Quantities of produce from the total stock on hand, and one tha adds to it. Now the problem is that as i sell the products i want t take away from the stock on hand, i use a simple formula whic subtracts the sold from stock on hand. but i want it doing 'calculato style' so that i can type a number, say i sold 7 of an item, i typed i in the sold cell, press enter and i...

Calculate total amount on continuous form line in sub-form
Hi, I’m on Access in Office 2003. I have a sub-form which has a default view of “Continuous Forms”. This form is comprised of a single line which has the fields: Work Date Hours Miles Other Expense Total The work date, hours, miles, and Other expenses are data entry fields. The Total field is equal to (Hours * HourlyRate) + (Miles * MilageRate) + Other Expenses. The rates are retrieved from the main form. That part is working fine. The Work Date, Hours, Miles, and Other Expenses are bound to fields on my table. The Total field is unbound. The problem ...

Entourage goes into spinning-wheel from time to time while using
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I got an iMac with 10.6.2, Microsoft Office 2008 Standard, fully updated, running in a Microsoft Exchange Server 2003. <br><br>For some reason while I'm using Entourage, from time to time the application freezes, showing up the spinning wheel. <br><br>What could be the cause? <FONT FACE="Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'>On 4/29/10 2:24 PM, in article <a href="59bb7aee.-1@webcrossing.JaKIaxP2ac0&qu...

Time Scale Question
I am trying to show the time scale in months on the middle tier and on the bottom tier I am trying to show the number of weeks in that month and then start the count over for each month (ex: Jan 1 2 3 4 5 , Feb 1 2 3 4, etc.) I cant seem to get it to show up that way. Can anyone help Hi, I'm afraid that option is not present, sorry -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "MSP Beginner" <MSP Beginner@discussions.microsoft.com> wrote in messa...

Converting a string date field/node in an XML document to Date type in a dataset
I am having problems with Date Strings when using VS.net method of converting an XML document to a dataset. I need the the date nodes to be of date type. As I am loading the Dataset to a datagrid, and I using the sort facility to sort by date. Currently the dates are being treated as strings. ree32@hotmail.com (ree32) wrote in message news:<7606ccc8.0409192108.3cbebe24@posting.google.com>... > I am having problems with Date Strings when using VS.net method of > converting an XML document to a dataset. > > I need the the date nodes to be of date type. As I am loading the ...

Prevent Manual Calculation Mode
Is there a way to prevent the manual calculation mode. It seems 2003 "knows" when to set it. In most cases I find it annoying when results are obviously not what they're supposed to be, and I haven't set the manual mode myself. Hi, This 'feature' of Excel causes it to remember the calculation setting of the first workbook opened in a session so if that workbok was set to calculation manual then it picks up that setting. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still ...

date format #18
Hi all, I have a spreadsheet containing lots of pivot tables. The source data contains date formulas in the following ways: =IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11 =IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07 As a result, when I try to sort my pivot table data by date, the dates that are shown as Nov-07 are recognised as text therefore only allowing me to sort by alphabetical order. The dates that are like 2007-11, i am able to sort it by the earliest/latest dates. I...

Calculated field 02-05-08
I am trying to calculated the number of days between to manually entered dates.Example.... Start date=02/05/08 and due date=02/08/08.... 3 days right? how do i get it to show on my form and entered into my table? Jim, It is bad practice to store calculated fields in your tables. If you need to store is somewhere you typically use a query for this. You'd then use yhis query as the source for your form. Calculation in the query: create a new field called difference like this difference: datediff("d",[firstdatefield],[seconddatefield]) hth -- Maurice Ausum "jim"...

calculations in a query
Hi there I am doing a query with calculations and the 2nd set is not working. The calculations are as follows: 1LoadTime: DateDiff("h",[starttime],[Finishtime]) This is done for 3 ports -- all the same formula and it works. But then I do a formula to sum the 3 ports as follows: TotalTime: ([1LoadTime] + [2LoadTime] + [3LoadTime]) and it doesn't work. There is no error message. It just shows a blank where a number should be. Please let me know how to make it calculate properly -- Thank-you! Ruth TotalTime: Nz([1LoadTime],0) + Nz([2LoadTime],0) + Nz([3LoadTime],0) ...

2 questions on POS 2009. 1. Decimal places 2. Tax Calculation
I am a user of RMS 2.0, and I have 2 questions on POS 2009 1. Decimal places. How many decimal places are you allowed in the price field on the item card ? In RMS 2.0 you are allowed 2, 3 if you change the type to 'gasoline'. I am looking for 5 decimal places 2. Tax calculation. Normally you enter in the price excluding tax, and then RMS calculates the tax for you. Is there an option in POS 2009 where the price can 'include' the taxes? And then when you select the item for sale, POS 2009 would 'back calculate' to break out the taxes ? Thanks i...

Question on Sorting by (date, etc)
I have a column (A) which sre dates. If I select the column heading and click either the sort ascending or desending button, the date column alone sorts independent of the other eight columns that comprise the entire entry. How would I go about being able to sort by a particular column and have everything sort with it? I have other columns that I would like to sort by sometimes too, like "color", "type", etc. Thanks for reading! select all the columns and now go to data | sort . On Feb 20, 11:21=A0pm, "Ed" <2...@333.com> wrote: > I have a column...

Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data refreshed from the Web. However I can't get it to show up on a chart with dates on the X axis. Have the axis formatted OK, but how does it collect its data? Have tried a simple worksheet with manually entered data & the dates entered in column A, but still can't get a chart to work. ...June. Jon Peltier has a charting tutorial that may help you get started: http://www.peltiertech.com/Excel/ChartsHowTo/index.html When you manually enter data and dates, what problems do you have with the chart? June wrote: >...

using date function, month shows as January when i type (12)
when date of birth is A1 and i use the formula =date(year(A1)+19,month(12),day(31)), to get the last day of the year before the 20th birthday, 31/1/1919 appears instead of 31/12/1919. why is this? if i take the +19 away, the same thing happens. The only time it works is if i specify the year instead of using another cell. Does this formula do what you need? ="12/31/"&YEAR(A1)+19 The reason your original formula did not work is because the MONTH() and DAY() functions require serial date numbers (that is to say, an Excel date equivalent expressed as a number). =DATE(YEA...

how to replace the date
hi kabel, i have put the date 03/03/2004 in a worksheet in various cells, i woul like to replace that date with 03/04/2004. pls help me -- Message posted from http://www.ExcelForum.com Hi try the following - goto 'Edit -Replace' - enter your existing date and your new date P.S.: please stay in the original thread P.P.S. My first name is Frank :-) -- Regards Frank Kabel Frankfurt, Germany > hi kabel, > i have put the date 03/03/2004 in a worksheet in various cells, i > would like to replace that date with 03/04/2004. > pls help me. > > > --- > Message p...

Validate Purchase Order Date
We were able to enter a PO (against a Project) dated 2004, several years before we started on GP, so I'd guess that (because this is non-posting) the date isn't validated against the fiscal periods that have been set up. However, this lead to a Project Begin Date of 2004, and caused an error when we tried to enter a fixed fee amount against the project "Fiscal information does not exist for the date range. The fiscal year must exist in order to create the periodic records". The only way I found to fix this was to go into SQL table PA01201 and fix the Project's b...

How do I sum dollars in one column based on dates in another?
How can I sum the total numbers contained in one column based on dates in another column? Example: I need the total of 5 units sold int the month of January 2006. I have the sale ammounts in one column and the closing dates in another. What forumla would I use for this? Thanks in advance for any help. =SUMPRODUCT(--(MONTH(B2:B50)=1),--(YEAR(B2:B50)=2006),A2:A50) for January change 1 in the month part to a different month -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Alan" <Alan@discus...

Outlook 2003
Hi, i'm using outlook 2003 with my pop3 company account, with all my personal forlders, account, calendar ,etc... I'm using an exchange account with another company via OWA, with its shared and personal folders ... Can i use both account in Outlook 2003 ??? i tried but i can only use one "personal folders" shared by two accounts ... i want to take accounts separated in toe root "Personal Folders". hope explain it clearly ! ugh! thanks in advance for ideas and support roy Inviato da X-Privat.Org - Registrazione gratuita http://www.x-privat.org/join.php you wou...

time conversion #5
I need to convert elapsed times from hours & minutes to total minutes. For example 10:35 to 635. I tried making a vlookup table to do it, but it sometimes returns a wrong value. Help? Either format the cell as [mm] or use a formula like =A1*24*60 and format as general. -- Regards Juan Pablo Gonz�lez "Pierre" <pgadmer@ix.netcom.com> wrote in message news:0c3c01c4ad75$8957fb80$a501280a@phx.gbl... > I need to convert elapsed times from hours & minutes to > total minutes. For example 10:35 to 635. I tried making a > vlookup table to do it, but it someti...

DLL-Hell: delay-load dependency problems only the second time
I have a problem with strange symptoms. My application loads my DLL the following way: typedef bool (*FooPtr) (void); { HINSTANCE hInstDll = AfxLoadLibrary(lpszModuleName); if (!hInstDll) return; // fails the second time we come here FooPtr pFoo = (FooPtr) GetProcAddress(hInstDll, "Foo"); bool bRetVal = (pFoo)(); AfxFreeLibrary(hInstDll); } It works fine on many computers, it works the first time executed on every machine tested. On some machines, executing this code after a short time again, it fails. I have observed it on both Win2k and WinXP. Using DependencyWalker...

Formula calculation in a Shared Excel File
I have a Shared excel workbook which has data entered though the month by multiple people, in this file I have a number of formulas producing data about the entries. The issue I am facing is that when any of the users open or save the new entries the file takes quite a while to re-calculate the formulas. I know I can use the Calculation tab in options to manually calculate the fields but this only works at an individual level rather than in the actual workbook and it also will apply to evey excel file we work on. Does anyone know a solution to have a selected range of cells with formua...

Calculate difference between 2 date and times with average
I have used the formula: Start time End time 6/9/2007 10:35 AM 6/9/2007 3:30 PM 4:55 Formula Description (Result) =Text(b1-a1,"d:h:) But when I use the text function, I cannot calculate an average. Also, if you know a way to calculate the difference and exclude weekends... As for excluding weekends, investigate the NETWORKDAYS formula in Help. But if your workday is not 24 hours long, and you have holidays to contend with in addition to weekends, the problem is fairly complex. Here are the steps: 1. If StartDate and EndDate are on the same date, hours worked = number of hou...

Query Date Help W/Avg work days
please note that in order to get the date 6/28/1979 I am using average work days each month of 21.75 I have tried to Add just the total days to the date but it's not correct because I need it to be based off the 21.75 days per month not actually days. Total Years of Prior Service Days Hire Date Adjusted Hire Date 7 years 4 months 6 days 2685 6/15/1987 2/7/1980 (s/b 2/9/80) 1 year 8 months 13 days 620 2/10/2005 6/1/2003 (s/b 5/27/03) This is what is being calculated so far once I run the query. "aldunford" wrote: > This worked great thanks so much!! I have ...

Adding hours scheduled for week
I have created a work schedule with start/end time cells formated hh;mm AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a formula for the "total" cell that will add the weekly shifts. Ex; Monday 10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in) and it will work for one day, but I can't figure out how to add the remaining work days - all it get is errors. Please help! It would be useful if you showed us the formula you were using. Excel treats times as numbers, so all arithmetic operations work on them...

Workflow - date and time
hi I have a field called enrolment date which is usually lesser than the created on date. i want a workflow which says, when a new contact is created wait for 5 days after enrolment date and then create - 1st mailer task wait for 2 months and create - 2nd mailer task. i have got a workflow and it says when a new patient is created wait 5 days after contact.enrolmentdate if case 1 create mailer 1 end if if case 2 create mailer 2 end if however, both mailer tasks get create right away rather than waiting for 5 days and 2 months. in the wait for timer i ticked revaluate expressision whe...