Formula for computing work time in Excel

Hi,

For instance, A1 = 8:20, B1 = 16:30.
I need to compute in C1 the following: B1 - A1 - 0.5 hour.

What is the formula?

If I use formula B1 - A1 - 0:30 I get invalid value.

Thanks,

-- 
 Alex Vinokur
     email: alex DOT vinokur AT gmail DOT com
     http://mathforum.org/library/view/10978.html
     http://sourceforge.net/users/alexvn





0
alexvn1 (6)
9/20/2005 5:11:54 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
451 Views

Similar Articles

[PageSpeed] 42

=B1-A1-TIME(0,30,0)

-- 
 HTH

Bob Phillips

"Alex Vinokur" <alexvn@big-foot.com> wrote in message
news:uGyW$ZgvFHA.2008@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> For instance, A1 = 8:20, B1 = 16:30.
> I need to compute in C1 the following: B1 - A1 - 0.5 hour.
>
> What is the formula?
>
> If I use formula B1 - A1 - 0:30 I get invalid value.
>
> Thanks,
>
> -- 
>  Alex Vinokur
>      email: alex DOT vinokur AT gmail DOT com
>      http://mathforum.org/library/view/10978.html
>      http://sourceforge.net/users/alexvn
>
>
>
>
>


0
bob.phillips1 (6510)
9/20/2005 5:49:31 PM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:%2310nougvFHA.3100@TK2MSFTNGP12.phx.gbl...
> =B1-A1-TIME(0,30,0)

Thanks, Bob!

It works fine.

[snip]

-- 
 Alex Vinokur
     email: alex DOT vinokur AT gmail DOT com
     http://mathforum.org/library/view/10978.html
     http://sourceforge.net/users/alexvn



0
alexvn1 (6)
9/20/2005 5:57:38 PM
That's because time is really just a floating point number.

1 is 1 day
and hour is 1/24
a half hour is 1/48

=B1 - A1 - 1/48

will work just fine
-- 
Gary''s Student


"Alex Vinokur" wrote:

> Hi,
> 
> For instance, A1 = 8:20, B1 = 16:30.
> I need to compute in C1 the following: B1 - A1 - 0.5 hour.
> 
> What is the formula?
> 
> If I use formula B1 - A1 - 0:30 I get invalid value.
> 
> Thanks,
> 
> -- 
>  Alex Vinokur
>      email: alex DOT vinokur AT gmail DOT com
>      http://mathforum.org/library/view/10978.html
>      http://sourceforge.net/users/alexvn
> 
> 
> 
> 
> 
> 
0
GarysStudent (1572)
9/20/2005 5:58:04 PM
Reply:

Similar Artilces:

SBS08, interesting RWW Computer connection interruptions
I have just started to notice a strange difference between RWW on SBS 03 and SBS 08.... SBS 08 opens an SSL window that it states must remain open in order to keep your Computer/Server connection (remote desktop) remotely. By default, most firewall rules have a inactivity time-out period of XX minutes. In this case, the SSL window doesn't have any traffic once the RWW RDP session is created, at least none that I can see. So, unlike SBS 03, when you connect to a computer using RWW in O8, and the SSL timeout is set for 10 minutes on the firewall, you will be disconnected eve...

Disabled Person Needs Icons to work
Hi, My sister is a quadraplegic and and is running win7 64bit on a dell laptop Inspirion 1750. Her desktop icons do not work and her shot cuts do not work. She needs to work from the dektop because we need as few clicks as possible. I have tried to find the solution but I have have not. Can someone help? Do you mean that when you click, or double click, on an icon that nothing happens? Is she using a mouse or the touchpad? Can you be a little more descriptive about the problem? Has this been happening from the time you got the laptop, or installed Win 7? Or, did it ju...

Registration doesn't work
Just installed Office 2004. After filling out much information, it asks for the Product I.D. number. With the "About box" in Excel 2004 open, I very carefully copied the Product I.D number to the form (and had a second person verify). I keep getting the error message "invalid Product I.D." I've cleared the box and re-entered (again having a second party verify) with the same results. This is a legitimate copy that I purchased via mail order. In article <be362d5d.0405190410.1a215db@posting.google.com>, crabuback@aol.com (Craig Buback) wrote: > Just insta...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

how do i enable dde in excel?
i am posting a DDE link into an excel cell from an external program and getting the message ' DDE Not enabled' in the cell once the link is pasted in. how do i enable DDE? ...

after saving to cd many times. now says read only?
i have saved many times to my cd. i overwrite it almost daily (CD-RW) all of a sudden it now say's the file is read only. although i can still save to my hard drive? nsturre wrote: > i have saved many times to my cd. i overwrite it almost daily (CD-RW) all of > a sudden it now say's the file is read only. although i can still save to my > hard drive? Unless you have formatted your CDRW for packet writing, then yes, it will be Read only. CDROM = CD Read Only Memory! -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk Just ...

Work Order Vs Backorder
I am trying to establish an easy way for my cashiers to take special orders for items not in database. I have found helpful posts that suggest using WO's. However (if I am understanding correctly) a workorder cannot be used in combination with items currently in stock. Ex: customer buys a dog collar off the floor and wants to buy a lead that we have never carried. Can both items be placed on same transaction? I really do not want to make the customer make two separate purchases. It seems using a generic SKU on a backorder would allow me to do this. Are there any pitfalls here...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

new email notification doesn't work?
In Outlook 2003, I have the option set for the semi-transparent notification window to pop up when I get new email, but it never does. Is there something I'm not doing right? Does it only come up in certain situations? Btw, I'm using the regular Inbox with a POP3 account. -- John Salerno Do you have rules moving your email to folders after receipt? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searchi...

Protecting a sheet (yet allowing buttons/macros to work)?
I'm new to the button/macro feature of Excel (even though I've been assigning macros to _toolbars_ for years <g>!) So, I don't even know if what I need to do can be done: Ideally, it would be nice if I could protect all the sheets or even better the workbook in one fell swoop, so that no-one could accidentally delete the buttons YET that would still allow the macros to work. I've tried everything I know and the best I seem to be able to do, yes, protects the buttons but I the macros stop working and display a ""Run-time error '1004'". T...

portfolio value over time
I run Money 2004 and i can't do some things i think are really basic - main two (related issues) that bother me are: 1) is there any way to view the value of my entire investment portfolio over time (e.g. as a chart showing basically just price * volume of shares i own for each investment, combined to show all investments together)? 2) is there any way to view price history for all my holdings as an indexed value (0% at the start growing up or down based on % change in price) as opposed to absolute value? Some shares are $20 each some are $200 each - doesn't make sense to sho...

Headers in Excel #3
Can you tell me if it's possible and if so, how I can change the header cells, i.e. A B C D E ... etc? Thanks. Andrea (508) 842-3880 Hi Andrea no this is not possible. The best workaround you can achieve is: - use row 1 as header - goto cell A2 and to the menu 'Windows - Freeze Panes' to always show row 1 - in 'Tools - Options - view' you can hide the row and column headers -- Regards Frank Kabel Frankfurt, Germany "Andrea" <shrews.receptionist@nitco-lift.com> schrieb im Newsbeitrag news:12ff301c44354$b88982b0$a501280a@phx.gbl... > Can you tell ...

Grouping will not work
I have data in columns A through FG and in rows 1 to 105. I have the months as the column headings, and each Jan-November is grouped to collapse into December using the Group feature on the Data menu. When I go to collapse the view to only display the December columns, after I collapse about 3 of the groupings, excel errors out and tells me it cannot collapse any additional groups because it will have to shift objects off-sheet. How can it be that I ave over 90 unused columns and I am actually making the viewing area less yet still getting tis error. I have tried closing and re-open...

Excel -> Access Import Bizarreness
A guy gave me an Excel file that he needs me to covert to XML. No problem. Slurp it into a database and use a DataSet.WriteXML() call to turn it into an XML file. But I can't import it into Access because it has "Merged Cells". For example, data that should look like this: ColA ColB ----- ----- Rec1 this Rec2 this Rec3 this Rec4 that Rec5 Other The three "this" cells have been merged into one tall cell: ColA ColB ----- ----- Rec1 this Rec2 Rec3 Rec4 that Rec5 Other I need to "unmerge" these, but I know almost nothi...

Is this possible within Excel 2000?
Hi, I've used Excel on a get by basis for many years at work, and I'm just beginning to realise its vast capabilities, having viewed several groups which I didn't know existed previously. I subsequently would like to ask if it is possible for Excel to look at the numerical or text content of a cell on one worksheet and then look at a predefined range on another worksheet and find the equivalent cell content and then move the cursor to that cell? This has me stumped presently. Thanks in advance for any assistance. J.J. To actually move the selection, you'd need to use a litt...

Excel 2000 fail to open workbook
Hi, I have one Excel template. If we open it on Excel 2000, the Excel will hang, and the process occupy cpu 95% ~98%. But we can open this tempalte in Excel 2003 normally, and if we save this tempalte in Excel 2003, and then open it in Excel 2000, it work normally too. Excel 2000 (9.0.3821 SR-1) Excel 2003 (11.8231.8221) sp3 Would you help us to find the reason for this issue? Thanks, Nancy Hi Nancy, Perhaps there was some (probably minor) corruption/inconsistency in the workbook that Excel 2003 could fix but Excel 2000 couldn't. -- Cheers macropod [Microsoft MVP - Word] ...

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

M2003 Deluxe not responding when I try to do tax work
When I try to go into Tax Estimator and click on a link there, or look at tax reports, M2003 Deluxe freezes. What is the fix? It may well just be that you have to wait longer. Have you tried that approach? My 2.2 GHz P4 takes the better part of a (not responding, 100% CPU, hourglass, et al.) minute to get into Tax Estimator on M04. (And then the text rendering is trashed besides.) M03 was no faster. "dutchman001" <dutchman001@email.msn.com> wrote in message news:uKHHC9I0DHA.3196@TK2MSFTNGP11.phx.gbl... > When I try to go into Tax Estimator and click on a link there, or ...

Need Help With A Formula Please
Hello and thank you in advance for your help! I have a Name on a column. The same name is listed multiple times on another sheet. However, one of the entries has the word DSL on the colmun next to it and on the next colum a value. I want to count those values. For example: On sheet 1 I have: Mike on A2 On sheet 2 i have: A2= Mike B2= Sales c2= 5 A3= Mike B3= DSL C3= 8 A4= Mike B4= Parts C4= 10 I want a formula of sheet1 that will return a value of 8 for Mike by matching the word Mike and DSL on sheet 2. Please helppppp!!!! Thanks!! -- EVRPAGING -------------------------------...

Excel prints some sheets very small
When I try to print multiple sheets of the same workbook at the same time, one of them (a different one each time) prints very small (about an inch tall) in the upper left hand corner. I am printing from a network file but I don't know if this has anything to do with it or not. Please HELP!!! ...

how do I insert rows without changing formula
In a cashflow worksheet how can I add rows without having to re-do all the proceeding formulas. use absolute references e.g. =$A$5 -- HTH RP (remove nothere from the email address if mailing direct) "tinka" <tinka@discussions.microsoft.com> wrote in message news:4ED4569E-0FD6-469B-82B1-F30139B1A280@microsoft.com... > In a cashflow worksheet how can I add rows without having to re-do all the > proceeding formulas. ...

how to get the system setup time.
I want to get the system setup time. but this code didn't work! CreateFile always return -1, and the error code is 0x00000005!(Access is denied.) TCHAR path[MAX_PATH]; if (GetWindowsDirectory(path, MAX_PATH)==0) break; HANDLE h = CreateFile(path, GENERIC_READ, FILE_SHARE_READ|FILE_SHARE_WRITE, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL); if (h==INVALID_HANDLE_VALUE) break; // always return Access is denied.! FILETIME ft1, ft2, ft3; if (!GetFileTime(h, &ft1, &ft2, &ft3)) break; thank you ! Buried in the MSDN documentation for the CreateFile function, there...

EXCEL 2007 acting up
j$ = "THIS MACRO WILL ERASE THE SECOND & THIRD SET OF DATA" + Chr$(13) + Chr$(13) Using EXCEL 2007, I get an error with the line above as a macro. It does not recognize CHR$. What am I doing wrong? Thanks Not for me it didn't. Check for MISSING references in Tools>References, uncheck them if there are any. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pcor" <pcor@discussions.microsoft.com> wrote in message news:F63F6E95-AF0A-4927-B337-99B5C48B92B1@microsoft.com... > j$ = "THIS MACRO WILL...