Summing negative time

Hi All,

I am having problems adding time values hwen a negative time is
involved.  I have read all the posts on this and have chosen the 1904
date option, but no success.
I determine the difference between times and then subtract a standard
day from this amount to achieve a result (positive or negative).  Then
I want to sum the results.  Works fine as long as I don't have any
negative results.  If I do have negative then the answer is always 0. 


Example:


Morning.............Afternoon..............Balance          
In.......Out........In........Out..........Total..Flex...Balance	
8:15 AM..12:35 PM...1:00 PM...5:00:00 PM...8:20...1:20...1:20	
9:00 AM..12:55 PM...1:00 PM...3:15:00 PM...6:10..-0:50...0:00	
8:30 AM..12:15 PM...1:30 PM...3:00 PM......5:15..-1:45...0:00	
8:00 AM..11:00 AM...11:30 AM..5:00 PM......8:30...1:30...0:00	


I have tried the following formulae:
= L3 + M4
= MOD ((L3+M4),1)
= SUM (L3:L4)

I am not the most proficient Excel user - if anyone has ideas I would
be most grateful.

Deb :)


-- 
Bumblebee
------------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25669
View this thread: http://www.excelforum.com/showthread.php?threadid=390851

0
7/28/2005 6:41:04 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
379 Views

Similar Articles

[PageSpeed] 39

On Thu, 28 Jul 2005 01:41:04 -0500, Bumblebee
<Bumblebee.1svbf9_1122534355.7113@excelforum-nospam.com> wrote:

>
>Hi All,
>
>I am having problems adding time values hwen a negative time is
>involved.  I have read all the posts on this and have chosen the 1904
>date option, but no success.
>I determine the difference between times and then subtract a standard
>day from this amount to achieve a result (positive or negative).  Then
>I want to sum the results.  Works fine as long as I don't have any
>negative results.  If I do have negative then the answer is always 0. 
>
>
>Example:
>
>
>Morning.............Afternoon..............Balance          
>In.......Out........In........Out..........Total..Flex...Balance	
>8:15 AM..12:35 PM...1:00 PM...5:00:00 PM...8:20...1:20...1:20	
>9:00 AM..12:55 PM...1:00 PM...3:15:00 PM...6:10..-0:50...0:00	
>8:30 AM..12:15 PM...1:30 PM...3:00 PM......5:15..-1:45...0:00	
>8:00 AM..11:00 AM...11:30 AM..5:00 PM......8:30...1:30...0:00	
>
>
>I have tried the following formulae:
>= L3 + M4
>= MOD ((L3+M4),1)
>= SUM (L3:L4)
>
>I am not the most proficient Excel user - if anyone has ideas I would
>be most grateful.
>
>Deb :)

I suspect your problem has nothing to do with the presence of negative times.
SUM and other operations work on negative times regardless of the date system.
Using the 1904 date system allows negative times to be DISPLAYED (as other than
#####) but does not change the actual contents of the cell.

I set up your data in cells A3:G6, with the labels in Rows 1 and 2.

In the Total column (E) I used the formula:

E3:	=B3-A3+D3-C3

In the Flex column (F) I used the formula:

F3:	=E3-TIME(7,,)

In the Balance column (G) I used the formula:

G3:	=F3
G4:	=G3+F4

I then selected G4 and copy/dragged down the formula, resulting in:

G5:	=G4+F5
G6:	=G5+F6

This summed the Balance of Flex Times:

  Morning	     Afternoon		       Balance		
In	Out	   In	    Out		Total	Flex	Balance
8:15 AM	12:35 PM  1:00 PM  5:00 PM      8:20	1:20	1:20
9:00 AM	12:55 PM  1:00 PM  3:15 PM	6:10   -0:50	0:30
8:30 AM	12:15 PM  1:30 PM  3:00 PM	5:15   -1:45   -1:15
8:00 AM	11:00 AM 11:30 AM  5:00 PM	8:30    1:30	0:15



--ron
0
ronrosenfeld (3122)
7/28/2005 10:31:07 AM
Much as I try to avoid date/time questions, I can understand why Excel
doesn't like negative time, it cannot exist except in a mathmaticians
head.

Try another column for your flex, flex-under and flex-over and then you
should have no troubles.


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=390851

0
7/28/2005 10:51:08 AM
Deb
Time format can't show negative values. One way is to convert the flexy hrs 
and balance to decimal numbers

Total Hrs in f4 =C4-B4+E4-D4
Flex Hrs F4 =IF(F4<0.2917,(0.2917-F4)*24*-1,(F4-0.2917)*24)
Balance in G4 =SUM($G$4:G4)

and formulas copied down

Peter

"Bumblebee" wrote:

> 
> Hi All,
> 
> I am having problems adding time values hwen a negative time is
> involved.  I have read all the posts on this and have chosen the 1904
> date option, but no success.
> I determine the difference between times and then subtract a standard
> day from this amount to achieve a result (positive or negative).  Then
> I want to sum the results.  Works fine as long as I don't have any
> negative results.  If I do have negative then the answer is always 0. 
> 
> 
> Example:
> 
> 
> Morning.............Afternoon..............Balance          
> In.......Out........In........Out..........Total..Flex...Balance	
> 8:15 AM..12:35 PM...1:00 PM...5:00:00 PM...8:20...1:20...1:20	
> 9:00 AM..12:55 PM...1:00 PM...3:15:00 PM...6:10..-0:50...0:00	
> 8:30 AM..12:15 PM...1:30 PM...3:00 PM......5:15..-1:45...0:00	
> 8:00 AM..11:00 AM...11:30 AM..5:00 PM......8:30...1:30...0:00	
> 
> 
> I have tried the following formulae:
> = L3 + M4
> = MOD ((L3+M4),1)
> = SUM (L3:L4)
> 
> I am not the most proficient Excel user - if anyone has ideas I would
> be most grateful.
> 
> Deb :)
> 
> 
> -- 
> Bumblebee
> ------------------------------------------------------------------------
> Bumblebee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25669
> View this thread: http://www.excelforum.com/showthread.php?threadid=390851
> 
> 
0
7/28/2005 11:29:03 AM
On Thu, 28 Jul 2005 04:29:03 -0700, "PeterAtherton"
<PeterAtherton@discussions.microsoft.com> wrote:

>Time format can't show negative values.

It can if one uses the 1904 Date system; which the OP wrote she was doing.


--ron
0
ronrosenfeld (3122)
7/28/2005 5:07:24 PM
Many thanks for all your help - Ron's solution worked a treat :

--
Bumblebe
-----------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2566
View this thread: http://www.excelforum.com/showthread.php?threadid=39085

0
7/28/2005 10:35:12 PM
I've just done some testing and discovered that if the Balance colum
totals to more than 24 (either -24 or +24) then the balance start
again from 0 in the next column.  I assume this is to do with thei
only being 24 hours in a day.

Total...Flex...Balance
8:20....1:20....1:20
6:10...-0:50....0:30
5:15...-1:45...-1:15
8:30....1:30....0:15
13:00...6:00....6:15
13:00...6:00...12:15
13:00...6:00...18:15***
13:00...6:00....0:15*** (should be 24:15)
13:00...6:00....6:15*** (should be 30:15)

I would like to hope that none of my staff will clock up more that 2
hours flex, but I can't guarantee it.

Any ideas how to fix the formula

--
Bumblebe
-----------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2566
View this thread: http://www.excelforum.com/showthread.php?threadid=39085

0
7/28/2005 10:51:55 PM
On Thu, 28 Jul 2005 17:51:55 -0500, Bumblebee
<Bumblebee.1swjvd_1122591968.733@excelforum-nospam.com> wrote:

>
>I've just done some testing and discovered that if the Balance column
>totals to more than 24 (either -24 or +24) then the balance starts
>again from 0 in the next column.  I assume this is to do with their
>only being 24 hours in a day.
>
>Total...Flex...Balance
>8:20....1:20....1:20
>6:10...-0:50....0:30
>5:15...-1:45...-1:15
>8:30....1:30....0:15
>13:00...6:00....6:15
>13:00...6:00...12:15
>13:00...6:00...18:15***
>13:00...6:00....0:15*** (should be 24:15)
>13:00...6:00....6:15*** (should be 30:15)
>
>I would like to hope that none of my staff will clock up more that 24
>hours flex, but I can't guarantee it.
>
>Any ideas how to fix the formula?

Format the cell with the formula as:

	[h]:mm

The brackets around the 'h' parameter prevent it from "rolling over" every 24
hours.


--ron
0
ronrosenfeld (3122)
7/29/2005 12:07:47 AM
Cancel that request - I found out I have to change the format of th
coloumn to put square brackets around the h - [h]:mm and this has fixe
it.

:)  :)  :

--
Bumblebe
-----------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2566
View this thread: http://www.excelforum.com/showthread.php?threadid=39085

0
7/29/2005 1:02:09 AM
Reply:

Similar Artilces:

Negative Number column
I am trying to make a column product negative numbers when I manually enter them in a spreadsheet. I am using a simple sum formula to add across rows (10 columns) and the first column should always be a negative number i.e credit and the rest positive. I am real new to excel so I must be doing something wrong. Assuming the 10 cols are A to J, maybe a simpler way would be to use something like .. In K1: =SUM(B1:J1)-A1 Or, in K1: =SUM(B1:J1,-A1) with K1 copied down Then you could just enter the credit numbers in the first column "as-is", w/o worrying about making these nega...

how to set default date value in the Date time field
I have added an attribute of type Date Time in the form of my entity. I want to display current date as default date value. Please tell me how can i set the default date value to the current date. i got it by writing a simple javascript code. Thank u "Rama" wrote: > I have added an attribute of type Date Time in the form of my entity. > I want to display current date as default date value. > Please tell me how can i set the default date value to the current date. > ...

Normal distribution
Hi. I have tried different tools and addons for excel but I am having trouble creating a normal distribution without a negative x-axiz. Example: I want to present a normal distribution for a quiz for students, testing how long they need to solve a mathematical problem. The time varies from 0 to 300 seconds, and most students answer within 10 seconds, some even at once (like you would have done when the question is 2+2?). When I make this with PHStat, XLStat, PlotManager or by using Excel's tools, I always end up with an x-axis that goes from -something to +something. What I need is the n...

Sum with Autofilter?
I have a spreadsheet with a few columns and column totals. If I apply an autofulter and select a subset, the sum total still shows the total of the entire column, not just the subset. Can this be changed? When using filters use Subtotal(9,A1:A100) << See Help for further details, 9 = to sum; 3 = to count, etc..... "Shaun Allan" <anonymous@discussions.microsoft.com> wrote in message news:555801c40058$3ac4e200$a501280a@phx.gbl... > I have a spreadsheet with a few columns and column > totals. If I apply an autofulter and select a subset, the > sum total sti...

Summing the number of incomplete jobs within a date range -sumprod
Hello, I need help figuring out a formula that counts the number of incomplete jobs for a specific worker that are within the next seven days. I would only want to count the "Date Completed" column if it was blank and within seven days from today's date. I've tried using sumproduct, but I can't seem to figure out how to get the date calculations to work properly. My data looks similar to this Worker Due Date Date Completed Bob 1/18/2010 1/13/2010 Bob 1/21/2010 Bob 2/20/2010 Tom ...

Negative budget amounts
How do we handle negative budget amounts. For example, say we anticipate a large freight recovery in one 1/4 and want to put it in the freight expense account. How would we handle this? thx, Doug -- Doug Wilson Business Analyst-Dynamics GP Flagstone Re When you enter/import your budget, if it is a Debit typical balance, ie Freight Expense, then enter in as a Credit value. So if you anticipate a + in an expense, (so reducing the expense) you would Credit the account, and put in that value ie: Anticipate $1000.00 in Recovered Freight In the budget you would put a -1000.00 and then it w...

Report call times out when called through web service but not through Report Manager
Hello all, I have a Reporting Services report that runs fine when I run it from Visual Studio. It also runs fine from Report Manager. However, when I run it from an .aspx page named RSRenderReport.aspx, I get a timeout error after about 100 seconds. I have set the following: In the web.config: <httpRuntime executionTimeout="300" maxRequestLength="300000" /> In the code behind, RSRenderReport.aspx.vb: Dim objRS As New ReportingService.ReportExecutionService objRS.Timeout = -1 The error returned is: ****BEGIN ERROR MESSAGE***** Mess...

Summing up data from various worksheets
I have daily data all on different worksheets (31 in total) and I would like to add up all of the data in these worksheets in a monthly compilation I have so far been using the =sum('jan. 1'!B6, 'jan. 2'!B6 ... etc.) which although is effective is very time consuming is there an easier way to sum up all of the b6 cells without entering them individually? Thanks -- mark_vi_ ------------------------------------------------------------------------ mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24507 View this thread: http://www.excelf...

Formula to sum values extracted from string
Hi all I'll preface this by pointing out that I do NOT want to use text to columns, nor a VBA solution. I'm looking for a formula alternative - maybe an array formula. I have a single column of data extracted from a string, which represents numeric values, single space-separated. The data looks like this: 8.56 2,514.12 3.18 0.35 What I'd ideally like is a formula that sums each of the four values, which in the above example would be 2,526.21. Any suggestions appreciated. >8.56 2,514.12 3.18 0.35 Assume that string is in cell A1. Create this named...

Transaction screen resolution got changed one more time!!!!
hi, in one of my POS the Transaction screen resolutions sometimes increments its height with no reason.... After i fixed it do it again few days after.... Is this a common bug or some like that? Thanks Aldo, I've seen it happen before, it doesn't seem to be too common and don't know what the cause is. The only solution I've found is to always log in as an Limited User instead of an Admin. The Limited User cannot change the setting and make it stick, without a lot of work. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other ...

outlook times out sending e-mail
When sending e-mail, my outlook says it is send 5 messages when I have none in my outbox. Any new messages sit in the out box and will no send. I have the newest Norton Antivirus. A virus scanrequest pops up during the send cycle, bu is too quick and I can't see the message. any ideas?? R. MELAND <anonymous@discussions.microsoft.com> wrote: > When sending e-mail, my outlook says it is send 5 messages > when I have none in my outbox. Any new messages sit in the > out box and will no send. I have the newest Norton > Antivirus. A virus scanrequest pops up during the s...

run time error 429 Excel 2000
i open Excel & this activex error message shows: run time error "429" and it's asking me to 'end' or 'debug'... i don't know Visual Basic and so i keep having this error msg pop up each time i start Excel (2000)... OS: Windows ME please help! thanks much! Next time the message appears, click Debug (the fact that Debug is showing means that its an unprotected macro causing the problem) then post the lines of code that are around the yellow. This sounds like an auto-fire or add-in macro going wrong ...

Access Report 'Graphs'
I am running a function/report that completes the following: 1. Modifies a temp table based on user selection (two variables - Easy) 2. Modifies the temp table with summary line at end of table (summarizing table data - Totals for Graphs) 3. Render a report based on that table 4. Render 6 graphs to show on the report All should be good at this point. Table is modified/updated with correct detail information and summary line. Report opens and queries the table correctly (always). Issue: Each Graph has a select statement based on each requirment (two items only) and the report runs 95...

Schedule emails to automatically be sent at a future date and time without user being logged in.
We have a request to create an automated email to be sent out twice a month to our company pay statement notifications. The payroll department is currently using the option to Do not deliver before the certain date and time which is working for now but is a problem if she logs off of her machine or if the machine turns off on her overnight when the email is scheduled to be sent. I was wondering if there were any other options or solutions that we might be able to try to have a generic email setup to automatically send at certain dates and times without having the user being logged in...

Time Zones
When under Set Personal Options, the Time Zone drop down list box does not include GMT+12 (for New Zealand). Any ideas how I can add it in? There is a service pack that needs to be installed. You can download it from http://support.microsoft.com/default.aspx?kbid=837059 >-----Original Message----- >When under Set Personal Options, the Time Zone drop down >list box does not include GMT+12 (for New Zealand). Any >ideas how I can add it in? >. > Thks, will do that. >-----Original Message----- >There is a service pack that needs to be installed. You >ca...

Sheet moves at print time, loos fine at preview
When I preview the spread sheet the vertical relationship between the spreadsheet and the header looks fine, no space, but after I print it there is extra space between the header and the spreadsheet. What causes this? What am doing wrong? Thanks, Erich Check your left margin to see if all the row numbers are sequential. You may have accidentally decreased the row height on row 2 and yo can't see the space in print preview, but it's there when you print. If that's the case, select the whole row, right click and select Ro Height to adjust the height -- DataCollecto ---------...

Using Bank Rec for the first time
Scenario: Have been using GP for a long time with bank rec registered. Have not been reconciling, but have been doing deposits. Beginning balances for the cash account and checkbook were not entered back when GP was first implemented (2 years ago). Need to start using Bank Rec now. What is the best way to move forward into this scenario? We want to go back to the start of the current fiscal year (several months ago). We have the accurate bank balance as of the last day of the last fiscal year. I was thinking that it would make the most sense to just do an adjustment to make the checkbook...

Employee Time card form
I have a table containing two fields - Employee_ID and Time_Stamp. I've already created a form that scans in Employee_IDs; and once any Employee_ID is scanned in, the ID and scanned time is recorded to the table. In addition to what it already does, the form needs to populate the employee's punch time records for the current week. For example: (current date and time is Wednesday 9:02 am) (Form view before update) Employee ID: ___________________ (Form view after update) Employee ID: _1001 (Enter)_______ ________________________________ Employee ID: 1001 Mon: ...

Indiana to Eastern Time Zone Exchange Appointment fixer scripts
Attached are some server-side VBScripts for Exchange 2003 (2000 was not tested) that effectively change appointments created in the Indiana Time Zone to Eastern Time Zone. It fixes both the time zone tags and start times where appropriate. Recurring meetings are dealt with properly, without creating exceptions. In fact, recurrences and exceptions adjust automatically. It also fixes single occurrence appointments for Apr 2 - Oct 29, 2006 ONLY. All fixed meetings have their subject lines appended with the original time and date of the item. There are additional scripts for fixing Pu...

Free busy time O2003 / Anonymous user in 2007
Hello, How do I disable the free/busy time in the calendar for Exchange/Outlook 2003? So other users can't see the appointments of myself. In Outlook 2007 you will set this due to change the sharing permissions of the calendar, and change the default user permission to none. Any idea how you will get the anonymous user with none permissions back in O2007? What's btw the extra functionality of this user. Any help is appreciated, Thanks, Peter ...

time over 1 hour #2
I have a cell that displays teh amount of time a person waited at a certain place. For example, 1:30 for 1 hour, 30 min. I need to have another cell that will tell me the time above one hour, in this case, :30 Thanks Excel counts one day as 1. Therefore 1 hour = 1/24. Try something like =A1-(1/24) Rgds, Andy With the time in A1 =MOD(A1,1/24) or if it 02:30 should be 01:30 over one hour =A1-TIME(1,,) format as hh:mm -- Regards, Peo Sjoblom "johnfli" <john@here.com> wrote in message news:e670PO%23nDHA.2772@TK2MSFTNGP12.phx.gbl... > I have a cell that ...

Changing text/color after a time period?
I hope some of the brilliant Excel people here can help me out. What I am looking to do, is enter a date in a certain cell (if it helps conceptualize, the date that I ship something to someone). Is it possible to have that cell with the "ship date" in it, either change colors, or change the font size, or something, after 30 days have gone by? My reasoning is that the product I ship out, has to be back to me in 30 days, or a penalty will incur to the other party. I just want these fields that have exceeded the 30 day mark, to stick out, so I can easily identify them. Thanks for any h...

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

Receive email in real-time
We have several PCs in our company that are receiving Exchange email messages in real-time, whereas others have to click Send/Receive within MS Outlook to receive their email. How do I setup Exchange where all users can receive their email instantaneously on any machine in the organization onto which they logon. Thanks in advance, Mervin Williams Hi, You can't configure Exchange to not deliver emails instantly to Outlook users. This is an Outlook setting and will have to be configured here. Leif "Mervin Williams" <mwilliams@innovasolutions.net> skrev i en meddelel...

2nd time posting this problem, pls help
Dear Kindly solve my problem table t1 has fields pictures, name in design view pictures field is ole object and name field is text. now in table, i have inserted some jpeg pictures in field pictures, correspondingly written some name in name field if we open table t1 to view. pictures name package n1 package n2 package n3 package n4 package n5 package n6 now i have written a small query as select t1.pictures from t1 where name = x; x is given in run time, like any thing as n1 /n2/n3/n4 /.... let say x is n2 now. i a...