determining time differences

How do I write the equation to calculate time differences 
of two cells where the time difference can span across 
midnight? Is there an if then else function, if so how do 
you write it
Excel dose not recognize this as a formula"=(if(B10 > 
A10) then =(B10-A10) else =(B10+24-A10))"

a1 = 23:45
b1 = 01:15
c1 = b1-a1

Thanking you in advance,

Hugh Newman
0
anonymous (74722)
3/5/2004 1:41:16 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
391 Views

Similar Articles

[PageSpeed] 51

Hi
try the following in C1
=A1-B1+(B1<A1)

-- 
Regards
Frank Kabel
Frankfurt, Germany

Hugh wrote:
> How do I write the equation to calculate time differences
> of two cells where the time difference can span across
> midnight? Is there an if then else function, if so how do
> you write it
> Excel dose not recognize this as a formula"=(if(B10 >
> A10) then =(B10-A10) else =(B10+24-A10))"
> 
> a1 = 23:45
> b1 = 01:15
> c1 = b1-a1
> 
> Thanking you in advance,
> 
> Hugh Newman
0
frank.kabel (11126)
3/5/2004 1:46:41 PM
Reply:

Similar Artilces:

Send email from different user.
Does anyone know if it is possible to configure the email so that I can send an email from different CRM user while I am logged in as myself in CRM (make it look like it's coming from another user like I can can do in outlook) If you use Direct E-mail (Actions > Direct E-mail), with an E-mail template, you can do this. >-----Original Message----- >Does anyone know if it is possible to configure the email so that I can send >an email from different CRM user while I am logged in as myself in CRM (make >it look like it's coming from another user like I can can do in...

User time out
Does anyone know of a way to have the system automatically time out a user after a stated period of inactivity in GP? I want to implement this for selected user(s) in GP10.0 on SQL2005. Rockton's Omni Tools can do this: http://www.rocktonsoftware.com/products.aspx?p=8. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "VGrinam" <VGrinam@discussions.microsoft.com> wrote in message news:30080784-EC3A-4D9B-9AD4-E7604F85D2F6@microsoft.com... > Does anyone know of a way to have the system automatically time out a user > after a stated period of inactivit...

Is microsoft having a lottery in the united kingdom at this time,
Is microsoft having a lottery winning notification at this time and are they charging a fee for the delivery and insurance for the winning prize. 1. Wrong newsgroup, but I don't think there is a group for Microsoft scams. 2. You already know the answer to this question, don't you? If not, you really shouldn't have a computer with Internet access. -- JoAnn Paules MVP Microsoft [Publisher] "lonerider01" <lonerider01@discussions.microsoft.com> wrote in message news:11E06343-7B33-44F7-BE9A-C110CC30BD01@microsoft.com... > Is microsoft having a lottery winn...

Creating a Combined Date/Time Shortcut
I know Excel provides shortcuts for entering the date (CTRL + ;) and the = time (CTRL + SHIFT + ;), but I really need one that will enter both the = date=20 and time in the same cell at the same time. (Since Excel does provide=20 formatting for same-cell date and time, it seems kind of odd that there = isn't=20 a shortcut to facilitate entry.) Is it possible to create an entirely = new shortcut=20 that doesn't use macros? (This last point is important, since the = workbook is=20 shared on a network.) Any help anyone can offer would be much appreciated--I'm getting=20 desperate. T...

How to determine adapter driver name from disk handle?
I'm attempting to do something that seems like it should be very easily doable, and yet... not. I have a list of loaded drivers. From that I can also determine their PCI location. I have a list of adapters, but not neccessarily their drivers, friendlynames, etc (unless they're SCSI and then there are some direct registry reads to CurrentControlSet that can work.) I have a list of drives and their handles. I can get the "adapter number" that they're attached to. I can get their SCSI address. Is there any way to correlate the drives and adapt...

MDI within Doc/View differences between VC++ 6.0 and VS.NET 2003
Hi All, Can anyone help to explain why the follow code below behave differently in VC++ 6.0 and VC++ in VS .NET 2003? In 6.0 I can only see two black spots, wheres it execute without any problem in .NET :( void CChildView::OnPaint() { CPaintDC dc(this); // device context for painting CBrush rbrush, gbrush; rbrush.CreateSolidBrush(RGB(255, 16, 255)); gbrush.CreateSolidBrush(RGB(255, 128, 255)); dc.FillRect(CRect(0, 0, 100, 100), &rbrush); dc.FillRect(CRect(50, 50, 150, 150), &gbrush); } I have been trying to create my own palette, but failed to realise as well. You clue is ...

Outlook Live 2003 re: reply to msg sent to different e-mail account
Issue: I want a different e-mail account to display to recipient I'm just subscribed to Outlook Live and using Outlook 2003. I simply want to configure Outlook 2003 as I have my Outlook Express configured (ie receipient sees the same "reply to" e-mail address no matter which e-mail account I use to send the original e-mail). For my POP3 account, I easily configued Outlook 2003 to work using a different "reply to" address. However, for the Hotmail account (http radio button) I don't have the same options available to change the "reply to" address. Rev...

Different page breaks
Why is it when I open the same file on different computers the page breaks move to different locations? I have tried this on three computers on the same network accessing the same file. All three have the same OS (Win 98 SE) and the same Excel (Office 2000 Pro) and they all have the same printer driver (HP 4000 PCL 6). Two of the computers are printing on the same printer (networked, not shared)over the network. I am having a similar problem at another office located in another city. Hi maybe different printer setups on the differen PCs? -- Regards Frank Kabel Frankfurt, Germany &...

Converting time into another time zone
I was helped here once before. I'm given reports but they are in a different time zone. I need to make that data work for me with what time it would be in my time. I was given the following string. =B2+C2-TIME(6,0,0) B2 is the time & C2 is the date. My output string is the East Coast Time below. Time Date East Coast Time 22:44:18 1/12/2009 Monday 1/12/09 16:44:18 Works like a charm. Issue now is that I'm taking 24h time from one time zone and converting it to 24h time in my time zone. I'd prefer that my time zone be put into...

Showing Appointment times
Outlook 2003 SP2 XP2 / SP2 A user enters appointments into the calendar. In DAY view, some of the completed entries show the time of the appointment and others do not - these items were entered one right after the other on the same day, and on the same day of the month (one at 8:00 am the other at 2:30 pm). (In month and week view, the appointment time displays on all entries.) Can you shed any light on how/why this might happen and how we can fix it? Thanks MUCH in advance!!! C.H. *tap* *tap* *tap* - Is this thing on? Can anyone help me with my question? ...

Should SSL Certificate Hostname=FQDN if MX record is different?
Hi, if I have MX records for "webmail.domain.com" but my Exchange host has a FQDN of "mail2.domain.com", will I have a problem with the certificate name not matching the address of the server and failing the SSL certificate authorization? Or would it be best if I add a new MX record using the FQDN and have users connect to that? Thank you, Will Hi, The certificate name must be the name that the users use when connecting from the internet. This name has nothing to do with the internal fqdn of the server and the MX record. Leif "Will Niccolls" <w...

data pulling multiple times
I have a query that is setup to pull data from a form. When I run the query it is pulling quadruple the data required. I have tried everything to troubleshoot this with no luck. If I have a relationship setup and I create the same relationship in my query will that cause the query to pull duplicated data? Please help... Thanks This may seem picayune, but I think it helps to have a clear picture ... Unless your query is using selection criteria that is entered on a form, your query will be pulling data from one/more underlying tables, not from forms. And if you have a p...

One-Time Multiple Mailing Issue
In Outlook 2007 I would like to mass mail about twenty recipients but do not need to maintain contacts. How can I configure this so that each recieves an email with their email address only. You can put all the addresses in the BCC field then. Depending on where you have written down the addresses, you can also use a mail merge or reply via a rule. For details see; http://www.howto-outlook.com/howto/mailmerge.htm and http://www.howto-outlook.com/howto/messagetemplates.htm#rules -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.ho...

Checking time and inserting a value in a different cell
I have Column H that has been formatted as time (i.e. 1:30 PM). If the value in column H is Less than 07:30 and Greater than 15:30, I would like the number 41079 populated in the appropriate cell in column J. If the test is not False, I would like the value 41078 populated. I have inserted the following formula in H6 ‘=IF(H6>15:30 and <7:30,41078,41079)’, however when I depress the enter key J6 displays #NAME. Can you help? Thanks Pank Hi Pank, I think the easiest thing to do would be to put your low (07:30) and high (15:30) times in a couple of cells somewhere and then refe...

Combo Box
Hi All, I created a combo box that is linked to a set of cells that contain numbers but for which I have selected to format as time i.e. the cells look like this: 12:00 AM 12:30 AM 1:00 AM 1:30 AM 2:00 AM 2:30 AM My problem is that when I select a value with the combo box, it displays them as the number, not as a formatted time and I cannot see which property to edit on the combo box to have the output formatted as time. Any help would be most appreciated! Rob :) How about removing that link to the range and just add the values to the combobox. dim myCell as range dim myRng as range ...

Move emails to a folder rule (Time based)
Hi. I have a rule that moves some emails to a folder based on the senders email. I would like to have outlook move all emails form the folder to a second one that are older than seven days. Any ideas? Thanks. PS, how do you create a custom rule? Rules act on messages when they are received in the in-box or sent. Outlook has no way of going back to move items after they have been received. You could probably write a little VBA routine to do what you want - see http://www.outlookcode.com for some samples or repost in an Outlook programming group for some potential help. --...

Days & time calculation formula in quary?
Hi, Access Users, Good Day, I would like to calculate two feilds in quary for days but formula "DateDiff() is giving correct answer. I would like to have your half, please. Regards, Wajid wajidalibwp@msn.com Wajid <Wajid@discussions.microsoft.com> wrote: > Access Users, Good Day, This is the Outlook newsgroup. -- Brian Tillman ...

Unit of the Perfmon counter Average Time of Delivery
Hi, What is the unit of MSExchangeIS Public\Average Time for Delivery ? It is in seconds or milliseconds. The reason is that I am getting huge value for this like 20150. Practically speaking this value would not go beyond 600 seconds. So how do we know that the counter value that Perfmon returns ?? We have similar Exchange Performance counters for which the unit is not mentioned in documentation. Pls help. Thanks in Advance, Dhana "Dhana" <Dhana@discussions.microsoft.com> wrote: >What is the unit of MSExchangeIS Public\Average Time for Delivery ? It is in >se...

I want to plot 2 different graphs together
I am trying to show my outgoings as a stacked column graph which I have managed to do, but I want to show my income in a column next to the stacked column so I can see if my outgoings have exceeded my income. Hi, Is the incomming a Stacked or plain Column? -- Cheers, Shane Devenshire "georgemc" wrote: > I am trying to show my outgoings as a stacked column graph which I have > managed to do, but I want to show my income in a column next to the stacked > column so I can see if my outgoings have exceeded my income. Try one of the links on this page: http://pelti...

Excel should format time down to the hundredth/sec.
I would like to be able to format the cells within Excel so that if I typed in my athlete's time from a race as 153268 or 3245, it would show up in the cell as 15:32.68 or :32.45 respectively. I would also like to be able to then add or subtract time in that same format. Right now I can only format the cell to display those times as 15:32.6 or 00:00.0 (nothing at all). I do race time analysis with excel but I have to convert the time into all seconds if I want to add/subtract/divide or whatever. Can you add a format that would recognize time not in the sense of how a clock on th...

Increase time allowed for services to start?
How does Windows XP decide all services have started and that it's okay to allow someone to log in? I would like to make it wait a longer time since some impatient users are logging in too quickly and complaining when tray icons (for example safe removal, volume control) never appear. -- Brian Gregory. (In the UK) ng@bgdsv.co.uk To email me remove the letter vee. On Sun, 28 Feb 2010 23:53:04 -0000, "Brian Gregory [UK]" <ng@bgdsv.co.uk> wrote: >How does Windows XP decide all services have started and that it's okay to >allow someone t...

how to calculate the difference of values (numbers) in a column?
A B C D A1 10 A2 9 1 A3 8 2 1 A4 7 3 2 A5 6 4 3 1 I need to calculate the differences of each value in a column and have the answers on second third column. For example subtract A2 from A1, A3 from A1, A4 from A1, A5 from A1; and subtract A3 from A2, A4 from A2, A5 from A2; subtract A4 from A3, A5 from A4; and finally subtract A5 from A4. Any help is appreciated. Hi, try this select B2:B5 and enter this formula in B2 =$A$1-(A2:A5), confirm with Ctrl-Shift-Enter. similarly for C3:C5 =$A$2-(A3:A5) D4:D5 =A3-(A4:A5) hth...

Can I operate several different email addresses from 1 client?
We have separate user accounts set up on our XP platform. My partner prefers to use Outlook Express. I use Outlook. I use ONE email account i.e. with one email address, on Outlook. My partner uses TWO different email addresses, same domain. One for business, one for personal emails. HOW do I set things up so that she can log on to Windows and have her business and private emails either arriving in separate folders, OR business on Outlook, and private email on Express - she'd prefer separate folders. How do you do this, because I am finding that email sent to either of her addresses...

how do I open 2 excel pages on the desktop at the same time
When I open page 1, it appears on the desktop. When I click on page 2, it appears on the desktop, but page 1 disappears. I want both page 1 and page 2 to be on the desktop at the same time. Thank you for helping. Excel doesn't have "pages" It has Workbooks. Do you want two workbooks visible at the same time? Open both and Go to Window>New Window Window>Arrange>Vertical Do you want to see just one but have the other appear on the Taskbar? Enable Option of "Windows in TaskBar" Gord Dibben MS Excel MVP On Wed, 4 Mar 2009 14:32:01 -0800, wilma <w...

Determine first Exchange2000 Server in a Site
Hi! I can't find any article about this topic for Exchange2000 Server. Could need some help. Thanxs! Peter Peter Gamharter <Peter.Gamharter@kk.lkh.ooe.gv.at> wrote: > I can't find any article about this topic for > Exchange2000 Server. Perhaps you'd have better luck in the Exchange newsgroups. -- Brian Tillman ...