CALCULATE DATE AND TIME

hOW DO i calculate the total time between two columns that contain a date and 
a time.

0
PamC (10)
4/4/2006 1:27:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
524 Views

Similar Articles

[PageSpeed] 29

Just subtract one from the other.

If you want it in hours, format it as [h]:mm. If you want days and it will
not exceed 31 days, format it as d hh:mm:ss. If it can be more than 31 days
you will need something like

=(INT(A1-B1)-(MOD(A1,1)>MOD(B1,1))&" days
")&TEXT(MOD(MOD(B1,1)-MOD(A1,1),1),"hh:mm")

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pam C" <PamC@discussions.microsoft.com> wrote in message
news:78112373-65A9-450D-AE88-F04D9584850E@microsoft.com...
> hOW DO i calculate the total time between two columns that contain a date
and
> a time.
>


0
bob.phillips1 (6510)
4/4/2006 1:50:02 PM
my date is a2 3/28/06 19:11 and in b2 3/28/06 22:40
I tried the formula you gave me and it isn't working, I just want a number 
in hours and minutes.

Thanks,

"Bob Phillips" wrote:

> Just subtract one from the other.
> 
> If you want it in hours, format it as [h]:mm. If you want days and it will
> not exceed 31 days, format it as d hh:mm:ss. If it can be more than 31 days
> you will need something like
> 
> =(INT(A1-B1)-(MOD(A1,1)>MOD(B1,1))&" days
> ")&TEXT(MOD(MOD(B1,1)-MOD(A1,1),1),"hh:mm")
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "Pam C" <PamC@discussions.microsoft.com> wrote in message
> news:78112373-65A9-450D-AE88-F04D9584850E@microsoft.com...
> > hOW DO i calculate the total time between two columns that contain a date
> and
> > a time.
> >
> 
> 
> 
0
PamC (10)
4/4/2006 2:29:25 PM
So just subtract the later from the other and format as [h]:mm

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pam C" <PamC@discussions.microsoft.com> wrote in message
news:96118C13-44F2-4482-922A-CFC5ECF9BE59@microsoft.com...
> my date is a2 3/28/06 19:11 and in b2 3/28/06 22:40
> I tried the formula you gave me and it isn't working, I just want a number
> in hours and minutes.
>
> Thanks,
>
> "Bob Phillips" wrote:
>
> > Just subtract one from the other.
> >
> > If you want it in hours, format it as [h]:mm. If you want days and it
will
> > not exceed 31 days, format it as d hh:mm:ss. If it can be more than 31
days
> > you will need something like
> >
> > =(INT(A1-B1)-(MOD(A1,1)>MOD(B1,1))&" days
> > ")&TEXT(MOD(MOD(B1,1)-MOD(A1,1),1),"hh:mm")
> >
> > -- 
> >  HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Pam C" <PamC@discussions.microsoft.com> wrote in message
> > news:78112373-65A9-450D-AE88-F04D9584850E@microsoft.com...
> > > hOW DO i calculate the total time between two columns that contain a
date
> > and
> > > a time.
> > >
> >
> >
> >


0
bob.phillips1 (6510)
4/4/2006 3:09:47 PM
Reply:

Similar Artilces:

Iterative Calculation help needed
I have the following information A1 - Balance at the beginning of calculation A2 - Annualized payment A3 - Interest rate divided by # of payments per year A4 - Number of years * # of payments per year I'm trying to calulate the total ((A1-A2)*(1+A3)) - I need to do this over and over up until I've don it A4 times. I can do it for each period but I want to do it all in one cell, i possible. How can I have it plug the new number into A1 and repeat th calculation A4 times. Thanks -- Message posted from http://www.ExcelForum.com Hi there You might find that the PMT function coul...

Comments on Manual Calculation on certain sheets.
I have a sheet in a workbook with array functions that slows down performance with calculation on automatic (which I need for the other sheets). Would it not have been useful to be able to set a single sheet to Manual and update when needed in the same way Pivot Tables work? One way to work around this is to save the Array Formula sheet in another workbook. I have browsed some of the posts on Excel's way of handling Automatic/Manual recalculation and it seems as if there is room for improvement or are there important reasons that I am not aware of? Any comments. Laurence Lombard Hi ...

sales line detail window-after updating the request Ship Date-also
I am also using manufacturing, when you change the requested ship date, you get a window asking: You can update the In House due date, What is the name of this window in modifer? I would like to modify this form, so the No button is disables, I want to make sure they always click 'yes' How do I find this form in modifer? the form name is just 'Microsoft GP Dynamics' thanks -- Doug ...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

writing calculated fields
I am trying to avoid starting a Crystal report from scratch, please help me! I need to use the equivalent of the "If, Then, Else" formula in Crystal reports in Report Writer. I need my PO in report writer to: if {IV00101.UOMSCHDL}="xxxxx" then (PO ordered QTY * 30) else if {IV00101.UOMSCHDL}="xxxxx" then (PO ordered QTY * 40) else PO ordered QTY I can only place PO's to the vendor in eaches but I am entering the PO into GP in cases. I have the PO processing side setup correctly, I just need the PO to print now. Thank you! -- CK Create two new calculate...

Behind the times
Behind the times, I know. I have recently graduated from Office 2000 to 2003. In 2000, when I would insert a picture I could format it with one or more items (line, line color, position, etc) and then move on to the next jpg, hit the F4 key and the photo would have the same formatting. That doesn't seem to work in 2003. Help files discuss "repeat" on the edit menu, but it doesn't seem to work on a pic. What am I missing here? This is going to be a pain to format each picture individually. I don't have 2003 handy to check, so I'm not sure what t...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Excel date changes
I have created a spreadsheet using excel which requires the use dates on a monthly basis (similar to a calendar). The problem I have is that every month I have to go in and change the dates on the spreadsheet to reflect those of the current month before printing. Is there a way I can make it so that excel does that for me every month. Thanks, I'd really appreciate the input. Please elaborate on what the spreadsheet looks like. "phonescoop@hotmail.com" wrote: > I have created a spreadsheet using excel which requires the use dates > on a monthly basis (similar to a cal...

Can excell notify me of due dates?
If I have due dates on my worksheet can I set something up so that I am notified on these dates? I've used this to help me. http://www.contextures.com/xlCondFormat01.html "Deion007" wrote: > If I have due dates on my worksheet can I set something up so that I am > notified on these dates? ...

Dates #12
I have a rather looooong list of dates (mm/dd/yy). In a column beside it I want just month and year so I can, for example, find all books sold in a month range. Can you help? NO need where rngA has your dates and rngB is a count of the sales =sumproduct((year(rngA)=2004)*(month(rngA)=8)*(rngB)) to sum rngC if it had the price total for each date =sumproduct((year(rngA)=2004)*(month(rngA)=8)*rngC) -- Don Guillett SalesAid Software donaldb@281.com "Lyndie" <anonymous@discussions.microsoft.com> wrote in message news:83fd01c48543$7d211680$a501280a@phx.gbl... > I have a ...

screen freeze after long time running
Hi I have a program written in Microsoft Visual C++ .NET . Its interface is based on CFormView. It includes three tabs to show information and plots. The plots are made by BitBlt() which copies data from the vectors to the screen around every 5 minutes. The problem is that after the program runs continuously for more than 3 days the screen freezes. Nothing is updated. I have to minimize the window and it may return to normal look. Some static labels on the interface also freeze or appear strange look. Does anyone have similar experience ? Thank you for your hints or suggestions! <e...

automatically sending messages at set times??
Wondering if there's a way with Outlook to send a default message at a specified time?? ie: a message for my ex to repay me her debts on a weekly basis :P "Adam Membrey" <membreya@hotmail.com> wrote in message news:O$2Z4y0zEHA.3416@TK2MSFTNGP09.phx.gbl... > Wondering if there's a way with Outlook to send a default message at a > specified time?? > > ie: a message for my ex to repay me her debts on a weekly basis :P Sure if you are willing to write code but chances are it would be easier to do from the command line with one of the email utilities like ...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

Open excel sheet twice at same time?
Hi, Probably a question asked all the time, but I often have trouble opening a file after making a desktop shortcut to an excel file. When opening, it gives an error message about opening a file with the same name at the same time (even though I'm not). The error message continues by saying that it doesn't matter if the files are in different folders and suggests renaming one of the files. Any ideas? Thanks. Robert No reason I can think of that would cause Excel to load the file twice irrespective of launching it from a shortcut. Couple of things to check: - The file isn'...

CLick on Box to enter time of day
I have a worksheet where payment is entered in column C. I would like to use Column B as a time stamp. In other words as soon as data is entered in C5 then the exact time comes up in B5. When data is entered in C6 then the exact time cones up in C5. I do not want this time to change even when the page is saved and opened later on. Elfego You can enter a static time in a cell by hitting CTRL + SHIFT + ;(semi-colon) You could also use event code to enter a static date when you enter something in a cell. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in ...

Time entered (and handled as data) in simple minutes and seconds.
Using Excel 2003. How can I get Excel to display and handle entries for duration in minutes and seconds only (without having to enter a 4'30" entry as 0:04:30)? I have gotten as far as getting it to display as "04:30.0", but all I need is single digit minutes (data on this spreadsheet will never go over 9:59) and double digit seconds (and to be able to enter them simply as in "4:30"). I couldn't find that as an option in the ones listed for time in the cell format dialogue box. Right now I have to enter 0:04:30 in order to get it to work right. Is t...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

Automatic calculation
Hi I have a workbook that takes an eternity to calculate and therefore, have the calculation mode set to manual. I would like the calculation mode set to Automatic when the workbook is deactivated or closed, but do not want the workbook to be calculated when this happens. Is that at all possible? Thanks in advance Ben You could use workbook events that fire when the workbook is activated/deactivated. Rightclick on the excel icon (to the left of File|Edit|...) on the menubar. Select view code and paste this in the code window. Option Explicit Private Sub Workbook_Activate() Applica...

Microsoft Business Portal Project Time and Expense Software Compon
I'm interested in the time and expense functionality (called "Business Portal Project Time and Expense") that is described under "Dynamics GP" domain. However, I do not readily see the relationship between "Dynamics GP" and "Microsoft Business Portal Project Time and Expense." What Microsoft products do I need in order to reach my goal of operating "Business Portal Project Time and Expense"? Many thanks. In terms of what you need to purchase, the answer depends upon what you have an what licensing method you are using: The Time and...

CComboBox shows only one item at a time !
Hi All, I am using CComboBox control and have tried both dropdown and dropddown list styles. The problem is that I have several items in the combo box but dropdown shows only 1 at a time (i.e. the arrows of v-scrollbar are stuck together).. How do I remedy this ? Thanks in advance. Ashish Got it... Thanks "Ashish" <abc@def.com> wrote in message news:eqRb$LSyDHA.2304@TK2MSFTNGP12.phx.gbl... > Hi All, > I am using CComboBox control and have tried both dropdown and dropddown list > styles. The problem is that I have several items in the combo box but > dropdown sho...

Change the format of my footer {date} option?
I would like to show the date in my footer as January 25, 2005 instead of the default 01/25/05 Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub put this in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "Tori" <Tori@discussions.microsoft.com> wrote in message news:817363F1-32AB-4528-B1D6-304F8E712EA8@microsoft.com... > I would like to show the date in my footer as January 25, 2005 instead of the &g...

IS Mailbox Average Delivery Time
We are using HP Open View and are receiving messages stating: “The IS Mailbox database instance 'SG2-Front-House'Average Delivery time (13228 secs) for last 10 messages is too high (>=10 secs)” We receive an alert approximately 4 times an hour with the seconds ranging anywhere from 100 to 30,000. We had restriction set on a SMTP connector at one point. We removed these to see if this was the cause. Nothing has changed. I have looked online, but can’t find much information as to what might cause this. One theory I was leaning towards was the fact that we have some users w...

sorting activities for each lead/contact by actual end dates
When I view the activities associated with each lead and contacts, the actual end date are not shown. Does anyone know how I can dispaly this field? The activities list in workplace does show the actual end dates but we'd like to see that when we access the list through indivial contact/lead record. Thanks, Victoria Hi Victoria, There is no good out of the box way to do this in MSCRM today. We're working hard to fix this for v2.0. Many of our customers have worked around this issue by registering for a post call out on the activity creation, and then having a custom object wh...

Pasted fractions converted to dates
I'm trying to copy a table containing text, decimals and fractions from a word table into excel. How can i stop the fractions from becoming dates when pasted? ie. 1-5/8 becomes 1/5/2008 and 9/16 becomes 16-Sep. Its the hyphen that is causeing the trouble. A fraction in Excel has the form 1 5/8 with a space between the integer and the fractional parts Can you get rid of the hyphen in the Word doc? best wishes Bernard "Jon_C" <JonCCrouch@gmail.com> wrote in message news:1192110561.240426.147550@o3g2000hsb.googlegroups.com... > I'm trying to copy a table containin...

Auto copy dates from one cell to another
I have a multiple page worksheet. In A8 is for the Employee Name and in B8 is for the date. I would like for this information to automatically be placed at A96 and B96 which is the top of the second page within the worksheet and so on for the remainder of the pages. Position cursor in cell A96 and enter the formula: =A$8, and in B96 the formula =B$8 Copy these two cells to the appropriate cells on the other "pages" of your worksheet. Pete Hi Kelly, In cell A96 you fill out the formula =A8 In cell B96 you fill out the formula =B8 this formula tells excel to take the val...