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
372 Views

Similar Articles

[PageSpeed] 13

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:

Reconnecting more than one mailbox at a time
Hello, I have about 100 mailboxes that need reconnecting to their AD account, rather than clicking on each mailbox and selecting reconnect, can I do this a quicker way? We use Exchnage 2003 SP2 Thanks On Thu, 19 Nov 2009 15:08:34 -0000, "Whiteford" <no@no.com> wrote: >I have about 100 mailboxes that need reconnecting to their AD account, >rather than clicking on each mailbox and selecting reconnect, can I do this >a quicker way? > >We use Exchnage 2003 SP2 See if the "Tools | Mailbox Recovery Center" in the Exchange System ...

error popup run time error 429
Hi, When I try to run RMS programs (administrator,Manager or POS) it pop up window saying that "run time error '429' ActiveX component can't create object". Does anyone know what wrong with it? How do I fix this problem? Thank you very much I had this error message when I removed an Add-on; It turned out the add-on left entries in the registry (Hooks) and this was causing the issue. I removed these entries and all was fine. "qnguyen" wrote: > Hi, > When I try to run RMS programs (administrator,Manager or POS) it pop up > window saying that &...

Calcuating % of increase
I am trying to calculate the % of increase in revenue from one year to the next. If Cell A2 (2003) is $100.00 If Cell A3 (2004)is $500.00 % of Increase in Cell A4 is 400% using the formula =(A3-A2)/A2 This works great but my problem is that in SOME cases, the previous year's revenue is in negative numbers. In that case, it calculates the % of increase correctly but it makes it a negative % instead of a positive one. If Cell A2 (2003) is ($100.00) If Cell A3 (2004)is $500.00 % of Increase in Cell A4 is -600% using the formula =(A3-A2)/A2 This should be a positive 600%. Is the...

Removing Dialog Item at Run Time
Is it possible to remove a Dialog item from a Dialog at run time? This code does not work, but I don't know why: BOOL CMyDialog::OnInitDialog() { HWND hWndCtrl; CWnd pl_wnd; GetDlgItem(IDC_REMOVE_THIS_CHECKBOX, &hWndCtrl); pl_wnd.Attach( hWndCtrl ); pl_wnd.Detach(); pl_wnd.DestroyWindow(); return TRUE; } AVee wrote: > Is it possible to remove a Dialog item from a Dialog at run time? > This code does not work, but I don't know why: > > BOOL CMyDialog::OnInitDialog() > { > HWND hWndCtrl; > CWnd pl_wnd; > > GetDlgItem(IDC_REMOVE_THIS_CHECK...

The Sum of It All
I am trying to get my databases to equal the same amount Based on the same numbers but different totals (i.e. one data base has in column one 2.5, 3, 1.75, 3.25; column two has 1.5, 2, 5.3. The second data base has 10.50 in one column and 8.8 in the second.) However, no matter what I do I cannot get the sums to be equal. Is there something that I am missing or has my brain gone passed mental meltdown? -- --"The second hand unwinds..."-- Vaya con Dios, my darling; Vaya con Dios my love. If the figures are calculated there may be rounding issues (for example, ...

I have a time sheet but...
I have developed a time sheet. It simply tracks time per day and keeps a running total (in decimal hours) of time over consequtive days. HOWEVER, I would like the following elaborations. I would like to have a list of generalized tasks that I could add to as necessary. .... then be able to enter start and end times with a task that I spent that time on. .... then perhaps on a different sheet show total time, total time for each task, and percent of the total spent on each task. Any thoughts as to how I could proceed or might there be a downloadable example I might modify? Hi Hutch! Good...

CRM Mobile
Thought I'd try out CRM mobile on a test config before I start offering to customers...... Have a SBS2003 box with CRM and test Adventure Works CRM data. Followed all CRM Mobile set up instructions and ran install on mobile device via Active Sync. All appeared to complete ok but now when try to make 1st connection it fails with dialogue box saying connection could not be made at this time try again later. Looked at the CRM log-file on mobile device and its shows an error message like this: IM 09/06/2004 17:49.39 CMP rdaMsgBusErrorTrace [LVL]0[EVT]0 txt exception details: Message:Sq...

Stop Sum blanks
I am using the Sum function. My range has blank cells. I am entering 0 for No. If I get a Sum of 0 then I have an answer of False. But how do I stop Excel from displaying 0 when the range of cells is left blank? Maybe... This checks for at least one number in A1:A10: =if(count(a1:a10)=0,"No Values",sum(a1:a10)) This checks for numbers in all of the cells in A1:A10 =if(count(a1:a10)<10,"Some Numbers Missing",Sum(a1:A10)) renee wrote: > > I am using the Sum function. My range has blank cells. I am entering 0 for > No. If I get a Sum of 0 then I...

Time Scale for Charts
In the 2003 Version of Excel I was able to set time scales with a Min Date and Max Date - for example: 1/1/2008 - 12/31/2008. I have charts that measure the Call volumes in our area per month and I am unable to see each month on the Horizontal Axis. Thanks, Susie You can set the min and max the same way, if you've made a line chart with a date axis. The particulars of how the controls are laid out in the dialog are somewhat different, but you should be able to find them. What I like to do is set the base unit to days, and the major unit to months, typically 1 month. If I start o...

Covert text to time
Hey, I am unable to convert a piece of text to the time format. I am copying this from an external source. Example, "July 24 2006, 05:31 PM" Excel does not convert it to the date format. Please help. Please see attached file +-------------------------------------------------------------------+ |Filename: TTT.zip | |Download: http://www.excelforum.com/attachment.php?postid=5112 | +-------------------------------------------------------------------+ -- maverick_abhi ------------------------------------------------------------...

Print Assistant change Start time
I use the Calendar print assistant to print my calendar. I normally use Long Week 01 or Standard Week 02 templates. The calendar starts at 8am. Is there a way to change this to 7am. My Outlook calendar shows a start time of 7am. Is there any way to further customize these templates. Thanks Kristy You can edit the XML - See http://www.slipstick.com/outlook/ol2007/editcpao.asp for instructions. I have not looked into changing the start times so I don't know where to tell you to look. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ O...

Which computer program is best to tally time spent on each project
Does anyone know which computer program is best to tally time spent on each project so that I can keep a running total? Do I need to set up an excel spred sheet to do this? Is there something connected to a dayplanner program that would allow this? thanks Sounds like MS Project to me. -- HTH RP "antiamazon" <antiamazon@discussions.microsoft.com> wrote in message news:7848A00B-3103-4E82-A4C8-F3E664F8A5EA@microsoft.com... > Does anyone know which computer program is best to tally time spent on > each project so that I can keep a running total? > > Do I need...

goal seek and negative values
im using goal seek and from time to time it returns a negative value. how do i prevent this? i have set validation rules for the cells so that i cannot manually enter a negative value but goal seek seems to igore this. Steve, Use Solver instead. Put in a constraint: A2>=0. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "steve" <steve@discussions.microsoft.com> wrote in message news:E5787E67-D3C9-4679-A514-8C26B9A75E48@microsoft.com... > im using goal seek and from time to time it returns a negative value. ...

SignedXml gives false negatives when using namespaces in signed xm
Hello all, It seems that digitally signing XML documents using the SignedXml class has a bug - or at least a behavior I cannot explain. The problem occurs when I sign XML documents containing namespace prefixes and namespace references and then validate it. The validation always fails (returns false) in this case. When I remove the namespace prefixes and namespace references from the XML, signing and validating works fine. It seems that the problem has been recognized in .NET framework 1.1 (see http://support.microsoft.com/kb/888999/en-us), however I am using .NET 2.0, and the problem...

Time and Invoicing
I need a time log each day that i work that will link to invoices. for example, say i've worked 3 hours on account "A" and 4 hours on account "B" today. How can i pull data from specific columns in a particular row. Is there a way that on sheet 2 labeled account "A" Invoice that it will search sheet 1 labeled Time Log for all entries for account "A" and copy the data from an adjacent cell into the account "A" invoice? Basically, if i enter account "A" in a cell on the time log sheet, then the hours recorded in an adjacent c...

First time user
I bought a computer that came with a version of Microsoft Money. I was wandering how good this program is. I have used Quicken before, but never Money. I was debating on which one to use. Any help would be greatly appreciated. Thanks. In microsoft.public.money, Greg wrote: >I bought a computer that came with a version of Microsoft >Money. I was wandering how good this program is. I have >used Quicken before, but never Money. I was debating on >which one to use. Any help would be greatly appreciated. You can use both for a while and decide. To really appreciate Money, y...

How can i find time in various time zone?
How do i use Excel to calculate the time in various timeezone by entering the current time in one cell in Excel and see the other values automatically filled with AM, PM indicator? why not simply use third party software http://wwp.greenwichmeantime.com/ "priya_yuvaraj" <priya_yuvaraj@discussions.microsoft.com> wrote in message news:D7A7933E-F503-4B3A-84AB-E8A7D0C7F229@microsoft.com... > How do i use Excel to calculate the time in various timeezone by entering the > current time in one cell in Excel and see the other values automatically > filled with AM, PM indi...

counting cells between two times
I have a spreadsheet with occurences of incidents where the time of the incident is recorded. I'm trying to count the number of times the incident occurs between certain times (eg 7am and 11am) by using the countif function with variations on the following formula =countif(B:B, ">=07:00:00 & <11:00:00") but it is not calculating it correctly. Can somebody help? Try =COUNTIF(A:A,">=07:00:00")-COUNTIF(A:A,">11:00:00") -- Jacob "Suzie" wrote: > I have a spreadsheet with occurences of incidents where the tim...

Outlook time stamp different from OS
I have a user who's outlook time stamp is out of sync with the OS time stamp. This occured at the daylight savings time switch over. Does anyone know how to resolve this? does outlook, windows, and the server all have the same time zone configuration, including the DST setting? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com "HLi CS" <anonymous@discussions.microsof...

Time between 2 dates #2
Hi, I want to calculate the time (in minutes) between two dates. The forma is: dd/mm-uu:mm For example: (column B) Time 1 = 10/05-13:45 (column C) Time 2 = 10/05-20:30 (column E) Time 2 - Time 1 should give 405 minutes I attached the document. Any suggestions on this one? P Attachment filename: test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=55445 -- Message posted from http://www.ExcelForum.com Enter this in to column E1 =(C1-B1)*1440 That should do the trick! Michae -- Message posted from http://www.ExcelForum.com ...

Summing and Averages with non-numeric cells
I have a 7 question survery with the following possible responses 1 2 3 4 5 N/A We then take ther responses and average them. I'd like to eliminate the N/A responses from the denominator and from the total sum. I currently have 1 cell for this operation - which doesn't work when the response is N/A since it's a simple (sum)/7 equation. Thanks for any help! Hi, Try it this way =SUM(A1:A7)/COUNT(A1:A7) Mike "Erinayn" wrote: > I have a 7 question survery with the following possible responses > 1 > 2 > 3 > 4 > 5 >...

conditional sum #6
I would like to count the cells from A1 to Ax, hvor x is a number I read in another cell. Something like a calculated range. Which formula does that? "Uffe Kousgaard" <oh@no.no> wrote in message news:4e8eac4e$0$293$14726298@news.sunsite.dk... >I would like to count It is actually the sum of the values in those cells I am after. Otherwise it would be too easy. On Oct 7, 8:43=A0pm, "Uffe Kousgaard" <o...@no.no> wrote: > "Uffe Kousgaard" <o...@no.no> wrote in message > > news:4e8eac4e$0$293$14726298@news.sunsite.dk... > >...

converting date and time fields
Hello, I have columns that contain date and time in the "14/07/2003 17:57:00" format. I need to convert them into date-only and time-only fields (not only visually). How do I get rid of the date/time part? Thanks thanks! "Laura Cook" <laura@top-brands.com> wrote in message news:eP$BBBzZDHA.2648@TK2MSFTNGP09.phx.gbl... > With date and time in column A, in B1 enter: > > =INT(A1) > > Format as a date. Now in C1 enter: > > =A1-B1 > > Format as a time. > > Copy the formulas down as far as needed. If you no longer need column A, > ...

Project Time
We are using GP 8.0 & BP 2.7 with Project Time and PDK. Submitting and approving timesheets are working ok. I am seeing some users get an error when their manager or supervisor rejects the timesheet and it is sent back for correction. The error is seen when the user tries to save changes to the rejected timesheet. Microsoft.Dynamics.Pma.PMAEntity.CostCategory [Cause]The entity, Timehsheet, accessed by the user failed security validation. [Correction]The system should accept a query or update...(cut off) Any ideas? David (Repost) ...

Automatically insert date and time in a side note
I know I can insert the current date and time in a note or side not by using sft+alt+f but would like to automate this so that the current date and time are automatically inserted into a side note when I create one by pressing the windows key + n. ...